Ms Access 2003 - Text File is imported out of sequence

[Chris Barlow]

Hi All

On importing a text file using the TransferText method into an empty, existing table in an Access 2003 database I occasionally find that although the data has all imported ok it is not in the sequence of the original text file. The existing table into which the table is imported is just a range of text fields, F1 through F17 with no primary key field and the text file is comma delimited.

The problem was encountered during testing where the same text file was used. The puzzling thing is that the problem is intermittent only, more often than not the import works fine, just occasionally importing out of sequence.

Has anyone else encountered this problem and know of a solution?

Best Regards
Chris

[puppydogbuddy]

Hi All

On importing a text file using the TransferText method into an empty, existing table in an Access 2003 database I occasionally find that although the data has all imported ok it is not in the sequence of the original text file. The existing table into which the table is imported is just a range of text fields, F1 through F17 with no primary key field and the text file is comma delimited.

The problem was encountered during testing where the same text file was used. The puzzling thing is that the problem is intermittent only, more often than not the import works fine, just occasionally importing out of sequence.

Has anyone else encountered this problem and know of a solution?

Best Regards
Chris

Just a guess. Some of your text fields get padded with a space or spaces on the left. Try using the trim function to remove leading or trailing spaces.

[Chris Barlow]

Just a guess. Some of your text fields get padded with a space or spaces on the left. Try using the trim function to remove leading or trailing spaces.

Yep, I guess they could screw things up but wouldn't they screw it up consistently whereas the problem i am encountering only happens occasionally. I'll give it a go though. Many thanks.

[puppydogbuddy]

Yep, I guess they could screw things up but wouldn't they screw it up consistently whereas the problem i am encountering only happens occasionally. I'll give it a go though. Many thanks.

If the source file for the transfer text is created by data entry, it could boil down to a person or person(s) accidentally hitting the space bar by mistake on an intermittent basis while doing the input.

[Chris Barlow]

Sorry, I didn't explain it well. The source file that is being imported was a test file and it was the same source file used each time. Most times it worked ok, just occasionally giving me the out of sequence problem.

Cheers
Chris

[puppydogbuddy]

Sorry, I didn't explain it well. The source file that is being imported was a test file and it was the same source file used each time. Most times it worked ok, just occasionally giving me the out of sequence problem.

Cheers
Chris

I need more info.
1. Are you using an import specification? If not, that might eliminate the problem.
2. Describe the contents of the text file.
3. Comma delimited or space delimited?
4. Post your DoCmd.transfertext syntax.

[Chris Barlow]

Hi

1. I am not using an import spec because access default if fine.
2. Example of text file below. Note that the first line is always a header (HDR) line.

HDR, 02/08/2007, 10:26:10, VAESVMDMREA2625.H02
I,"REA","0000080963TR-122","","E","","","","","","","","","","02/08/2007 00:00:00","","1"
M,"07A63722:1","40849755","10.57800000","T","","", ""
I,"REA","0000080970TR-74F","","E","","","","","","","","","","02/08/2007 00:00:00","","1"
M,"07A61701:1","40847734","1190.35000000","T",""," ",""

3. Comma delimited
4. DoCmd.TransferText acImportDelim, , "sch", "c:\read files\sch\sch.txt", False

I suppose the interesting thing about this issue is that it is intermittent. Using exactly the same text file it works fine most of the time, just occasionally importing out of sequence.

Regards

[puppydogbuddy]

Hi

1. I am not using an import spec because access default if fine.
2. Example of text file below. Note that the first line is always a header (HDR) line.

HDR, 02/08/2007, 10:26:10, VAESVMDMREA2625.H02
I,"REA","0000080963TR-122","","E","","","","","","","","","","02/08/2007 00:00:00","","1"
M,"07A63722:1","40849755","10.57800000","T","","", ""
I,"REA","0000080970TR-74F","","E","","","","","","","","","","02/08/2007 00:00:00","","1"
M,"07A61701:1","40847734","1190.35000000","T",""," ",""

3. Comma delimited
4. DoCmd.TransferText acImportDelim, , "sch", "c:\read files\sch\sch.txt", False

I suppose the interesting thing about this issue is that it is intermittent. Using exactly the same text file it works fine most of the time, just occasionally importing out of sequence.

Regards

Your problem is probably due to the header line. If you get rid of that header line manually, or through an Import Spec, I think your problem will be resolved. In the syntax of your transferText, you've set hasfields = false....this tells Access that the first row is normal data, not a header or column name (see excerpt from Access Help below) . My theory is that most of the time Access recognizes that the header is not a data row in spite of the syntax, and probably ignores it, but not always (hence..intermittent problems).
__________________________________________________ _____________
hasfieldnames Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed.