[AccessD] FW: Removing quotes

Nice about about Perl or any other "higher level language" is that there
is always more than one way to get the same result.  Another way to
accomplish this would be to split on the tab delimiters and evaluate each
item using a regular expression and then rejoin.  Or globally substitute
the | for all tabs (^t) and perform the regular expression on the quotes. 
For doing intermediate processing on text files using Perl or Tcl are
great tools plus they are open-source and free.

Mike...

 be using split and creating a regular-expression

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>