Importing csv file into Access

[JennyB]

I have several csv files that I want to import into Access. These files are
overwritten once a month. I need a way to replace any commas in the text
fields with a blank. If these are left in they push the data to to next
field when they are imported. Apart from doing a manual search and replace
before each import has someone found a more automated method.

 

[John Nurick]

Hi Jenny,

Usually, if text fields in a CSV file contain or may contain commas, the
fields are qualified (delimited) by quote marks, e.g.
ID, ArtistName, ArtistType
1, "Lulu", "Vocalist"
2, "Dave Dee, Dozy, Beaky, Mick and Titch", "Band"

The import routine normally handles this properly, dropping the quote
marks and respecting the commas within the fields. If you're importing
manually, or with the aid of an import specification or schema.ini file,
make certain that it specifies " as a text qualifier.

If however the text fields aren't delimited by quote marks or some other
character, you've got problems: how can you tell Access which commas are
field separators and which are part of the data?