[AccessD] FW: Removing quotes

No Perl here.  Anyway, it's tab delimited.  I think I'll get up early and
write some code.

Rocky
 




 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Thursday, May 31, 2007 6:25 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

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

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.472 / Virus Database: 269.8.5/826 - Release Date: 5/31/2007
4:51 PM