How to Import CSV in Snowflake

Galaxy Glossary

How do I import a CSV file into Snowflake?

Importing a CSV loads external comma-separated data into Snowflake tables.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use COPY INTO for CSV imports?

COPY INTO streams CSV files from a stage into a table in parallel, handling compression, field delimiters, and errors automatically.

What permissions are required?

GRANT USAGE on the warehouse, database, schema, and stage plus INSERT on the target table.Without them, COPY INTO will fail.

How do I create a file format for CSV?

CREATE FILE FORMAT my_csv TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1; keeps quoted text intact and ignores the header row.

How do I stage the CSV file?

Put the file in an S3 bucket or internal stage: PUT file:///tmp/customers.csv @%Customers AUTO_COMPRESS=TRUE;

How do I load the file?

Run COPY INTO Customers FROM @mystage/customers.csv FILE_FORMAT = (FORMAT_NAME = my_csv); Snowflake maps columns by position.

How do I validate before loading?

Use COPY INTO ...VALIDATION_MODE='RETURN_ERRORS' to preview errors without inserting data.

Can I load multiple files at once?

YES: COPY INTO Customers FROM @mystage FILE_FORMAT=(FORMAT_NAME=my_csv) PATTERN='.*\.csv.gz';

How do I handle bad rows?

Add ON_ERROR='SKIP_FILE' or 'CONTINUE' to ignore faulty rows and complete the load.

Best practices for production loads

Compress files (gzip), keep them <100 MB, define explicit file formats, and log COPY_HISTORY to monitor load success.

How to automate daily CSV imports?

Create a task that calls a stored procedure containing the COPY INTO command.Schedule the task with a CRON expression.

Why How to Import CSV in Snowflake is important

How to Import CSV in Snowflake Example Usage


--Load Orders and OrderItems in one shot
COPY INTO Orders
FROM @ecommerce_stage/orders/
FILE_FORMAT = (FORMAT_NAME = ecommerce_csv)
PATTERN = 'orders_\\d{8}\\.csv(\\.gz)?';

COPY INTO OrderItems
FROM @ecommerce_stage/order_items/
FILE_FORMAT = (FORMAT_NAME = ecommerce_csv)
ON_ERROR = 'SKIP_FILE';

How to Import CSV in Snowflake Syntax


--Create CSV file format
CREATE OR REPLACE FILE FORMAT ecommerce_csv
TYPE = 'CSV'
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
SKIP_HEADER = 1;

--Create external stage on S3
CREATE OR REPLACE STAGE ecommerce_stage
URL = 's3://acme-data/ecommerce/'
CREDENTIALS = (aws_key_id='AKIA...' aws_secret_key='…');

--Load Customers CSV into table
COPY INTO Customers(id, name, email, created_at)
FROM @ecommerce_stage/customers.csv.gz
FILE_FORMAT = (FORMAT_NAME = ecommerce_csv)
ON_ERROR = 'CONTINUE';

Common Mistakes

Using INTERNAL CSV settings for UTF-8 files with BOM: causes misaligned columns. Fix by setting ENCODING='UTF8' and removing BOM before upload.
Relying on AUTO file format: Snowflake may infer wrong delimiters. Always CREATE FILE FORMAT with explicit CSV options to prevent load errors.