Re: Issue with copying data from a text file. - Mailing list pgsql-sql

Can you not export the source file with escape characters? ie, <br /><br /> ^17\" Alloy Wheels^<br /><br /> ~p<br
/><br/> On Tue, 2007-03-20 at 11:25 +0900, Paul Lambert wrote: <blockquote type="CITE"><pre> 
<font color="#000000">I have a procedure in place that copies data from a caret delimited text </font>
<font color="#000000">file into a table storing some information.</font>

<font color="#000000">One of the fields in the table contains an item description which may </font>
<font color="#000000">contain item dimensions such as - 17" alloy wheels</font>

<font color="#000000">The problem I am getting when I do my load is I believe due to the </font>
<font color="#000000">presence of the double quotation marks giving the copy the impression </font>
<font color="#000000">that it is to include the information following as a single text string </font>
<font color="#000000">until it gets to the next set of double quotes. As a result, I get the </font>
<font color="#000000">following:</font>

<font color="#000000">AutoDRS=#       COPY deal_lines_temp_load FROM </font>
<font color="#000000">'c:/temp/autodrs_deal_lines.txt'</font>
<font color="#000000">WITH DELIMITER AS '^' CSV HEADER;</font>
<font color="#000000">ERROR:  value too long for type character varying(30)</font>
<font color="#000000">CONTEXT:  COPY deal_lines_temp_load, line 87, column order_desc: "17 5 </font>
<font color="#000000">spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^C^^</font>

<font color="#000000">The column as you can see is defined as a 30 character field, the load </font>
<font color="#000000">contains in this column ^17" 5 spoke alloy wheels.^</font>

<font color="#000000">I note an option in the COPY command to specify the quote character, </font>
<font color="#000000">defaulting to double quote. The problem being a single quote will also </font>
<font color="#000000">be used in the data, as will other characters. Is there any way to get a </font>
<font color="#000000">copy to have no quote character? I.e. read the file and put whatever is </font>
<font color="#000000">between the caret characters straight into the appropriate field exactly </font>
<font color="#000000">as is.</font>

<font color="#000000">TIA,</font>
<font color="#000000">Paul.</font>

</pre></blockquote><table cellpadding="0" cellspacing="0" width="100%"><tr><td> Phillip Smith<br /> IT Coordinator<br
/>Weatherbeeta P/L<br /> 8 Moncrief Rd<br /> Nunawading, Vic, 3131<br /> AUSTRALIA<br /><br /> P. +613 9845 0600<br />
F.+613 9845 0655<br /> E. <a
href="mailto:phillip.smith@weatherbeeta.com.au">phillip.smith@weatherbeeta.com.au</a></td></tr></table>