31

Is there an option to load a CSV into Redshift with a header? I see the documentation for CSV but it says nothing about a header. Ideally it could use the header to determine the columns to load.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Some Guy
  • 12,768
  • 22
  • 58
  • 86
  • With CSV the header is not helpful, but if you are using JSON is more descriptive : http://docs.aws.amazon.com/redshift/latest/dg/copy-usage_notes-copy-from-json.html – Guy Mar 02 '15 at 17:42

1 Answers1

61

Use the IGNOREHEADER 1 option when using the COPY command:

IGNOREHEADER [ AS ] number_rows

Treats the specified number_rows as a file header and does not load them. Use IGNOREHEADER to skip file headers in all files in a parallel load.

See: Amazon Redshift COPY command documentation

Community
  • 1
  • 1
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • 4
    Is there any way to have it use the header for knowing which columns to load? Ideally I could just say LOAD mytable FROM myfile.csv USING HEADER FOR COLUMNS, or something like that. – Some Guy Mar 01 '15 at 22:54
  • 1
    No, the Header will not be used to identify columns. You must provide column names as part of the COPY command and they will be loaded in the order provided in the file. – John Rotenstein Mar 01 '15 at 22:58
  • 1
    It was clear from the dococ but after experimenting I found the format is `COPY schema.table FROM.... CREDENTIALS...... IGNOREHEADER 1`. Don't forget the 1 (to skip one row) – Nick.Mc Feb 26 '16 at 09:06
  • A tool I wrote will create a table for you based on the CSV column headers, but Redshift alone doesn't know how to create a table based on a CSV you hand it: https://github.com/bluelabsio/records-mover (to be fair, I'm not sure if OP was looking to only load certain columns (and leave the rest NULL?) or create the table.) – apiology Apr 05 '20 at 20:35