Delimiters other than Pipe and Tab

[rajasekhart]

Hi,

Do we have delimiters other than tab and pipe?

Actually, i want to load data using copy command in which pipe delimiter is used.

But if the data itself containing pipe symbol(|) as a character in a column (say col1), it is rejecting the rows , throwing the error as
Less number of columns found in the destination table.

The same case when i used tab delimiter.

So , can anyone help me how to solve this problem.

Thanks in advance ,
Raj

[id10t]

Hi Raj!

Does it helps Loading UTF-8 Format Data?

[johnr]

Hi Raj,

Were you successful in getting through this? I too have exactly the same problem. The delimiter is "|" and it is part of the data as well.
Would be good if you can share how did you get around the problem. That would be much appreciated.

Thanks,
-John

[rajasekhart]

Hi Johnr,

I followed a simple method to avoid this problem.

While generating the file i replaced the pipe symbol in the data with another unused symbol(say ~).
Then once file is generated with the pipe as delimiter, load that file into the table u wanted.
And then again replace the symbol(~) in the table data with pipe..

Correct me if i followed a lenghty process.

Thanks,
Raj.

[JimKnicely]

Hi,

Can you enclose the text containing the pipe symbol in your load file with double quotes and then add the ENCLOSED BY '"' option to the COPY command?

Following is an example...

The data file (notice all the | symbols in column one):

Code: Select all

dbadmin=> \! cat /usr/home/dbadmin/test.txt
"This|is|a|test|of|col1"|"This is a test of col2"

The COPY command:

Code: Select all

dbadmin=> copy test from '/usr/home/dbadmin/test.txt' enclosed by '"' exceptions '/usr/home/dbadmin/test.err';
 Rows Loaded
-------------
           1
(1 row)

The data loaded:

Code: Select all

dbadmin=> select * from test;
          col1          |          col2
------------------------+------------------------
 This|is|a|test|of|col1 | This is a test of col2
(1 row)

[rajasekhart]

Hi Knicely,

I too got the same idea, but the problem is "While generating the file , how can you enclose the columns with the double quote?".

If we are able to generate the file enclosed by double quote , then thats a good solution..

Kindly help me ...

Thanks,
Raj.

[JimKnicely]

Hi Raj,

How are you generating your data files? Does the tool you are using include an an option to enclose the text with quotes? Or do you already have data file and want to add the double quotes?

Thanks.