Error importing a text file into an Access Database

[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.

 

[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.

 

[Joe]

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

 

[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.

 

[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

 

[Joe]

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

 

[Joe]

How do I convert these characters to a space?

 

[Joe]

How do I convert these characters to spaces?

 

[Douglas J Steele]

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

 

[Dirk Goldgar]

How do I convert these characters to a space?

See Doug's response.