[AccessD] FW: Removing quotes

Import with no delimiters, than append to a new table or update but use
the Mid function to get all but the first and last characters, which
would be quotes.  Then you can do a replace to replace all Chr(34) with
chr(39).

Alternatively, import it into a Word table first and do your search and
replace there.

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
at Beach Access Software
Sent: Thursday, May 31, 2007 3:36 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] FW: Removing quotes

Dear List:
 
A client has a large tab delimited file which I'm trying to import into
an access table using the wizard.  The text fields in the tab delimited
file have quotes around them.  Unfortunately the Description field has
embedded quotes for descriptions with lengths - i.e. "Suture 3 1/2" ".  
 
So if I import with the quote as text qualifier any record with a quote
in the description field is unparseable because there's an uneven number
of quotes and all of the subsequence fields after the Description are
dropped.

If I import with no character as the text qualifier I get all the fields
but all the text fields are surrounded by quotes.  So I have to remove
them. 
 
I'm thinking to do it with a couple update queries but there are about
140 fields so it would be awkward and a bit time consuming to structure
and test the update queries.  
 
I'm thinking of doing it with a bit of code - cycle through the fields
for each record and strip the quotes (also replace the quote that
represents inches with 'in.')
 
I think it would take an hour to do it in code. But is there any faster,
easier, slicker way I'm overlooking?
 
MTIA
 
Rocky

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com