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

--
ziggynorto
-----------------------------------------------------------------------
ziggynorton's Profile: http://www.excelforum.com/member.php...fo&userid=3521
View this thread: http://www.excelforum.com/showthread.php?threadid=55175

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"
wrote in message
...

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

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.

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/qu...adelimited.htm

--
ziggynorto
-----------------------------------------------------------------------
ziggynorton's Profile: http://www.excelforum.com/member.php...fo&userid=3521
View this thread: http://www.excelforum.com/showthread.php?threadid=55175

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

NickHK

"ziggynorton"
wrote in message
...

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