RE: import woes - double quotes

I found this perl code on the internet somewhere and modified it to print to a pipe delimited file. It won't fix the quotes inside a field (I did that manually, but that only happened in few rows). Hope this helps. Joe #!/usr/local/bin/perl -w #*************************************************************************** ***** # # ch_delimit.pl # # # Usage: # # ch_delimit.pl < inputfile > outputfile # OR: # ch_delimit.pl inputfile > outputfile # OR: # ch_delimit.pl inputfile outputfile # #*************************************************************************** ***** #use strict; use FileHandle; my $sourcefile = $ARGV[0] || ''; my $destfile = $ARGV[1] || ''; my $input = \\*STDIN; my $output = \\*STDOUT; if ($sourcefile && -f $sourcefile) { $input = new FileHandle("<$sourcefile"); $input or die "Couldn't open input file: $sourcefile"; }; if ($destfile) { $output = new FileHandle(">$destfile"); $output or die "Couldn't open output file: $destfile"; }; my $text = (); my @new = (); my @values = <$input>; foreach $text (@values) { push(@new, $+) while $text =~ m{ "([^\\"\\\\]*(?:\\\\.[^\\"\\\\]*)*)",? # groups the phrase inside the quotes | ([^,]+),? | , }gx; push(@new, undef) if substr($text,-1,1) eq ','; } print $output "|"; print $output join('|', @new); $sourcefile && $input->close(); $destfile && $output->close(); exit; -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Colin McGrath Sent: Monday, November 13, 2000 4:42 PM To: Informix Mailing List Cc: cornell@cs.umass.edu Subject: Re: import woes - double quotes Did you see my earlier post pointing to the K&R webpage? > I've used code from Kernighan & Pike's "The Practice of Programming" > (with a small change) to convert comma-separated values to pipes. Their > CSV source is posted at: http://cm.bell-labs.com/cm/cs/tpop/code.html Anyway, here's the C code for stripping out quotes that separate fields (but not quotes inside fields, and my modification to switch commas to pipe symbols: /* Copyright (C) 1999 Lucent Technologies */ /* Excerpted from 'The Practice of Programming' */ /* by Brian W. Kernighan and Rob Pike */ #include <stdio.h> #include <string.h> #include <stdlib.h> #include <assert.h> #include "csv.h" enum { NOMEM = -2 }; /* out of memory signal */ static char *line = NULL; /* input chars */ static char *sline = NULL; /* line copy used by split */ static int maxline = 0; /* size of line[] and sline[] */ static char **field = NULL; /* field pointers */ static int maxfield = 0; /* size of field[] */ static int nfield = 0; /* number of fields in field[] */ static char fieldsep[] = ","; /* field separator chars */ static char *advquoted(char *); static int split(void); /* endofline: check for and consume \\r, \\n, \\r\\n, or EOF */ static int endofline(FILE *fin, int c) { int eol; eol = (c=='\\r' || c=='\\n'); if (c == '\\r') { c = getc(fin); if (c != '\\n' && c != EOF) ungetc(c, fin); /* read too far; put c back */ } return eol; } /* reset: set variables back to starting values */ static void reset(void) { free(line); /* free(NULL) permitted by ANSI C */ free(sline); free(field); line = NULL; sline = NULL; field = NULL; maxline = maxfield = nfield = 0; } /* csvgetline: get one line, grow as needed */ /* sample input: "LU",86.25,"11/4/1998","2:19PM",+4.0625 */ char *csvgetline(FILE *fin) { int i, c; char *newl, *news; if (line == NULL) { /* allocate on first call */ maxline = maxfield = 1; line = (char *) malloc(maxline); sline = (char *) malloc(maxline); field = (char **) malloc(maxfield*sizeof(field[0])); if (line == NULL || sline == NULL || field == NULL) { reset(); return NULL; /* out of memory */ } } for (i=0; (c=getc(fin))!=EOF && !endofline(fin,c); i++) { if (i >= maxline-1) { /* grow line */ maxline *= 2; /* double current size */ newl = (char *) realloc(line, maxline); if (newl != NULL) line = newl; news = (char *) realloc(sline, maxline); if (newl == NULL || news == NULL) { reset(); return NULL; /* out of memory */ } sline = news; } line[i] = c; } line[i] = '\\0'; if (split() == NOMEM) { reset(); return NULL; /* out of memory */ } return (c == EOF && i == 0) ? NULL : line; } /* split: split line into fields */ static int split(void) { char *p, **newf; char *sepp; /* pointer to temporary separator character */ int sepc; /* temporary separator character */ nfield = 0; if (line[0] == '\\0') return 0; strcpy(sline, line); p = sline; do { if (nfield >= maxfield) { maxfield *= 2; /* double current size */ newf = (char **) realloc(field, maxfield * sizeof(field[0])); if (newf == NULL) return NOMEM; field = newf; } if (*p == '"') sepp = advquoted(++p); /* skip initial quote */ else sepp = p + strcspn(p, fieldsep); sepc = sepp[0]; sepp[0] = '\\0'; /* terminate field */ field[nfield++] = p; p = sepp + 1; } while (sepc == ','); return nfield; } /* advquoted: quoted field; return pointer to next separator */ static char *advquoted(char *p) { int i, j; for (i = j = 0; p[j] != '\\0'; i++, j++) { if (p[j] == '"' && p[++j] != '"') { /* copy up to next separator or \\0 */ int k = strcspn(p+j, fieldsep);