• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Comma delima file to Table and back to file

I would like to be able to import a comma delima file locate at C:/ My Docs/filename.
into Access and to a table name DataComma (all Cells intact as to the form, the same way should be in table with same fields)
Because i want to make a FrmDataComma to make changes to table then click on button to send back to  comma delima file in its original location, C:/My Docs/

Basically i want to edit my comma delima files in access frmand then save it back as comma to file

Is this possible?/
Sam Cohen
Sam Cohen
  • 4
  • 3
2 Solutions
Use DoCmd.TransferText to import and export comma delimited files.

To import:
DoCmd.TransferText acImportDelim, "MySpec", "MyTable", "C:\MyDocs\MyFile.txt", False

To export, do the same thing except change acImportDelim to acExportDelim. MySpec is the name of an import specification that you have saved. To create an import specification, start up the Import Text Wizard (File->Get External Data->Import, choose your csv file). Set all the options you want. When you get to the last page of the wizard, don't click Finish. Click Advanced, and then click Save. Enter the name of the specification, save it and then exit the wizard.
To add to what shanesuebsahakarn said above:
Aside from the editing textboxes on your form you will need two command buttons, Import CSV and Export CSV. in the onclick event of the Import CSV command button insert:

DoCmd.TransferText acImportDelim,"MySpec", "MyTable", "C:\MyDocs\filename.txt", False

In the onclick event of the Export CSV command button insert:

DoCmd.TransferText acExportDelim,"MySpec", "MyTable", "C:\MyDocs\filename.txt"

You did not have a file extension in you filename criteria so I added txt.

Sam CohenConsultant to Digital/DeveloperAuthor Commented:
I will try both yall comments
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Sam CohenConsultant to Digital/DeveloperAuthor Commented:
what is MySpec referred to?
And is txt the valid entention for comma delima?
also with Mytable, do i already have to have a table MyTable?
If you have any problem importing the file they can usually be resolved using an import specification which you create by clicking on the 'Advanced' tab in the import wizard. You can save the spec by clicking 'Save As' and provide a name. Use this name if you have a specification or put nothing between the commas.  

Sorry, the proper extension for a comma delimited file is "csv". Use that in place of txt in the command line.
Sam CohenConsultant to Digital/DeveloperAuthor Commented:
We heres the problem im having
in my csv, i have number fields and text field
when i open the filename.csv in MS Excel i see this : (note where the * is , that is a number field )( in some number fields 1=yes 0=no)

     |   A*     |      b           |       c*     |     b*       | and so on-----(eg AA,BB,etc)
1   |   SITE   |  PRODUCT  |    QTY    |   FORMAT |
2   |    1      | hp Printer    |      2      |      1         |
3   |    0      | Compaq Prt |      1      |       0        |

Now when i use the code:
DoCmd.TransferText acImportDelim,"", "MyTable", "C:\MyDocs\filename.csv", False
And i look at my MyTable i get this:

|   F1*     |      F2         |      F3*     |     F4*      | and so on-------F78
|             |  PRODUCT   |                |                 |     <----Notice Titles have disappeared because of the fields being *
------------------------------------------------------------                                     ( remember * means number fields)
|    1       | hp Printer    |      2         |      1         |
|    0       | Compaq Prt |      1         |       0        |

so when i do this code:
DoCmd.TransferText acExportDelim,"", "MyTable", "C:\MyDocs\filename.csv"
it never works in the my outside program" TLR" (a certain program that imports Csv) i want to import it in,
But it work if i open it in Ms Excell and edit it then save and import in to TLR

But i want to edit it in Access and save it the way it came

What could be the problem

I have increased the Points as to this being urgent!!!!
let me know if you would like a link to the csv file

The False is telling Access there is not a row of titles in the csv file. From your example, there is. Change the false to true and let me know what happens.
Sam CohenConsultant to Digital/DeveloperAuthor Commented:

This is what ive found out so far
when i do these codes:
DoCmd.TransferText acImportDelim,"", "MyTable", "C:\MyDocs\filename.csv",True
DoCmd.TransferText acExportDelim,"", "MyTable", "C:\MyDocs\filename.csv", True

it outputs fine , but i would have to just openthe  csv in excel and the just save (just open then resave) in order for it to work in my program TLR

any ideas why i just cant open it directly from my program TLR????
I mean if i try to open it directly, without opening and saving in Excell, it say no content was imported

is there a way or code i can , after doing:
DoCmd.TransferText acExportDelim,"", "MyTable", "C:\MyDocs\filename.csv", True
Make excell resave it again or something?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now