import woes - double quotes

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

First of all, Informix uses the "\\" character as an escape character in case a particular character in a record is similar your delimiter. Microsoft (like SQL server) uses the double quote string as a way to use it as an escape characters for string data types. Use this to convert your SQL server outpur text file to informix format: cat test-data.txt |sed 's/",/`/g'|sed 's/"//g'|sed 's/`/|/g' > test2.txt then on dbaccess, load from test2.txt insert into next_id; PS. I think you uploaded the file in binary format that's why you have ^Ms in it. In article <3A101790.A440C12C@cs.umass.edu>, Matthew Cornell <cornell@cs.umass.edu> 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 > Sent via Deja.com http://www.deja.com/ Before you buy.

Matthew Cornell wrote: > 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 >[snip] > Using LOAD to load this file [snip] 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... A number of solutions have been suggested, some using Perl or C, others using general Unix tools like awk and sed. Using general purpose tools is problematic when the data contains actual instances of the double quote character as well as double quotes serving as the string delimiting metacharacters. You could also consider using SQLCMD from the IIUG archives. It has a number of format modes, including specifically CSV (-F csv on the command line, or format csv in the script). SQLCMD expects that literal double quotes are escaped with the current escape character, which defaults to backslash. SQL itself uses a different convention - you use two double quotes in a row to embed a single doublt quote in a string. If you don't have actual double quotes in the data, then all is hunkydory. > 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. Even SQLCMD exepcts just newlines, not CRLF. That can be dealt with reliably using the tr command on Unix. > 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). No. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"