Обсуждение: data loading
<br /><font face="Times New Roman" size="2">Hi,</font><br /><br /><br /><font face="Times New Roman" size="2"> i try toload data from flat file (comma delimiter format) into temporary table . i use COPY command as below:</font><br /><br /><fontface="Times New Roman" size="2">dwnc=# copy biosadm.custdo_temp</font><font face="Times New Roman"> </font><font face="TimesNew Roman" size="2"><br /> dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'</font><font face="Times NewRoman"> </font><font face="Times New Roman" size="2"><br /> dwnc-# WITH DELIMITER ',' ;</font><font face="Times New Roman"><br/></font><font face="Times New Roman" size="2"><br /> ERROR: copy: line 141, Extra data after last expected column</font><fontface="Times New Roman"> </font><br /><br /><br /><font face="Times New Roman" size="2">do u have any ideawhat causes the above error ???or is there any command can be used to load data as requested. tq </font>
On Friday 09 January 2004 02:13, azwa@nc.com.my wrote: > Hi, > > > i try to load data from flat file (comma delimiter format) into > temporary table . i use COPY command as below: > > dwnc=# copy biosadm.custdo_temp > dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV' > dwnc-# WITH DELIMITER ',' ; > > ERROR: copy: line 141, Extra data after last expected column Does line 141 (or nearby) have a comma somewhere in its data? That would fool the COPY into mis-counting the columns. See the COPY entry in the SQL Command Reference chapter of the manuals - you'll want to escape any commas with a backslash:\, -- Richard Huxton Archonet Ltd
On Friday 09 January 2004 02:13, azwa@nc.com.my wrote: > Hi, > Just realised this question is also posted under COPY command. Ignore me. -- Richard Huxton Archonet Ltd
--- Richard Huxton <dev@archonet.com> wrote: > On Friday 09 January 2004 02:13, azwa@nc.com.my > wrote: > > Hi, > > > > > > i try to load data from flat file (comma > delimiter format) into > > temporary table . i use COPY command as below: > > > > dwnc=# copy biosadm.custdo_temp > > dwnc-# from > '/home/bios/customer_data/CustomerDO_new.CSV' > > dwnc-# WITH DELIMITER ',' ; > > > > ERROR: copy: line 141, Extra data after last > expected column > > Does line 141 (or nearby) have a comma somewhere in > its data? That would fool > the COPY into mis-counting the columns. See the COPY > entry in the SQL Command > Reference chapter of the manuals - you'll want to > escape any commas with a > backslash: > \, CSV deals with embedded commas by quoting the data field that contains it. Applications that are aware of this will know to ignore commas within quoted strings. COPY is not smart enough to figure this out (nor smart enough to strip off any quotes, which will be imported as data). If you know for a fact that your data contains no quotes, then you can get away with the "with delimiters ','" trick. Otherwise, you will need to preprocess your data through something that will parse the CSV format, e.g. some Perl module.Note that hand rolled solutions usually underestimate the complexity of dealing with CSV data, and why bother when others have already solved the problem for you. __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus