removing commas in csv

[ziggynorton]

Hi, my first post!
I have a workbook which I need to change into a comma delimited file s
as to import it into Access (it won't import with the normal xls fil
but it's ALMOST right if I use csv). It works fine until I try t
import memo fields containing a comma, wherein it uses the comma t
create a new cell. What I want to do is to have all the commas remove
from the memo fields. I did find a piece of code which should do thi
but have no idea how to implement it. Any suggestions gratefull
received. Thanks. Ziggy

 

[NickHK]

ziggynorton,
You should be able to import/link the XL file directly in Access, assuming
it has a table structure. The fact you have commas in a text field should
not matter.
Even so, with the CSV file, as long as your memo field is surrounded by
quotes (which it would be if generated by XL), again the internal commas
should not matter, as long as you tell Access that the Text Qualifier is ""
(on the first step of the Access Import Wizard click Advanced..).

Why will the XL file not import directly ?

NickHK

"ziggynorton" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

As far as know, it is not possible to import into MEMO (or more
appropriately IMAGE) columns (which contain Byte arrays) in Access using SQL
(what Import will be using). I have used ADO and its AppendChunk method to
retrieve Byte() arrays and then the Update method to write to the table.

 

[ziggynorton]

I've been importing Excel spreadsheets directly into Access just usin
the import feature that Access has. However, for some reason, th
latest batch of spreadsheets wouldn't import certain fields, jus
creating errors. However, I did find that the best way of exportin
them is using the tab delimiter rather than the comma. As Nic
mentioned, csv SHOULD surround the memo fields with quotes, but thi
only happened MOST of the time, a few seemed to slip through the net
So, the best method, and the one that works, is exporting text with ta
delimiting, which imports directly. This is a link to an article whic
discusses the subject but unfortunately, my skills don't allow me t
put this into practice (or to be honest, understand where to put th
code!)
http://support.sas.com/sassamples/quicktips/04jul/commadelimited.htm

 

[NickHK]

ziggynorton,
Post a sample of the records in XL that are causing you trouble.

NickHK

"ziggynorton" <[email protected]>
wrote in message
news:[email protected]...