Importing Data In CSV File

[Tom]

AccessXP --

I am designing an application to import data form a CSV file on a daily basis.
Each line in the CSV file will begin with either A, B or C. I will need to
import the data into three different tables depending on which letter the row
begins with. I'm thinking the way to do this is to use TransferText and import
the file into a temporary table and then run three append queries on the temp
table to put the data into the appropriate tables.

Is this the best way to do this or is there a better way?

Thanks!

Tom

 

[Pieter Linden]

That's how I'd do it... but then what do I know?

 

[Van T. Dinh]

I thinh you can also "link" to the csv file and it appears as a linked Table
in your Database. You can then use the linked Table as the Datasource for
your Append Queries.

 

[Allan Christopher]

Browse for file:
look under "API": http://www.mvps.org/access/api/index.html
Retrieve one line:
http://www.mvps.org/access/modules/mdl0057.htm
Parse out records:
http://www.mvps.org/access/strings/str0003.htm

If your csv cooperates with transfer text then you have a choice. I
have had a couple that didnt. This way worked every time so far. Also,
going this way sped the whole transfer up, too (but took a lot longer
to put together).

Allan

 

[rkc]

AccessXP --

I am designing an application to import data form a CSV file on a daily basis.
Each line in the CSV file will begin with either A, B or C. I will need to
import the data into three different tables depending on which letter the row
begins with. I'm thinking the way to do this is to use TransferText and import
the file into a temporary table and then run three append queries on the temp
table to put the data into the appropriate tables.

Is this the best way to do this or is there a better way?

If the three differing lines of data differ only in whether the first field
is an A, B or
C then the method you outlined is a viable way of automating the task. I
won't ask
why your storing data with the exact same structure in three different
tables.

If the A, B and C lines also differ in structure then your probably looking
at writing
code to read the files, parse out the different lines and add the records to
the correct
tables as you go.

 

[mike l]

Are you familiar w/ VBA? I would write a module to open
the files and run through the records looking at the value
in the first field to determine which table to update. If
you'd like a sample of how to do this email me at
(e-mail address removed) w/ CSV VBA in the subject line! It's
really simple and can be automated so all you have to do
is put the new files into a folder and run.