Thread: ERROR: COPY quote must be a single ASCII character

Dear all,

I have to copy the file with the following delimiters into the database:

||5354235||,||some text...||
||1234567||,||some text...||
||1234568||,||some text...||

The sql statement I defined was:

COPY testtable FROM 'test.txt' WITH DELIMITER AS ',' CSV QUOTE AS '||';

but the error:

ERROR: COPY quote must be a single ASCII character

Any solution? Thank you in advance.

Best, Andrej

Andrej Kastrin wrote:
> ||5354235||,||some text...|| ||1234567||,||some text...||
> ||1234568||,||some text...||
>
> The sql statement I defined was:
>
> COPY testtable FROM 'test.txt' WITH DELIMITER AS ',' CSV QUOTE AS
> '||';
>
> but the error:
>
> ERROR: COPY quote must be a single ASCII character

I guess I'm stating the obvious here, but can you just change your
'||' quotes to a single character within the file?

Colin

The answer is simple no. I know I can to some preprocessing on input
files using awk, sed etc. but don't want to change the structure of the
file.

Colin Wetherbee wrote:
> Andrej Kastrin wrote:
>> ||5354235||,||some text...|| ||1234567||,||some text...||
>> ||1234568||,||some text...||
>>
>> The sql statement I defined was:
>>
>> COPY testtable FROM 'test.txt' WITH DELIMITER AS ',' CSV QUOTE AS
>> '||';
>>
>> but the error:
>>
>> ERROR: COPY quote must be a single ASCII character
>
> I guess I'm stating the obvious here, but can you just change your '||'
> quotes to a single character within the file?
>
> Colin
>

Andrej Kastrin wrote:
>
> Colin Wetherbee wrote:
>> Andrej Kastrin wrote:
>>> ||5354235||,||some text...|| ||1234567||,||some text...||
>>> ||1234568||,||some text...||
>>>
>>> The sql statement I defined was:
>>>
>>> COPY testtable FROM 'test.txt' WITH DELIMITER AS ',' CSV QUOTE AS
>>> '||';
>>>
>>> but the error:
>>>
>>> ERROR: COPY quote must be a single ASCII character
>>
>> I guess I'm stating the obvious here, but can you just change your
>> '||' quotes to a single character within the file?
>>
>> Colin
>>
 > The answer is simple no. I know I can to some preprocessing on input
 > files using awk, sed etc. but don't want to change the structure of
 > the file.

OK, how about replacing '||' with '|' on a *copy* of your file, then?
It's not making that big a change to the structure, in any case.

Otherwise, you won't be able to do what it is that you wish to do. The
delimiter must be a single character, full stop.

brian

On Feb 8, 2008 9:06 AM, Andrej Kastrin <andrej.kastrin@siol.net> wrote:
> The answer is simple no. I know I can to some preprocessing on input
> files using awk, sed etc. but don't want to change the structure of the
> file.

If the problem is that you have | characters in your data, then can
you change the || to \t (tabs) for this load?  Or some other
character?