Import Specifications in MS Access

[djmc]

Suppose my CSV file forms a table like this:

COL_1,COL_2,COL_3
a,b,c
d,e,f

I want to import the csv file into the following table structure in MS Access:

COL_2 | COL_1 | COL_3
----------------------
b | a | c
e | d | f
----------------------

How can I create a specification in MS Access that would direct col 1 in the csv to 2nd column of the table structure in MS Access? (or interchange to any column i want)

I know I can do this by reading the file line by line and parsing the values and inserting the data to the fields i want but I was wondering if there is a better way using a specification file when i do DoCmd.TransferText.

Thanks.

 

[Nelviticus]

I assume you're adding this data to an existing table rather than a new one, right? If not then it doesn't matter what order the columns are in as they're just data.

An import specification only lets you define things such as which columns to skip, what data type each column is and what delimiters to use so you'd have to either a) make sure that your data file is in the right order in the first place or b) read the file and parse it as you've suggested.

Nelviticus

 

[PHV]

Another way is to pull the data into a temporary table and then launch an append query from the temp table to the permanent one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244