Importing Delimited text fil

[Guest]

I am trying to import a series of delimited text files into an existing
table. They are all delimited with semicolons, and most of the files went in
correctly. However, I have two files that I am having problems with. When I
try to import them, the import wizard recognizes SOME of the semicolons as
delimiters, but not all of them. I tried changing to tabs, but had the same
problem...it recognized SOME of them but not all of them.

I have to be able to start running queries on this today if possible...can
anyone help?

 

[John Nurick]

Hi Kathleen,

Usually this indicates that there's some irregularity in the problematic
files.

You say you tried changing the semicolons to tabs but that it recognised
"some of them but not all of them". Does this mean that the program you
used to change the text file only changed some of the semicolons to
tabs? If so, it's possible that some of the semicolons in the original
file are the "real" semicolons that are used as delimiters, while others
are Unicode characters that look the same in the font you were using
(the ordinary semicolon is ASCII 3B, Unicode U+003B; there's also a
"small semicolon" at U+FE54 and a "full width semicolon" at U+FF1B).

Otherwise, it could be an issue with quote marks used to delimit
(qualify) text fields: one too many or too few on a line, perhaps. Also,
if text fields are qualified with quotes, any quotes within the data
must be doubled:
"He said, ""Hello!"""
If the text files come from non-Microsoft (especially non-Windows)
software, quotes within quotes may be escaped differently:
"He said, \"Hello!\""
which will screw up the Access/Jet text import system.

Finally, if you're using a recent version of Access, a missing CR or LF
character at the end of a record can cause problems. The record
separator needs to be the two characters CR and LF - Chr(13) & Chr(10);
again, files from non-Windows sources often use just LF or just CR.

I hope this helps. If you're still stuck, please post back with more
information about the size and structure of the problematic files, and
how the differ from the ones that imported successsfully.

 

[Guest]

Thanks a lot, we figured out about 15 minutes ago that it was the "" in the
..txt files. I used the find/replace to replace the " with blank spaces and
then it worked just fine.

Again, thanks for answering

 

[Guest]

John,

I am experiencing the problem you described regarding Access 2003+ with CF
LF. I am using Access 2007 BTR. I am trying to import a comma delimited
file, but it is failing and telling me the data contains more than 255
columns. When I examine the source file in Wordpad, there is a continuous
stream of data because there is only a LF as record separator instead of CR
LF.

I am not that proficient at VBA coding and was hoping you might share a
routine that reads in a text file and replaces the LF with CF LF...or any
other solution you might have.

Thanks.

 

[John Nurick]

Hi Neal,

If you do a web search for
convert lf crlf
you'll find a number of utility programs that will convert the
linebreaks.