RE: import woes - double quotes
Wow - and that from Kernigan?
1/ Wouldn't it have be simpler in awk? (awk - aho, weinberger, kernigan)
awk '{gsub( /\\r/,"") ; gsub(/"/,"") ; print}' file1 > file2
(nawk, or awk, or gawk - depending on your flavour of UNIX - LINUX, so
probably gawk)
2/ dbaccess databasename sqlScript.sql
DO NOT USE A POTENTIALLY DESTRUCTIVE SCRIPT WITHOUT TESTING IT
Sorry, but that was heart-felt.
-----Original Message-----
From: Colin McGrath [mailto:cmm@trac3000.ueci.com]
Sent: 14 November 2000 00:55
To: informix-list@iiug.org
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>
[deleted]
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 ","
> INSERT INTO next_id;>
> I get this error:
>
> 1279: Value exceeds string column length.>
> If I remove the double quotes, it imports just fine. So my question is:
> how do I tell LOAD (or some other import utility) that double quotes
> delimit character records? This particular combination (of double
> quotes, commas, and CR-LF) is *not* that exotic :0) Actually, there
> *must* be a way to escape the delimiter for cases in which, for example,
> a string has a comma in it. Thanks in advance...
>
> BTW:
>
> 1) I had to convert the DOS-style CR-LF line termination to Unix-style
> LF-only, or LOAD barfed. Of course it didn't give me a useful error
> message.
>
> 2) Is there a way to get dbaccess to show *all* error messages that
> result from running SQL commands? It seems to only show the first (i.e.
> no line/char # info).
>
>
> Thank you.
>
>
> matt
> cornell@cs.umass.edu
--
Colin