# Load CSV Files with LOAD DATA

Data from CSV files can be loaded using the LOAD DATA command. For more information on the LOAD DATA command, refer to [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md). To see a few examples of using LOAD DATA with CSV files, refer to [Load CSV Files Examples](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/load-csv-files-examples.md).

## Syntax

```sql
LOAD DATA [LOCAL] INFILE '<file_name>'
  [REPLACE | IGNORE | SKIP { ALL | CONSTRAINT | DUPLICATE KEY | PARSER } ERRORS]
  INTO TABLE <table_name>
  [CHARACTER SET <character_set_name>]
  [{FIELDS | COLUMNS}
  [TERMINATED BY '<string>']
      [[OPTIONALLY] ENCLOSED BY '<char>']
      [ESCAPED BY '<char>']
  ]
  [LINES
  [STARTING BY '<string>']
  [TERMINATED BY '<string>']
  ]
  [TRAILING NULLCOLS]
  [NULL DEFINED BY <string> [OPTIONALLY ENCLOSED]]
  [IGNORE <number> LINES]
  [ ({<column_name> | @<variable_name>}, ...) ]
  [SET <column_name> = <expression>,...]
  [WHERE <expression>,...]
  [MAX_ERRORS <number>]
  [ERRORS HANDLE <string>]

```

## Remarks

* Error Logging and Error Handling are discussed on the [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md) page.
* To specify the compression type of an input file, use the `COMPRESSION` clause. See [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data/#UUID-55530b3c-3939-f404-a8e0-db8a648b36fd.md) for more information.
* If a CSV file appears to have the incorrect number of fields in any line, you can use the `SKIP PARSER ERRORS` option to skip the line. `LOAD DATA` reports a warning for every line that is skipped.
  > **❗ Important**: Lines in a CSV file may appear to have the wrong number of fields if the `FIELDS TERMINATED BY`, `FIELDS ENCLOSED BY`, or `ESCAPED BY` clauses are incorrectly configured. If `LOAD DATA` incorrectly finds the start of the next line in a CSV after a parser error, it may parse all the subsequent lines incorrectly. For these reasons, investigate the CSV input and configuration settings mentioned above before using `SKIP PARSER ERRORS`.
* The `SKIP ALL ERRORS` option is inclusive of the `SKIP PARSER ERRORS`, `SKIP DUPLICATE KEY ERRORS` and `SKIP CONSTRAINT ERRORS` options, i.e., specifying the `SKIP ALL ERRORS` option in a `LOAD DATA` query applies the behavior of the other three options.
* The `TERMINATED BY` clause allows you to define field, column, and line delimiters so that the input data is interpreted and read correctly. For example, use `FIELDS TERMINATED BY`clause to load a CSV file where the fields are delimited by commas. Additionally, use the `LINES TERMINATED BY '\r\n'` clause if the lines in the CSV file are terminated by carriage return/newline pairs.
* The `ENCLOSED BY` or equivalent `OPTIONALLY ENCLOSED BY` clause allows you to specify a string that encloses the field values. For example, use the `ENCLOSED BY '"'` clause to load a CSV file where the fields are enclosed within double quotation. Note that `LOAD DATA` will still load a field value even if it is not enclosed.
* The `ESCAPED BY` clause allows you to specify the escape character. For example, if the input data contains special character(s), you may need to escape those characters to avoid misinterpretation. Also, you may need to redefine the default escape character to load a data set that contains the said character.
* Many characters can be an escape. If the `FIELDS ESCAPED BY` clause is empty, the character escape sequence will do nothing.
* The `STARTING BY` clause allows you to load only those lines of data that include a specified string (or prefix). While loading data, the `STARTING BY` clause skips the specified prefix and anything before it. It also skips the lines that do not contain the specified prefix.

  If no `FIELDS` or `LINES` clause is specified, then SingleStore uses the following defaults:
  ```sql
  FIELDS TERMINATED BY '\t'
  ENCLOSED BY ''
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
  STARTING BY ''
  ```

* The `TRAILING NULLCOLS` clause allows the input file to contain rows having fewer than the number of columns in the table. These missing fields must be trailing columns in the row; they are inserted as `NULL` values in the table. See [Load CSV Files Examples](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/load-csv-files-examples/#section-idm43148153520831107.md).

* The `NULL DEFINED BY` clause inserts `NULL` field values in the table for fields in the input file having the value `string_to_insert_as_null`. The `OPTIONALLY ENCLOSED` option ensures that a quoted field is also treated as `NULL`, not an empty string. Refer to [Load CSV Files Examples](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/load-csv-files-examples/#section-idm43176041242399315.md) for more information.
  > **📝 Note**: If the string value `'NULL'` is passed to a number-type column (for example, DECIMAL), it is parsed as a string and converted to 0. To insert `NULL` values instead, use the `NULL DEFINED BY 'NULL' OPTIONALLY ENCLOSED` clause. You can use the `ENCLOSED BY` clause in conjunction to specify the string that encloses the `NULL` values.
* The `IGNORE <number> LINES` clause ignores the specified lines from the beginning of the input file. For example, use `IGNORE 1 LINES` to skip the header line that contains the column names.

## LOAD DATA from an AWS S3 Source

CSV files that are stored in an AWS S3 bucket can be loaded via a LOAD DATA query without a pipeline.

```sql
LOAD DATA S3 '<bucket name>'
CONFIG '{"region" : "<region_name>"}' 
CREDENTIALS '{"aws_access_key_id" : "<key_id>", 
             "aws_secret_access_key": "<access_key>"}' 
INTO TABLE <table_name>;

```

***

Modified at: July 25, 2025

Source: [/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/load-csv-files-with-load-data/](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/load-csv-files-with-load-data/)

(An index of the documentation is available at /llms.txt)