admin管理员组

文章数量:1023866

I am trying to move some data from a CSV in S3 to a Redshift table. The date column is giving me a hard time however. When opened in a text editor, the date is formatted like this: "YYYY-MM-DD HH:MI:SS", however when I open the file in excel, the formula bar lists the date as YYYY-MM-DD HH:MI:SS AM/PM, and the actual table doesn't even include the seconds, or the AM/PM.

When I run the query in the image, I get this error: Date value did not match format specified [yyyy-MM-dd] And when I remove the DATEFORMAT line entirely, I get: Invalid Date Format - length must be 10 or more.

What am I doing wrong? The column in the table is expecting a date in the format of yyyy-MM-dd, but my boss assured me that the time shouldn't affect that
enter image description here

I am trying to move some data from a CSV in S3 to a Redshift table. The date column is giving me a hard time however. When opened in a text editor, the date is formatted like this: "YYYY-MM-DD HH:MI:SS", however when I open the file in excel, the formula bar lists the date as YYYY-MM-DD HH:MI:SS AM/PM, and the actual table doesn't even include the seconds, or the AM/PM.

When I run the query in the image, I get this error: Date value did not match format specified [yyyy-MM-dd] And when I remove the DATEFORMAT line entirely, I get: Invalid Date Format - length must be 10 or more.

What am I doing wrong? The column in the table is expecting a date in the format of yyyy-MM-dd, but my boss assured me that the time shouldn't affect that
enter image description here

  • amazon-web-services
  • amazon-s3
  • amazon-redshift
  • data-integration
  • data-engineering
Share Improve this question edited Nov 19, 2024 at 11:16 John Rotenstein 271k28 gold badges448 silver badges532 bronze badges asked Nov 18, 2024 at 23:25 plakosizzleplakosizzle 12 bronze badges 3
  • Did you try dateformat ‘auto’? Copy it in as a varchar, what exactly does it display? Never open csv in excel, it always mangles it – user433342 Commented Nov 19, 2024 at 2:05
  • Can you provide table DDL and the line form stl_load_errors? It could be many things. Is the column a date or a timestamp? Is it quoted? Is it failing on the first line? Is there a header row? etc. – Bill Weiner Commented Nov 19, 2024 at 2:36
  • Got it, thanks guys. The problem was the header line, once I added IGNOREHEADER 1, I was fine – plakosizzle Commented Nov 20, 2024 at 14:52
Add a comment  | 

1 Answer 1

Reset to default 0

I got the answer. My problem was that it was reading the header line as data. Once I added the line IGNOREHEADER 1, it worked. Somewhat misleading error.

I am trying to move some data from a CSV in S3 to a Redshift table. The date column is giving me a hard time however. When opened in a text editor, the date is formatted like this: "YYYY-MM-DD HH:MI:SS", however when I open the file in excel, the formula bar lists the date as YYYY-MM-DD HH:MI:SS AM/PM, and the actual table doesn't even include the seconds, or the AM/PM.

When I run the query in the image, I get this error: Date value did not match format specified [yyyy-MM-dd] And when I remove the DATEFORMAT line entirely, I get: Invalid Date Format - length must be 10 or more.

What am I doing wrong? The column in the table is expecting a date in the format of yyyy-MM-dd, but my boss assured me that the time shouldn't affect that
enter image description here

I am trying to move some data from a CSV in S3 to a Redshift table. The date column is giving me a hard time however. When opened in a text editor, the date is formatted like this: "YYYY-MM-DD HH:MI:SS", however when I open the file in excel, the formula bar lists the date as YYYY-MM-DD HH:MI:SS AM/PM, and the actual table doesn't even include the seconds, or the AM/PM.

When I run the query in the image, I get this error: Date value did not match format specified [yyyy-MM-dd] And when I remove the DATEFORMAT line entirely, I get: Invalid Date Format - length must be 10 or more.

What am I doing wrong? The column in the table is expecting a date in the format of yyyy-MM-dd, but my boss assured me that the time shouldn't affect that
enter image description here

  • amazon-web-services
  • amazon-s3
  • amazon-redshift
  • data-integration
  • data-engineering
Share Improve this question edited Nov 19, 2024 at 11:16 John Rotenstein 271k28 gold badges448 silver badges532 bronze badges asked Nov 18, 2024 at 23:25 plakosizzleplakosizzle 12 bronze badges 3
  • Did you try dateformat ‘auto’? Copy it in as a varchar, what exactly does it display? Never open csv in excel, it always mangles it – user433342 Commented Nov 19, 2024 at 2:05
  • Can you provide table DDL and the line form stl_load_errors? It could be many things. Is the column a date or a timestamp? Is it quoted? Is it failing on the first line? Is there a header row? etc. – Bill Weiner Commented Nov 19, 2024 at 2:36
  • Got it, thanks guys. The problem was the header line, once I added IGNOREHEADER 1, I was fine – plakosizzle Commented Nov 20, 2024 at 14:52
Add a comment  | 

1 Answer 1

Reset to default 0

I got the answer. My problem was that it was reading the header line as data. Once I added the line IGNOREHEADER 1, it worked. Somewhat misleading error.

本文标签： amazon web servicesCopying File to Redshift from S3 BucketStack Overflow

版权声明：本文标题：amazon web services - Copying File to Redshift from S3 Bucket - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745589158a2157782.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。