Solved

Comma delima file to Table and back to file

Posted on 2004-08-29
10
247 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
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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

932 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