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

Maybe use char 254 or 253 or something similar... Anything that isn't going to be found in the file.<br /><br /> Have
youtried using a string as a delimiter? QUOTE 'THIS.STRING.ISNT.IN.THE.TEXT.FILE'<br /><br /> ~p<br /><br /> On Tue,
2007-03-20at 11:59 +0900, Paul Lambert wrote: <blockquote type="CITE"><pre> 
<font color="#000000">Andrej Ricnik-Bay wrote:</font>
<font color="#000000">> On 3/20/07, Paul Lambert <<a
href="mailto:paul.lambert@autoledgers.com.au">paul.lambert@autoledgers.com.au</a>>wrote:</font> 
<font color="#000000">> </font>
<font color="#000000">>> The source file comes from extracts on our main application which sits</font>
<font color="#000000">>> inside an in-house pretending-to-be-a-dbms file system. The content of</font>
<font color="#000000">>> these extracts would be difficult to change - the extract program would</font>
<font color="#000000">>> need to parse the data looking for quotes and preceed them with the</font>
<font color="#000000">>> necessary escape character.</font>
<font color="#000000">>></font>
<font color="#000000">>> Not being a proper database dump it's not a simple matter of flicking a</font>
<font color="#000000">>> switch to get it to include the escape character. The way the extracts</font>
<font color="#000000">>> are written would require a few dozen lines of code to each extract, and</font>
<font color="#000000">>> theres about 40ish extracts.</font>
<font color="#000000">>></font>
<font color="#000000">>> Plus I don't maintain that side of our code, and those that do can be a</font>
<font color="#000000">>> bit lazy and I'd likely be waiting months to get it done - if they even</font>
<font color="#000000">>> decide to do it.</font>
<font color="#000000">> Pipe it through sed and replace the Carets with TABS?</font>
<font color="#000000">> sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt > </font>
<font color="#000000">> c:/temp/autodrs_deal_lines.tab</font>
<font color="#000000">> </font>
<font color="#000000">> Then use copy like so:</font>
<font color="#000000">> \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null ''</font>
<font color="#000000">> </font>
<font color="#000000">> </font>
<font color="#000000">> Cheers,</font>
<font color="#000000">> Andrej</font>
<font color="#000000">> </font>
<font color="#000000">> </font>

<font color="#000000">The data contains tabs... don't ask why... I don't have a clue :P</font>

<font color="#000000">I'll do something along the lines of sed... but not with sed, I'll use </font>
<font color="#000000">the command line interpreter on the OpenVMS systems where the extracts </font>
<font color="#000000">run. I just thought there might have been a quicker way to switch it off </font>
<font color="#000000">in the copy command, i.e. specifying "quote none" as one of the </font>
<font color="#000000">parameters to the command. I guess not...</font>

<font color="#000000">Thanks for the pointers.</font>

<font color="#000000">P.</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>