COPY file with pipe in string
+2Hello 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.
+2Hi 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
+2Hello eli, My flat file comes with '|' as a delimiter, so I cannot change my delimitter as all other column have the same delimiter.
+2You 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
Look at the below link to explore different options: https://my.vertica.com/docs/6.1.x/HTML/index.htm#2590.htm
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
