Обсуждение: COPY command
<br /><font face="Times New Roman" size="2">Hi,</font><br /><br /><br /><font face="Times New Roman" size="2"> i try toload a file into temporary table but it gives me an error . do u have any idea/solutions ?? the command that i've runningas below :</font><br /><br /><br /><font face="Times New Roman" size="2"> dwnc=# copy biosadm.custdo_temp</font><br/><font face="Times New Roman" size="2">dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'</font><br/><font face="Times New Roman" size="2">dwnc-# WITH DELIMITER ',';</font><br /><br /><font face="Times New Roman" size="2">ERROR: copy: line 141, Extra data after last expected column</font><br/><br /><font face="Times New Roman" size="2">FYI, my file was in comma delimiter type (csv) . My table structureas following :</font><br /><br /><font face="Times New Roman" size="2"> dwnc-# \dbiosadm.custdo_temp</font><br/><font face="Times New Roman" size="2"> Table "biosadm.custdo_temp"</font><br /><fontface="Times New Roman" size="2"> Column | Type | Modifiers</font><br /><font face="Times New Roman"size="2">----------+-----------------------+-----------</font><br /><font face="Times New Roman" size="2"> dono | character varying(13) |</font><br /><font face="Times New Roman" size="2"> dodate | date |</font><br/><font face="Times New Roman" size="2"> custname | character varying(70) |</font><br /><font face="Times NewRoman" size="2"> custlo | character varying(40) |</font><br /><font face="Times New Roman" size="2"> attnto | charactervarying(80) |</font><br /><br /><br /><font face="Times New Roman" size="2">Please guide me . thanks</font>
This sounds as if the last character in each line might be a delimiter (a ",") which is standard for data unloaded from somesources; if this is the case try removing it and your data should load. Or you may have a comma in a character fieldand that is throwing off the count for the line in question ? HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: azwa@nc.com.my [mailto:azwa@nc.com.my] Sent: Tue 1/6/2004 10:19 PM To: pgsql-sql@postgresql.org Cc: Subject: [SQL] COPY command Hi, i try to load a file into temporary table but it gives me an error . do u have any idea/solutions ?? the command that i've running 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 FYI, my file was in comma delimiter type (csv) . My table structure as following : dwnc-# \dbiosadm.custdo_temp Table "biosadm.custdo_temp" Column | Type | Modifiers ----------+-----------------------+-----------dono | character varying(13) |dodate | date |custname| character varying(70) |custlo | character varying(40) |attnto | character varying(80) | Please guide me . thanks
<font face="sans-serif" size="2">Hi ,</font><font face="sans-serif"> </font><br /><br /><br /><font face="sans-serif" size="2">yesnow i can do the copy after replace comma delimiter with tab delimiter which is default copy with delimiter .btw i've another</font><font face="sans-serif"> </font><br /><font face="sans-serif" size="2">question : how do we runthe COPY command from file which has several null columns in each field. </font><font face="sans-serif"> </font><br /><fontface="sans-serif" size="2">thanks.</font><font face="sans-serif"> </font><br /><br /><font face="sans-serif" size="2">example:</font><font face="sans-serif"> </font><br /><br /><font face="sans-serif" size="2"> dwnc=# copy biosadm.custinv_temp</font><fontface="sans-serif"> </font><br /><font face="sans-serif" size="2">dwnc-# from '/home/bios/customer_data/CustomerInvoice_5Dec03_tab.txt';</font><font face="sans-serif"> </font><br /><font face="sans-serif"size="2">ERROR: copy: line 1, Missing data for column "subsidiary" </font><font face="sans-serif"> </font><br/><br /><br /><font face="sans-serif" size="2">p/s: my subsidiary column has several null data. </font><br /><br/><br /><table width="100%"><tr valign="top"><td></td><td><div align="center"><font face="sans-serif" size="2"><b>"GregoryS. Williamson" <gsw@globexplorer.com></b></font></div><div align="center"><p><font face="sans-serif"size="2">07:06 PM PST Yesterday</font></div><div align="center"><br /></div></td><td><font face="sans-serif"size="2"> To: <azwa@nc.com.my>, <pgsql-sql@postgresql.org></font><br /><font face="sans-serif"size="2"> cc: </font><br /><font face="sans-serif" size="2"> Subject: RE: [SQL]COPY command</font></td></tr></table><br /><br /><font face="Courier New" size="2"><br /> This sounds as if the lastcharacter in each line might be a delimiter (a ",") which is standard for data unloaded from some sources; if this isthe case try removing it and your data should load. Or you may have a comma in a character field and that is throwing offthe count for the line in question ?<br /><br /> HTH,<br /><br /> Greg Williamson<br /> DBA<br /> GlobeXplorer LLC<br/><br /> -----Original Message-----<br /> From: azwa@nc.com.my [mailto:azwa@nc.com.my]<br /> Sent: Tue 1/6/2004 10:19 PM<br /> To: pgsql-sql@postgresql.org<br /> Cc: <br/> Subject: [SQL] COPY command<br /><br /> Hi,<br /><br /><br /> i try to load a file into temporarytable but it gives me an error . do <br /> u have any idea/solutions ?? the command that i've running as below:<br /><br /><br /> dwnc=# copy biosadm.custdo_temp<br /> dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'<br/> dwnc-# WITH DELIMITER ',' ;<br /><br /> ERROR: copy: line 141, Extradata after last expected column<br /><br /> FYI, my file was in comma delimiter type (csv) . My table structure as <br/> following :<br /><br /> dwnc-# \dbiosadm.custdo_temp<br /> Table "biosadm.custdo_temp"<br /> Column | Type | Modifiers<br /> ----------+-----------------------+-----------<br /> dono | character varying(13)|<br /> dodate | date |<br /> custname | character varying(70) |<br /> custlo | charactervarying(40) |<br /> attnto | character varying(80) |<br /><br /><br /> Please guide me . thanks<br /><br /><br/></font><br />
On Jan 9, 2004, at 1:11 PM, azwa@nc.com.my wrote: > how do we run the COPY command from file which has several null > columns in each field. > thanks. > > example : > > dwnc=# copy biosadm.custinv_temp > dwnc-# from '/home/bios/customer_data/CustomerInvoice_5Dec03_tab.txt' ; > ERROR: copy: line 1, Missing data for column "subsidiary" Just like you specify the delimiter, you need to specify the value for NULL. For example, if your *.txt file has a line with a "missing" value (such as the space between 'bar' and 'foofoo') foo bar foofoo barbar I usually use 'NULL' as NULL, so the line would be foo bar NULL foofoo barbar then use the COPY command with NULL AS 'NULL' Check out the following link for detailed info on the COPY command: <http://www.postgresql.org/docs/current/static/sql-copy.html> HTH Michael Glaesemann grzm myrealbox com