TransferText and First Record in Text file

[Daron]

I am using this command to import text file:

DoCmd.TransferText acImportDelim, str_SpecName, str_TblName,
str_FileName, False

- The import will always be missing the first line in the text file.
- These text files are coming out of Access.
- The first line is formatted exactly the same and end with the same
CR/LF that the rest of the file has.

The text files I am importing with this problem will have either 125 or
143 fields, CSV, all fields enclosed in double quotes, even empty
fields.

If I use this same command, but the text fields and spec are for fewer
fields, 37 or 40, then the first line imports fine.

Any ideas or pointers?

Daron

[Larry Linson]

Daron" <Da**********@gmail.com> wrote

I am using this command to import text file:

DoCmd.TransferText acImportDelim, str_SpecName,
str_TblName, str_FileName, False

- The import will always be missing the first line in the text file.
Two things to consider:

(1) try omitting the False for the "HasFieldNames" argument, and let it
default to False -- shouldn't make any difference, but stranger things have
happened.
(2) could it be that the length of the text in that first record exceeds
the number of characters allowed in an Access record?

The second item comes to mind as a possible problem when you say
If I use this same command, but the text fields and spec
are for fewer fields, 37 or 40, then the first line imports fine. - These text files are coming out of Access.

A third thing to consider -- don't use the intermediary of a delimited text
file. Either link directly to the tables in the other Access database, or
export as a database file (maybe a dBase, .DBF).

This isn't a feature that I often use, but these are just "general
suggestions".

Have you considered using File I/O instead of TransferText -- Import? That
puts the operation under your control... a bit more effort, but sometimes it
is "comforting" to be "in command".

Larry Linson
Microsoft Access MVP

[Daron]

I have thought about the file i/o option, just trying to do the simple
option first, as the files could have 37, 40, 125, or 140 fields, that
then need to be 'standardized' to 40 fileds. I will try the the other
options first. Let you know Monday after I get back to work!

Just a thought, but would reading each line into a variable, and then
using the split() command work for this?

Thanks for your suggestions!

[Larry Linson]

I often use the Line Input command. I used various coded implementations of
a split before Access had the command. Since Access has had that command,
Split has worked nicely for me.

Larry Linson
Microsoft Access MVP

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I have thought about the file i/o option, just trying to do the simple
option first, as the files could have 37, 40, 125, or 140 fields, that
then need to be 'standardized' to 40 fileds. I will try the the other
options first. Let you know Monday after I get back to work!

Just a thought, but would reading each line into a variable, and then
using the split() command work for this?

Thanks for your suggestions!