Working with CSV and TSV data in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-formats/csv-tsv.md)

ClickHouse supports importing data from and exporting to CSV. Since CSV files can come with different format specifics, including header rows, custom delimiters, and escape symbols, ClickHouse provides formats and settings to address each case efficiently.

## Importing data from a CSV file[​](#importing-data-from-a-csv-file "Direct link to Importing data from a CSV file")

Before importing data, let's create a table with a relevant structure:

```
CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)
```

To import data from the [CSV file](/docs/assets/files/data_small-221917c8bad6ba5f809d442a9e8e40b5.csv) to the `sometable` table, we can pipe our file directly to the clickhouse-client:

```
clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv
```

Note that we use [FORMAT CSV](/docs/interfaces/formats/CSV) to let ClickHouse know we're ingesting CSV formatted data. Alternatively, we can load data from a local file using the [FROM INFILE](/docs/sql-reference/statements/insert-into#inserting-data-from-a-file) clause:

```
INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV
```

Here, we use the `FORMAT CSV` clause so ClickHouse understands the file format. We can also load data directly from URLs using [url()](/docs/sql-reference/table-functions/url) function or from S3 files using [s3()](/docs/sql-reference/table-functions/s3) function.

Tip

We can skip explicit format setting for `file()` and `INFILE`/`OUTFILE`.
In that case, ClickHouse will automatically detect format based on file extension.

### CSV files with headers[​](#csv-files-with-headers "Direct link to CSV files with headers")

Suppose our [CSV file has headers](/docs/assets/files/data_small_headers-a6392414b05dc22a901590ca85594a03.csv) in it:

```
head data-small-headers.csv
```

```
"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
```

To import data from this file, we can use [CSVWithNames](/docs/interfaces/formats/CSVWithNames) format:

```
clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv
```

In this case, ClickHouse skips the first row while importing data from the file.

Tip

Starting from [version](https://github.com/ClickHouse/ClickHouse/releases) 23.1, ClickHouse will automatically detect headers in CSV files when using the `CSV` format, so it isn't necessary to use `CSVWithNames` or `CSVWithNamesAndTypes`.

### CSV files with custom delimiters[​](#csv-files-with-custom-delimiters "Direct link to CSV files with custom delimiters")

In case the CSV file uses other than comma delimiter, we can use the [format\_csv\_delimiter](/docs/operations/settings/formats#format_csv_delimiter) option to set the relevant symbol:

```
SET format_csv_delimiter = ';'
```

Now, when we import from a CSV file, `;` symbol is going to be used as a delimiter instead of a comma.

### Skipping lines in a CSV file[​](#skipping-lines-in-a-csv-file "Direct link to Skipping lines in a CSV file")

Sometimes, we might skip a certain number of lines while importing data from a CSV file. This can be done using [input\_format\_csv\_skip\_first\_lines](/docs/operations/settings/formats#input_format_csv_skip_first_lines) option:

```
SET input_format_csv_skip_first_lines = 10
```

In this case, we're going to skip the first ten lines from the CSV file:

```
SELECT count(*) FROM file('data-small.csv', CSV)
```

```
┌─count()─┐
│     990 │
└─────────┘
```

The [file](/docs/assets/files/data_small-221917c8bad6ba5f809d442a9e8e40b5.csv) has 1k rows, but ClickHouse loaded only 990 since we've asked to skip the first 10.

Tip

When using the `file()` function, with ClickHouse Cloud you will need to run the commands in `clickhouse client` on the machine where the file resides. Another option is to use [`clickhouse-local`](/docs/operations/utilities/clickhouse-local) to explore files locally.

### Treating NULL values in CSV files[​](#treating-null-values-in-csv-files "Direct link to Treating NULL values in CSV files")

Null values can be encoded differently depending on the application that generated the file. By default, ClickHouse uses `\N` as a Null value in CSV. But we can change that using the [format\_csv\_null\_representation](/docs/operations/settings/formats#format_tsv_null_representation) option.

Suppose we have the following CSV file:

```
> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70
```

If we load data from this file, ClickHouse will treat `Nothing` as a String (which is correct):

```
SELECT * FROM file('nulls.csv')
```

```
┌─c1──────┬─c2──────┐
│ Donald  │ 90      │
│ Joe     │ Nothing │
│ Nothing │ 70      │
└─────────┴─────────┘
```

If we want ClickHouse to treat `Nothing` as `NULL`, we can define that using the following option:

```
SET format_csv_null_representation = 'Nothing'
```

Now we have `NULL` where we expect it to be:

```
SELECT * FROM file('nulls.csv')
```

```
┌─c1─────┬─c2───┐
│ Donald │ 90   │
│ Joe    │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ   │ 70   │
└────────┴──────┘
```

## TSV (tab-separated) files[​](#tsv-tab-separated-files "Direct link to TSV (tab-separated) files")

Tab-separated data format is widely used as a data interchange format. To load data from a [TSV file](/docs/assets/files/data_small-71d90f4a40aa3cda835ab0feb8a7b588.tsv) to ClickHouse, the [TabSeparated](/docs/interfaces/formats/TabSeparated) format is used:

```
clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv
```

There's also a [TabSeparatedWithNames](/docs/interfaces/formats/TabSeparatedWithNames) format to allow working with TSV files that have headers. And, like for CSV, we can skip the first X lines using the [input\_format\_tsv\_skip\_first\_lines](/docs/operations/settings/formats#input_format_tsv_skip_first_lines) option.

### Raw TSV[​](#raw-tsv "Direct link to Raw TSV")

Sometimes, TSV files are saved without escaping tabs and line breaks. We should use [TabSeparatedRaw](/docs/interfaces/formats/TabSeparatedRaw) to handle such files.

## Exporting to CSV[​](#exporting-to-csv "Direct link to Exporting to CSV")

Any format in our previous examples can also be used to export data. To export data from a table (or a query) to a CSV format, we use the same `FORMAT` clause:

```
SELECT *
FROM sometable
LIMIT 5
FORMAT CSV
```

```
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86
```

To add a header to the CSV file, we use the [CSVWithNames](/docs/interfaces/formats/CSVWithNames) format:

```
SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames
```

```
"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86
```

### Saving exported data to a CSV file[​](#saving-exported-data-to-a-csv-file "Direct link to Saving exported data to a CSV file")

To save exported data to a file, we can use the [INTO...OUTFILE](/docs/sql-reference/statements/select/into-outfile) clause:

```
SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames
```

```
36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)
```

Note how it took ClickHouse **~1** second to save 36m rows to a CSV file.

### Exporting CSV with custom delimiters[​](#exporting-csv-with-custom-delimiters "Direct link to Exporting CSV with custom delimiters")

If we want to have other than comma delimiters, we can use the [format\_csv\_delimiter](/docs/operations/settings/formats#format_csv_delimiter) settings option for that:

```
SET format_csv_delimiter = '|'
```

Now ClickHouse will use `|` as a delimiter for CSV format: