How to solve error "Field delimiter ',' found while expecting record delimiter '
'" while loading json data to the stage

Question

I am trying to "COPY INTO" command to load data from s3 to the snowflake

Below are the steps I followed to create the stage and loading file from stage to Snowflake

JSON file

{
   "Name":"Umesh",
   "Desigantion":"Product Manager",
   "Location":"United Kingdom"
}

create or replace stage emp_json_stage
url='s3://mybucket/emp.json'
credentials=(aws_key_id='my id' aws_secret_key='my key');

# create the table with variant
CREATE TABLE emp_json_raw (
  json_data_raw VARIANT
);

#load data from stage to snowflake

COPY INTO emp_json_raw from @emp_json_stage;

I am getting below error

Field delimiter ',' found while expecting record delimiter ' ' File 'emp.json', line 2, character 18 Row 2, column "emp_json_raw"["JSON_DATA_RAW":1]

I am using a simple JSON file, and I don't understand this error.

What causes it and how can I solve it?

user_4987448 · Answer

File format is not specified and is defaulting to CSV format hence the error.

Try this:

COPY INTO emp_json_raw 
from @emp_json_stage
file_format=(TYPE=JSON);

There are other options too that can be specified with file_format other than TYPE. Refer the documentation here: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#type-json

user_1352490 · Answer

try:

file_format = (type = csv field_optionally_enclosed_by='"')

The default settings do not expect the " wrapping around your data. So you could strip all the " or ... just set the field_optionally_enclosed_by to a ". This does mean if your data has " in it things get messy.

https://docs.snowflake.com/en/user-guide/getting-started-tutorial-copy-into.html

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

user_14442664 · Answer

Also have a standard practice to mention type of file either it could be CSV, JSON ,AVRO , Parquet etc.

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

How to solve error "Field delimiter ',' found while expecting record delimiter '\n'" while loading json data to the stage

JSON file

3 Answers