importing data with commas in numeric fields into

Question

I am importing data into redshift using the SQL COPY statement. The data has comma thousands separators in the numeric fields which the COPY statement rejects.

The COPY statement has a number of options to specify field separators, date and time formats and NULL values. However I do not see anything to specify number formatting.

Do I need to preprocess the data before loading or is there a way to get redshift to parse the numbers corerctly?

Answer 1

1. Import the columns as TEXT data type in a temporary table
2. Insert the temporary table to your target table. Have your SELECT statement for the INSERT replace commas with empty strings, and cast the values to the correct numeric type.