By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Ms Access 2003 - Text File is imported out of sequence

P: 4
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
Aug 12 '07 #1
Share this Question
Share on Google+
7 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 12 '07 #2

P: 4
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.
Aug 12 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 13 '07 #4

P: 4
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
Aug 14 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 14 '07 #6

P: 4
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
Aug 14 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 14 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.