Solved

Comma delima file to Table and back to file

Posted on 2004-08-29
10
245 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
10 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I will try both yall comments
0
 
LVL 8

Author Comment

by:Sam Cohen
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now