Text import column order changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

....the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
 
An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.
 
Thanks Klatuu,

99% of the time the field names in the text file are fine. What I will do is
set things up so that exceptions that fail to import, are logged and I'll
follow them up manually.

So it sounds like what I need is is a VBA way of linking, appending and then
unlinking, looping through all the text files in a directory...

Any suggestions most welcome.



Klatuu said:
An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.

GPO said:
Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

...the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
Click to expand...
Click to expand...
 
DoCmd.TransferText acLinkDelim is no help because exactly the same problem
arises. If you don't use a spec, it can't tell what the delimiting is. If you
do use a spec, it overrided the column names in the text file....

Klatuu said:
An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.

GPO said:
Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

...the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
Click to expand...
Click to expand...
 
I don't know what kind of text file you are using. I used this, and it works
just fine. The file is a comma delimited .csv file:
docmd.TransferText acLinkDelim,,"_DeleteMe","c:\documents and
settings\hargida\my documents\actual_res_mixed.csv",true


Now, as to your previous post.
You would have to have a way of know what the directory is that you want to
pull the files from. The you could use the Dir() function. Read up on it in
help. Basically, on the first Dir() call, you give it the path, and a file
filter like if you want only .csv files that start with ABC, it would be
ABC*.csv. Then you set up a loop where you keep calling the Dir() function
with no arguments and it returns the next file in the directory that matches
the filter pattern. After it has returned all the matching files, the next
call will return "". Exit the loop when you get that returned.
So, first, delete all the old import tables. You should set up a naming
convention for them so you can identify them in VBA and do a loop to delete
them. Then do your transfer loop.

GPO said:
DoCmd.TransferText acLinkDelim is no help because exactly the same problem
arises. If you don't use a spec, it can't tell what the delimiting is. If you
do use a spec, it overrided the column names in the text file....

Klatuu said:
An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.

GPO said:
Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

...the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
Click to expand...
Click to expand...
Click to expand...
 
Try it with a tab delimited file. There seems to be nothing in the spec that
tells it that it is a tab. Instead it assumes it is comma.

Klatuu said:
I don't know what kind of text file you are using. I used this, and it works
just fine. The file is a comma delimited .csv file:
docmd.TransferText acLinkDelim,,"_DeleteMe","c:\documents and
settings\hargida\my documents\actual_res_mixed.csv",true


Now, as to your previous post.
You would have to have a way of know what the directory is that you want to
pull the files from. The you could use the Dir() function. Read up on it in
help. Basically, on the first Dir() call, you give it the path, and a file
filter like if you want only .csv files that start with ABC, it would be
ABC*.csv. Then you set up a loop where you keep calling the Dir() function
with no arguments and it returns the next file in the directory that matches
the filter pattern. After it has returned all the matching files, the next
call will return "". Exit the loop when you get that returned.
So, first, delete all the old import tables. You should set up a naming
convention for them so you can identify them in VBA and do a loop to delete
them. Then do your transfer loop.

GPO said:
DoCmd.TransferText acLinkDelim is no help because exactly the same problem
arises. If you don't use a spec, it can't tell what the delimiting is. If you
do use a spec, it overrided the column names in the text file....

Klatuu said:
An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.

:

Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

...the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
Click to expand...
Click to expand...
Click to expand...
 
I was afraid of that. I will have to create a tab delim and try it.

GPO said:
Try it with a tab delimited file. There seems to be nothing in the spec that
tells it that it is a tab. Instead it assumes it is comma.

Klatuu said:
I don't know what kind of text file you are using. I used this, and it works
just fine. The file is a comma delimited .csv file:
docmd.TransferText acLinkDelim,,"_DeleteMe","c:\documents and
settings\hargida\my documents\actual_res_mixed.csv",true


Now, as to your previous post.
You would have to have a way of know what the directory is that you want to
pull the files from. The you could use the Dir() function. Read up on it in
help. Basically, on the first Dir() call, you give it the path, and a file
filter like if you want only .csv files that start with ABC, it would be
ABC*.csv. Then you set up a loop where you keep calling the Dir() function
with no arguments and it returns the next file in the directory that matches
the filter pattern. After it has returned all the matching files, the next
call will return "". Exit the loop when you get that returned.
So, first, delete all the old import tables. You should set up a naming
convention for them so you can identify them in VBA and do a loop to delete
them. Then do your transfer loop.

GPO said:
DoCmd.TransferText acLinkDelim is no help because exactly the same problem
arises. If you don't use a spec, it can't tell what the delimiting is. If you
do use a spec, it overrided the column names in the text file....

:

An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.

:

Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

...the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
Click to expand...
Click to expand...
Click to expand...
 
Wow! you are correct. Transfertext using a tab delimited file without a spec
does not work. What is the source of the data? If it is Excel that is being
saved as .txt, see if it can be changed to be saved as .csv instead.

GPO said:
Try it with a tab delimited file. There seems to be nothing in the spec that
tells it that it is a tab. Instead it assumes it is comma.

Klatuu said:
I don't know what kind of text file you are using. I used this, and it works
just fine. The file is a comma delimited .csv file:
docmd.TransferText acLinkDelim,,"_DeleteMe","c:\documents and
settings\hargida\my documents\actual_res_mixed.csv",true


Now, as to your previous post.
You would have to have a way of know what the directory is that you want to
pull the files from. The you could use the Dir() function. Read up on it in
help. Basically, on the first Dir() call, you give it the path, and a file
filter like if you want only .csv files that start with ABC, it would be
ABC*.csv. Then you set up a loop where you keep calling the Dir() function
with no arguments and it returns the next file in the directory that matches
the filter pattern. After it has returned all the matching files, the next
call will return "". Exit the loop when you get that returned.
So, first, delete all the old import tables. You should set up a naming
convention for them so you can identify them in VBA and do a loop to delete
them. Then do your transfer loop.

GPO said:
DoCmd.TransferText acLinkDelim is no help because exactly the same problem
arises. If you don't use a spec, it can't tell what the delimiting is. If you
do use a spec, it overrided the column names in the text file....

:

An Import spec will not work for you in this situation. If all the files to
be imported have the same field names, but they are in different orders, then
a method that will work is to link to the text file rather than import it.
Then you can create an append query to put the data into your tables. Action
queries do not care what order the fields are in as long as the field names
are the same.
If the field names are not consistent, then you have a whole other problem.

:

Access 2000 and 2003

Hi,
I'm importing a several hundred tab delimited text files with column names
in the first row, into an existing table. The columns in the text files are
in no particular order, presumably because the first row should determine
which column the data should belong in.

I have an import spec saved and I effect the import using:
DoCmd.TransferText acImportDelim, strTableType & "_spec", "stg_" &
strTableType, fd.Path & "\" & fl.Name, True

The problem I have, seems to be that the import spec is not taking any
notice of the order of the columns in the text file as determined by the
first row. However, if I try and import without the spec parameter listed in
the code, (like so)...

DoCmd.TransferText acImportDelim, , "stg_" & strTableType, fd.Path & "\" &
fl.Name, True

...the DoCmd.TransferText method does not seem to know that the file is tab
delimited, and I get the error message "Field
[fld_blah1_fld_blah2_fld_blah3] doesn t exist in destination table
[tblBlah]"

Any ideas? The key points are:
The field names are in the first row of the text files
The columns of the text files are in no particular order
The data are imported using docmd.transfertext
The data go into an existing table

Regards
GPO
Click to expand...
Click to expand...
Click to expand...
 
Back
Top