Re: import woes - double quotes

From: Matthew Cornell <cornell@cs.umass.edu> > >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... You can't. Try piping the input file to tr, sed or awk. >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. Define "useful". >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). Try running stuff into the command line with a script file: dbaccess dbname scriptname _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.