Error importing a text file into an Access Database

J

josephsamuels

Hello,

I have created an application that parses a text file and imports the
data into an access table. It reads each line of the file and stores
the entire line as a string. Then, it parses the string and separates
it into many smaller strings . Each smaller string is then imported
into a field in the database using an Insert query.

Everything had been working great until today.

I have a text file that must have some hidden characters in it or
something, because when i store data that looks like spaces in a
smaller string, the smaller string breaks my insert query.

Here is a basic version of my insert query:

"insert into test ([test]) values ( '" strline "' )"

When I read the text file in question, I get this error:

-Run-time error '3075': Syntax error in string in query expression '".-

I printed the SQL statement to see what was happening, and this is what
the printed SQL looks like: Insert into test ([test]) values ('

This text in question should just be spaces, but when I hover over the
variable in debug mode it shows a string like this:
""

What is going on with this? Is there any way I can convert
"" to just spaces?

A few things I've tried that don't work:
instr(1,strline,"") - to see if the string contained the character
trim(strline) - to see if trimming the string would return spaces

If anyone can help me with this I would be very grateful. Thanks.
 
D

Douglas J Steele

You're missing the concatenation operators.

"insert into test ([test]) values ( '" & strline & "' )"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I have created an application that parses a text file and imports the
data into an access table. It reads each line of the file and stores
the entire line as a string. Then, it parses the string and separates
it into many smaller strings . Each smaller string is then imported
into a field in the database using an Insert query.

Everything had been working great until today.

I have a text file that must have some hidden characters in it or
something, because when i store data that looks like spaces in a
smaller string, the smaller string breaks my insert query.

Here is a basic version of my insert query:

"insert into test ([test]) values ( '" strline "' )"

When I read the text file in question, I get this error:

-Run-time error '3075': Syntax error in string in query expression '".-

I printed the SQL statement to see what was happening, and this is what
the printed SQL looks like: Insert into test ([test]) values ('

This text in question should just be spaces, but when I hover over the
variable in debug mode it shows a string like this:
""

What is going on with this? Is there any way I can convert
"" to just spaces?

A few things I've tried that don't work:
instr(1,strline,"") - to see if the string contained the character
trim(strline) - to see if trimming the string would return spaces

If anyone can help me with this I would be very grateful. Thanks.
 
J

Joe

I'm sorry, I mistyped. The SQL string looks like this "insert into
test ([test]) values ( '" & strline & "' )"
 
D

Dirk Goldgar

Hello,

I have created an application that parses a text file and imports the
data into an access table. It reads each line of the file and stores
the entire line as a string. Then, it parses the string and separates
it into many smaller strings . Each smaller string is then imported
into a field in the database using an Insert query.

Everything had been working great until today.

I have a text file that must have some hidden characters in it or
something, because when i store data that looks like spaces in a
smaller string, the smaller string breaks my insert query.

Here is a basic version of my insert query:

"insert into test ([test]) values ( '" strline "' )"

When I read the text file in question, I get this error:

-Run-time error '3075': Syntax error in string in query expression
'".-

I printed the SQL statement to see what was happening, and this is
what the printed SQL looks like: Insert into test ([test]) values ('

This text in question should just be spaces, but when I hover over the
variable in debug mode it shows a string like this:
""

What is going on with this? Is there any way I can convert
"" to just spaces?

A few things I've tried that don't work:
instr(1,strline,"") - to see if the string contained the character
trim(strline) - to see if trimming the string would return spaces

If anyone can help me with this I would be very grateful. Thanks.

You appear to have a non-displayable character in the string. It might
be a carriage return (without its companion line feed character to make
it a valid line break), or it might be a line feed (without a leading
carriage return), or it might be any of a number of other
non-displayable characters.

Your best bet is it find out what the character is, so that you can
remove it from the string or replace it with a space. At a breakpoint
in the routine, at the point where you can display strLine and see the
invalid characters displayed as boxes, enter the following line in the
Immediate Window:

For i = 1 to Len(strLine) : ?Asc(Mid(strLine, i, 1) : Next i

That should display the ASCII value of each character in the string, so
you can see what these non-displayable characters are.
 
J

Joe

Dirk,

I did exactly what you said and the ASCII value of the non-displayable
characters is 32. What does that mean?

Thanks a lot for your help,

Joe
 
J

Joe

I spoke too soon. I found out that 32 = space. The string also
contains some 0 characters, which equal .
 
D

Douglas J Steele

"insert into test ([test]) values ( '" & Replace(strline, Chr$(0), Chr$(32))
&* "' )"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top