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);
memmove(p+i, p+j, k);
i += k;
j += k;
break;
}
p[i] = p[j];
}
p[i] = '\\0';
return p + j;
}
/* csvfield: return pointer to n-th field */
char *csvfield(int n)
{
if (n < 0 || n >= nfield)
return NULL;
return field[n];
}
/* csvnfield: return number of fields */
int csvnfield(void)
{
return nfield;
}
/* csvtest main: test CSV library */
int main(void)
{
int i, j, k = 0;
char *line;
while ((line = csvgetline(stdin)) != NULL) {
for (i = 0; i < csvnfield(); i++)
printf("%s|", csvfield(i));
if (k == 0) {
k = 1;
j = i;
}
else {
while (i < j) {
printf("|");
i++;
}
}
printf("\\n");
}
return 0;
}
Matthew Cornell wrote:
>
> Hi Folks,
>
> Still hacking away at IDS.2000 9.20.UC1 on RedHat 6.2. I'm trying to
> import data in text files that I exported from SQL Server 7.0. By
> default, that program exports data with commas delimiting fields, and
> CR-LF at ends of rows. Importantly, it delimits strings (CHAR and
> VARCHAR) with double quotes. For example, here are a few lines from one
> table:
>
> "L",76386
>
> for which the table definition is:
>
> CREATE TABLE next_id (
> entity_type CHAR(1) NOT NULL,
> next_id INT NOT NULL);>
>
> Using LOAD to load this file:
>
> LOAD FROM 'test-data.txt'
> DELIMITER ",">