Search strings

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi

I created an import routine to bring a CSV file into a
new table in Access. However, much of this data (coming
from a proprietary format) contains quotes and spaces all
over the place. Example:

[ " 232"] (brackets added by me to represent the start
and end of field data)

I have been trying to create a function to replace the
spaces and quotes with nothing (leaving the 232), but
can't get it to work - primarily don't know how to search
for quotes in a string.

Any help is greatly appreciated.
 
To search for a " character in a string, use InStr function:

InStr("TextStringBeingSearched", Chr(34))
 
If you are using Access 2000 or later, take a look in VBA Help at the
Replace function. If you are using an earlier version, search the archives
of this newsgroup at http://groups.google.com for code that performs a
Replace function -- it's been posted here many times.

You might ask yourself the question, "Do I really want to replace it with
'nothing'?" if you replace it with an empty string, it will interfere with
your character counts for start, length, and end of the text fields. Perhaps
you want to replace it with a blank/space character, instead.

Clarifications and followups here to the newsgroup, please, not by e-mail.
Thanks.

Larry Linson
Microsoft Access MVP
 
Steve,
You provide an example of just one field, but I am guessing that all fields
are in this format.
It appears that your data is coming to you in a quoted .csv format.
That is, all text fields are sent with quotes around them so that an import
routine can identify them as text fields and ignore any commas
embedded between the quotes.
For example "Doe,John" , "1,234.00" will both be read into two columns, not
4 columns.

If you are using the Access Import Wizard, First click on Delimited, then
click on the Advanced button on the bottom left of the wizard.
You will get the import specifications screen, where you can specify the
delimiter as "," and specify the Text Qualifier as "

That should solve your problem.
When you are importing the data, it should normally strip the padding spaces
and zeros if you define your data types correctly.

Remember to save the import specification - you can reuse it anytime
afterwards.

HS
 
Back
Top