COPY file with pipe in string

Navin_C

Hello All, I have come across a scenario where we need to load some data in the Vertica table. The data here is in a special format Flat file Code: Select all A|B|C|D|edfg|hi|jk|L|J|R| A|B|C|D|hdjf|ih|gm|L|J|R| Here the data in small letters has 2 pipes in them, which Vertica takes as delimitters, But it is a part of one string in the 5th column of flat file. So that completely is a string which needs to be loaded into single column in Vertica table. I am using the copy command for this, but Vertica gives "Column mismatch error" as interpreted by Vertica. How can I load this data into Vertica in a table with 8 columns.

eli_revach

Hi The default COPY delimiter is a vertical bar (|). You can define a different delimiter using any ASCII value in the range E'\000' to E'\177' see more deatils on - >https://my.vertica.com/docs/6.1.x/HTML/index.htm#2590.htm

Navin_C

Hello eli, My flat file comes with '|' as a delimiter, so I cannot change my delimitter as all other column have the same delimiter.

eli_revach

You can load it as it and hendel the reject seperatly vertica will create for you the reject file for you ,and for the case of the reject you can use some kind of awk to replace the pipes with someother chars excluding the one which you like to load

Prasanta_Pal

Look at the below link to explore different options: https://my.vertica.com/docs/6.1.x/HTML/index.htm#2590.htm