Fix for Vertica ERROR 2035 COPY Invalid integer format

Ok, i have spent about 2 hours today trying to understand what is wrong with a loading script of mine. The is getting loaded but the 1st line. Content of the file: 

[dbadmin@node]$ head File.csv
ID
201
222
224
228
229
231
232
233
235
  • it looks like all are integer values ! right ?
The file type is UTF-8 so is good ! Now there is leading character called the BOM Marker in the file so this was the reason my file was not getting loaded and end-up with this error: 
ERROR 2035: COPY: Input record 1 has been rejected (Invalid integer format '201' for column 1 (id))

 So what is the fix for this ?

  • remove the the BOM marker using sed(good old sed :) ).
sed -i 's/\xEF\xBB\xBF//' File.csv

To do it for a file that get the name randomly generated sed wont accept  (*)wildcard for a in-place operation so i came-up with this script.

sed -i 's/\xEF\xBB\xBF//' ls -1 | grep File*
I hope this was helpful for all that face the same errro :) a

Copyright 2020. All Rights Reserved