I have a CSV file in my S3 bucket. I created an external stage and used the following copy command:
SQL
copy into invoice from
(
SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39,
$40, $41, $42, $43, $44, $45 FROM
@invoicemain)
file_format = (type = csv field_delimiter = ',' RECORD_DELIMITER = '\n' skip_header = 1);
My table name is Invoice, and my stage name is invoicemain.
The command is working and it is loading data into the table. However, I have the following issue:
One of my columns in the CSV file is called address, which has the following format:
Steph John
Aaa bbbdkkk
Johnston St
Wellington
Nz 6194
The address column has values with a delimiter of the next line. The copy command considers this the next line, and the values get mixed up with other columns, resulting in inaccurate data. I have three address columns with the same format. Is there any way to address this issue in the copy command and get the correct data?
I am new to Snowflake and appreciate any help you can provide.