appending data from text file with different column orderings

S

sysdrk

I'm interested in appending data to an Access table without having to
be constrained by the order of the columns in an external file. For
instance, if I have a table with 4 fields - A, B, C, D - I would like
to be able to have my external file in any column order, say D, C, B,
A. Is this possible without programming?
From what I've seen, it seems that I would have to have my external
Click to expand...
file ordered the same as the table - A, B, C, D - and I'd like to have
independence of the table ordering.

Denis
 
J

John Nurick

Hi Denis,

1) If the external file is a delimited text file whose first row
consists of column headers that match the field names in the Access
table, the order of the columns doesn't matter.

2) If a delimited file doesn't have doesn't have column headers, the
order of columns must be the same as in the table ...

UNLESS

a) you're using an import specification or schema.ini file, in which
case you can assign field names that match those in the table and the
situation is the same as (1) above ...

OR

b) you can use an Append query along these lines to re-order and/or
alias the names (F1, F2... are the default names used by Jet):

INSERT INTO MyTable (FieldThree, FieldTwo, FieldOne)
SELECT F1, F2, F3
FROM [Text;HDR=No;Database=C:\My Folder\;].MyFile#txt
 
S

sysdrk

Hi Denis,

1) If the external file is a delimited text file whose first row
consists of column headers that match the field names in the Access
table, the order of the columns doesn't matter.
Click to expand...

OK, that's what I hoped the answer would be. However when I did some
initial googling on this topic I found a microsoft website

http://office.microsoft.com/en-us/access/HA010546511033.aspx

on this topic that says:

Note If you want to append data to an existing table, your text file
should not have column headings- they will interfere with the import
process. Confirm that the columns of data in your text file will work
with the data types of the fields in your Access table. For example,
to successfully import a date into a Date/Time field in an Access
table, the field in the text file should be in a format that Access
will recognize as a date. It may take a little experimentation on your
part to get this right in your text file.

This quote is in reference to using the Import Text Wizard. Do I use
something other than the Import Text Wizard to accomplish this task?
Or is this reference wrong?

Denis
 
J

John Nurick

In my experience case (1) does work - provided that file and table have
the same number of columns, the data types are compatible and the column
names are identical. I.e. the only difference is that the columns are in
different orders.

I have suggested a correction to the article you cite.
 
Top