4

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Daniel Mahler
  • 7,653
  • 5
  • 51
  • 90
  • Possible duplicate of [How to change decimal separator from comma to fullstop in redshift copy command](http://stackoverflow.com/questions/27274585/how-to-change-decimal-separator-from-comma-to-fullstop-in-redshift-copy-command) – moertel Aug 22 '16 at 18:48

1 Answers1

2
  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.
Ron Dunn
  • 2,971
  • 20
  • 27