Working with CSV and TSV data in ClickHouse

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:

```
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
```

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

If we want a CSV file to work fine in a Windows environment, we should consider enabling [output\_format\_csv\_crlf\_end\_of\_line](/docs/operations/settings/formats#output_format_csv_crlf_end_of_line) option. This will use `\r\n` as a line breaks instead of `\n`:

```
SET output_format_csv_crlf_end_of_line = 1;
```

## Schema inference for CSV files[​](#schema-inference-for-csv-files "Direct link to Schema inference for CSV files")

We might work with unknown CSV files in many cases, so we have to explore which types to use for columns. Clickhouse, by default, will try to guess data formats based on its analysis of a given CSV file. This is known as "Schema Inference". Detected data types can be explored using the `DESCRIBE` statement in pair with the [file()](/docs/sql-reference/table-functions/file) function:

```
DESCRIBE file('data-small.csv', CSV)
```

```
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(Date)   │              │                    │         │                  │                │
│ c3   │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

Here, ClickHouse could guess column types for our CSV file efficiently. If we don't want ClickHouse to guess, we can disable this with the following option:

```
SET input_format_csv_use_best_effort_in_schema_inference = 0
```

All column types will be treated as a `String` in this case.

### Exporting and importing CSV with explicit column types[​](#exporting-and-importing-csv-with-explicit-column-types "Direct link to Exporting and importing CSV with explicit column types")

ClickHouse also allows explicitly setting column types when exporting data using [CSVWithNamesAndTypes](/docs/interfaces/formats/CSVWithNamesAndTypes) (and other \*WithNames formats family):

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

```
"path","month","hits"
"String","Date","UInt32"
"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
```

This format will include two header rows - one with column names and the other with column types. This will allow ClickHouse (and other apps) to identify column types when loading data from [such files](/docs/assets/files/data_csv_types-c74cceade0c1e9663917fce7866175d6.csv):

```
DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)
```

```
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ String │              │                    │         │                  │                │
│ month │ Date   │              │                    │         │                  │                │
│ hits  │ UInt32 │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

Now ClickHouse identifies column types based on a (second) header row instead of guessing.

## Custom delimiters, separators, and escaping rules[​](#custom-delimiters-separators-and-escaping-rules "Direct link to Custom delimiters, separators, and escaping rules")

In sophisticated cases, text data can be formatted in a highly custom manner but still have a structure. ClickHouse has a special [CustomSeparated](/docs/interfaces/formats/CustomSeparated) format for such cases, which allows setting custom escaping rules, delimiters, line separators, and starting/ending symbols.

Suppose we have the following data in the file:

```
row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...
```

We can see that individual rows are wrapped in `row()`, lines are separated with `,` and individual values are delimited with `;`. In this case, we can use the following settings to read data from this file:

```
SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';
```

Now we can load data from our custom formatted [file](/docs/assets/files/data_small_custom-6bea557c59b2b53ef70ec3936afa7d8d.txt):

```
SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3
```

```
┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘
```

We can also use [CustomSeparatedWithNames](/docs/interfaces/formats/CustomSeparatedWithNames) to get headers exported and imported correctly. Explore [regex and template](/docs/integrations/data-formats/templates-regexp) formats to deal with even more complex cases.

## Working with large CSV files[​](#working-with-large-csv-files "Direct link to Working with large CSV files")

CSV files can be large, and ClickHouse works efficiently with files of any size. Large files usually come compressed, and ClickHouse covers this with no need for decompression before processing. We can use a `COMPRESSION` clause during an insert:

```
INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV
```

If a `COMPRESSION` clause is omitted, ClickHouse will still try to guess file compression based on its extension. The same approach can be used to export files directly to compressed formats:

```
SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV
```

This will create a compressed `data_csv.csv.gz` file.

## Other formats[​](#other-formats "Direct link to Other formats")

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

- **CSV and TSV formats**
- [Parquet](/docs/integrations/data-formats/parquet)
- [JSON formats](/docs/integrations/data-formats/json/overview)
- [Regex and templates](/docs/integrations/data-formats/templates-regexp)
- [Native and binary formats](/docs/integrations/data-formats/binary-native)
- [SQL formats](/docs/integrations/data-formats/sql)

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without the need for Clickhouse server.