Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Comma delima file to Table and back to file

Posted on 2004-08-29
10
Medium Priority
?
264 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 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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