Read csv file and write contents to existing table

[chris0309]

Hi All,

I have created an Access database that includes a table and form. On the form
i have created a blank button. What i am looking for is vb code to add behind
the button to read a csv file in a known location and write the contents of
the file to my table that already exists.

I have been looking around forums and the internet but cannot find any
information on what im looking for.

I would appreciate any help on this matter.

Many thanks in advance.

Chris

 

[dymondjack]

Check out the DoCmd.TransferText method. It should do everything you need,
and the help file has info on how to use it.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Jim Burke in Novi]

You'll need to create an import specification so that Access knows where each
field is in the csv file. To create the spec, do a manual import first. Use
File, Get External Data, Import. The import wizard will the guide you through
the process. At some point you'll need to make sure you save the spec that
you have created (there's an option in the wizard for saving the spec,a dn
you give the spec a name). Then you can use DoCmd.TransferText - the import
spec is one of the parameters.

Also, if this isn't just a one time import, you'll likely want to delete the
records from the table before the import, otherwise the new rows will be
appended to the end of the table. To delete all records from the table just
use

DoCmd.RunSQL "DELETE * FROM tableName"

 

[dymondjack]

Sorry about that... I knew TransferText would do it, but I hadn't realized
there was that much to it (I've never done it for a csv file before, didn't
realize an import spec was required).


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Jim Burke in Novi]

To be honest, I can't say for certain it's required - I've just always used
one, and don't remember for sure if it's because it was always needed or not.
It may be that if you want all fields imported and don't have any need to
're-define' data types, it will work with just the table name and the file
name and nothing else.