Re: import woes - double quotes

--- 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 Hmm, I always thought a few meant more than one (or two).... > >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... I always use sed or awk to strip double quotes and change the delimiter to a pipe. Where are you getting your data from? If you're doing your own export you might be able to specify a pipe delimiter. > >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). You can put your SQL in a 4gl program and get multiple error messages at compile time. > > >Thank you. > > >matt >cornell@cs.umass.edu == "Outlook not so good." That magic 8-ball knows everything! I'll ask about Exchange Server next. _____________________________________________________________ Want a new web-based email account ? ---> http://www.firstlinux.net