Wells Oliver asked:
>
>I have CSV files blowing up because they have double quotes around numeric values: " 1.34" yields ERROR: invalid
inputsyntax for type numeric: " 1.34"
>>
>And occasionally these are empty, and you see invalid syntax for type numeric: " "
>
>Are there flags I can pass to COPY to avoid this? There are properly quoted string values elsewhere in the final, so
I'mreticent to strip out double quote characters or anything.
>
There's no easy command line method that I know of. Perhaps if you can detect patterns some sed / awk might help.
I'd probably load the CSV file into a temporary work table with the columns that are giving you issues defined as type
"text."Then use SQL to remove the offending quotation marks from the afflicted columns. Create new columns of type
numericin the table, and copy the values from the text variant to the numeric ones with the proper cast one at a time;
ifit blows up you go back to resolving issues. Then copy the table using the numeric columns to the desired
destination.
Hopefully others might have better ideas ... best o' luck!
Greg Williamson