How to transform data when we have comma separated values in csv format file in snowflake

Question

I have an excel csv format data set with the following data:

Columns: id, product_name, sales, quantity, Profit

Data: 1, "Novimex Executive Leather Armchair, Black","$3,709.40", 9, -$288.77

When I am trying to insert these records from stage to snowflake table, data is getting shifted from product name column because we have comma separated , Black and similarly for following columns data are getting shifted. After loading the data it is looking like as per below:

+----+-------------------------------------+--------+----------+---------+
| id |            product_name             | sales  | quantity | Profit  |
+----+-------------------------------------+--------+----------+---------+
|  1 | "Novimex Executive Leather Armchair | Black" | $3       | 709.40" |
+----+-------------------------------------+--------+----------+---------+

Query used:

copy into orders_staging (id,Product_Name,Sales,Quantity,Profit) 
from
(select $1,$2,$3,$4,$5
from @sales_data_stage)
file_format = (type = csv field_delimiter = ',' skip_header = 1 ENCODING = 'iso-8859-1');

Answer 1

Use Field Enclosure.

FIELD_OPTIONALLY_ENCLOSED_BY='"'

If you have any issues with accounting styled numbers, remember to put " " around them too.

https://community.snowflake.com/s/question/0D50Z00008pDcoRSAS/copying-csv-files-delimited-by-commas-where-commas-are-also-enclosed-in-strings

Additional documentation for Copy To

https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#type-csv

Additional documentation on the Create File

https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html