Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Comma delima file to Table and back to file

Posted on 2004-08-29
10
Medium Priority
?
266 Views
Last Modified: 2012-06-21
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?/
0
Comment
Question by:Sam Cohen
  • 4
  • 3
8 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 11926538
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11926637
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.

0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 11927829
I will try both yall comments
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 8

Author Comment

by:Sam Cohen
ID: 11965868
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?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 500 total points
ID: 11966142
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.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 11968365
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




0
 
LVL 44

Expert Comment

by:GRayL
ID: 11968498
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.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 11968720
Ok GRAyL

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?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question