removing commas in csv

[ziggynorton]

Hi, my first post!
I have a workbook which I need to change into a comma delimited file so as to import it into Access (it won't import with the normal xls file but it's ALMOST right if I use csv). It works fine until I try to import memo fields containing a comma, wherein it uses the comma to create a new cell. What I want to do is to have all the commas removed from the memo fields. I did find a piece of code which should do this but have no idea how to implement it. Any suggestions gratefully 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]...
>
> Hi, my first post!
> I have a workbook which I need to change into a comma delimited file so
> as to import it into Access (it won't import with the normal xls file
> but it's ALMOST right if I use csv). It works fine until I try to
> import memo fields containing a comma, wherein it uses the comma to
> create a new cell. What I want to do is to have all the commas removed
> from the memo fields. I did find a piece of code which should do this
> but have no idea how to implement it. Any suggestions gratefully
> received. Thanks. Ziggy.
>
>
> --
> ziggynorton
> ------------------------------------------------------------------------
> ziggynorton's Profile:
http://www.excelforum.com/member.php...o&userid=35210
> View this thread: http://www.excelforum.com/showthread...hreadid=551751
>

[AA2e72E]

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 using the import feature that Access has. However, for some reason, the latest batch of spreadsheets wouldn't import certain fields, just creating errors. However, I did find that the best way of exporting them is using the tab delimiter rather than the comma. As Nick mentioned, csv SHOULD surround the memo fields with quotes, but this 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 tab delimiting, which imports directly. This is a link to an article which discusses the subject but unfortunately, my skills don't allow me to put this into practice (or to be honest, understand where to put the code!): http://support.sas.com/sassamples/qu...delimited.html

[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]...
>
> I've been importing Excel spreadsheets directly into Access just using
> the import feature that Access has. However, for some reason, the
> latest batch of spreadsheets wouldn't import certain fields, just
> creating errors. However, I did find that the best way of exporting
> them is using the tab delimiter rather than the comma. As Nick
> mentioned, csv SHOULD surround the memo fields with quotes, but this
> 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 tab
> delimiting, which imports directly. This is a link to an article which
> discusses the subject but unfortunately, my skills don't allow me to
> put this into practice (or to be honest, understand where to put the
> code!):
> http://support.sas.com/sassamples/qu...delimited.html
>
>
> --
> ziggynorton
> ------------------------------------------------------------------------
> ziggynorton's Profile:
http://www.excelforum.com/member.php...o&userid=35210
> View this thread: http://www.excelforum.com/showthread...hreadid=551751
>