Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Load CSV Files into Snowflake: Step-by-Step Guide

To load a CSV file into Snowflake, first create a file format with TYPE = 'CSV', then upload the file to a Snowflake stage. Finally, use the COPY INTO command to load the data from the stage into a Snowflake table.
📐

Syntax

Loading CSV data into Snowflake involves three main steps:

  • Create a file format: Defines how Snowflake reads the CSV file.
  • Upload the CSV file to a stage: A temporary storage location in Snowflake.
  • Use COPY INTO command: Loads data from the stage into a table.

Each part is essential for correctly importing CSV data.

sql
CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

PUT file:///path/to/your/file.csv @~;

COPY INTO my_table
  FROM @~/file.csv
  FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
💻

Example

This example shows how to load a CSV file named data.csv into a table called users. It creates a file format, uploads the file to the user stage, and copies the data into the table.

sql
CREATE OR REPLACE FILE FORMAT csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

-- Upload the CSV file to the user stage
PUT file:///tmp/data.csv @~;

-- Load data from the stage into the users table
COPY INTO users
  FROM @~/data.csv
  FILE_FORMAT = (FORMAT_NAME = 'csv_format');
Output
COPY INTO users from @~/data.csv FILE_FORMAT = (FORMAT_NAME = 'csv_format') Rows loaded: 100 Files processed: 1 Load completed successfully.
⚠️

Common Pitfalls

Common mistakes when loading CSV files into Snowflake include:

  • Not creating or specifying the correct file format, causing parsing errors.
  • Forgetting to upload the file to a stage before running COPY INTO.
  • Incorrect file path or stage name in the COPY INTO command.
  • Not skipping the header row if the CSV has one, leading to errors or wrong data.

Always verify the file format matches your CSV structure and confirm the file is uploaded to the correct stage.

sql
/* Wrong: Missing file format and header skip */
COPY INTO users
  FROM @~/data.csv;

/* Right: Specify file format with header skip */
COPY INTO users
  FROM @~/data.csv
  FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
📊

Quick Reference

StepCommandPurpose
Create file formatCREATE FILE FORMAT my_csv_format TYPE='CSV' SKIP_HEADER=1;Defines CSV parsing rules
Upload filePUT file:///path/to/file.csv @~;Uploads CSV to user stage
Load dataCOPY INTO my_table FROM @~/file.csv FILE_FORMAT=(FORMAT_NAME='my_csv_format');Loads CSV data into table
✅

Key Takeaways

Create a file format to tell Snowflake how to read your CSV file.
Upload your CSV file to a Snowflake stage before loading.
Use the COPY INTO command with the correct file format to load data into a table.
Always skip the header row if your CSV has one to avoid loading it as data.
Check file paths and stage names carefully to prevent load errors.