CODEWITHSUNDEEP
amazon-redshift
Balajee Addanki
Balajee Addanki

Reputation: 760

acceptanydate with dateformat 'auto' inserting nulls for a date column - Redshift

I am trying to get a Redshift table from a flat file with multiple date formats which is causing nulls to be inserted. My insert command looks like below:-

echo "COPY xxscty.daily_facebook_campaign from '${S3_BUCKET}/Society/20140701_20150315_campaign.csv' credentials as 'aws_access_key_id=${ACCESS_KEY};aws_secret_access_key=${SECRET_KEY}' acceptanydate dateformat 'auto' delimiter',' csv quote as '~' ACCEPTINVCHARS as '~' IGNOREHEADER 1"|psql "$PSQLARGS"

The reason why nulls are being inserted seem to be fairly sporadic with data being inserted for some and not for others of the same date format.

For example; the date column does get loaded with

1/07/2014 (DD/MM/YYYY)

but inserts null for

2014-07-13 (YYYY/MM/DD)

Upvotes: 1

Views: 2051

Answers (1)

Sameh Sharaf
Sameh Sharaf

Reputation: 1131

You need to use dateformat 'YYYY/MM/DD' instead of 'auto'.

Problem is, if you have different formats in the same file then it won't copy all dates.

Upvotes: 1

Related Questions