[AccessD] FW: Removing quotes

If you have a windows version of Perl

 Here's a Perl script  to convert CSV to
pipe-delimited text strings, then change text delimiter
 in import wizard to pipe symbol  "|"

Might work

#csvtab.pl
#Perl script to convert quote-delimited CSV files
#to undelimited pipe-separated files.
#Syntax:
#  perl csvtab.pl inputfile.txt > outputfile.txt

use strict;
use Text::ParseWords;
my @line;

while (<>) {
  @line = parse_line ",", 1, $_;
  map { s/^\"//; s/\"$// }  @line;
  print join "|", @line ;

}




Rocky Smolin at Beach Access Software wrote:

>This is a test file of 18k records.  The final file will be over a million.
>And it's not a one shot.  So I've got to find an automated solution.  
>
>Rocky
>
>
>
> 	
>	
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
>Sent: Thursday, May 31, 2007 3:52 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] FW: Removing quotes
>
>Can't you delete the quotes in the original file?  -- I'm trying to think of
>a Find Replace pattern that might work -- how about 
>
>Space " 
>
>And 
>
>" space
>
>That would catch every " at the beginning of a word and after -- would help
>I would think. Might not get everything, but might get enough that you could
>visually catch the rest. 
>
>Susan H. 
>
>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
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada