title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/CustomSeparated/CustomSeparated.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✔ ✔  | | | | | |

## Description[​](#description "Direct link to Description")

Similar to [Template](/docs/interfaces/formats/Template), but it prints or reads all names and types of columns and uses escaping rule from [format\_custom\_escaping\_rule](/docs/operations/settings/formats#format_custom_escaping_rule) setting and delimiters from the following settings:

- [format\_custom\_field\_delimiter](/docs/operations/settings/formats#format_custom_field_delimiter)
- [format\_custom\_row\_before\_delimiter](/docs/operations/settings/formats#format_custom_row_before_delimiter)
- [format\_custom\_row\_after\_delimiter](/docs/operations/settings/formats#format_custom_row_after_delimiter)
- [format\_custom\_row\_between\_delimiter](/docs/operations/settings/formats#format_custom_row_between_delimiter)
- [format\_custom\_result\_before\_delimiter](/docs/operations/settings/formats#format_custom_result_before_delimiter)
- [format\_custom\_result\_after\_delimiter](/docs/operations/settings/formats#format_custom_result_after_delimiter)

Note

It does not use escaping rules settings and delimiters from format strings.

There is also the [`CustomSeparatedIgnoreSpaces`](/docs/interfaces/formats/CustomSeparatedIgnoreSpaces) format, which is similar to [TemplateIgnoreSpaces](/docs/interfaces/formats/TemplateIgnoreSpaces).

## Example usage[​](#example-usage "Direct link to Example usage")

### Inserting data[​](#inserting-data "Direct link to Inserting data")

Using the following txt file, named as `football.txt`:

```
row('2022-04-30';2021;'Sutton United';'Bradford City';1;4),row('2022-04-30';2021;'Swindon Town';'Barrow';2;1),row('2022-04-30';2021;'Tranmere Rovers';'Oldham Athletic';2;0),row('2022-05-02';2021;'Salford City';'Mansfield Town';2;2),row('2022-05-02';2021;'Port Vale';'Newport County';1;2),row('2022-05-07';2021;'Barrow';'Northampton Town';1;3),row('2022-05-07';2021;'Bradford City';'Carlisle United';2;0),row('2022-05-07';2021;'Bristol Rovers';'Scunthorpe United';7;0),row('2022-05-07';2021;'Exeter City';'Port Vale';0;1),row('2022-05-07';2021;'Harrogate Town A.F.C.';'Sutton United';0;2),row('2022-05-07';2021;'Hartlepool United';'Colchester United';0;2),row('2022-05-07';2021;'Leyton Orient';'Tranmere Rovers';0;1),row('2022-05-07';2021;'Mansfield Town';'Forest Green Rovers';2;2),row('2022-05-07';2021;'Newport County';'Rochdale';0;2),row('2022-05-07';2021;'Oldham Athletic';'Crawley Town';3;3),row('2022-05-07';2021;'Stevenage Borough';'Salford City';4;2),row('2022-05-07';2021;'Walsall';'Swindon Town';0;3)
```

Configure the custom delimiter settings:

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

Insert the data:

```
INSERT INTO football FROM INFILE 'football.txt' FORMAT CustomSeparated;
```

### Reading data[​](#reading-data "Direct link to Reading data")

Configure the custom delimiter settings:

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

Read data using the `CustomSeparated` format:

```
SELECT *
FROM football
FORMAT CustomSeparated
```

The output will be in the configured custom format:

```
row('2022-04-30';2021;'Sutton United';'Bradford City';1;4),row('2022-04-30';2021;'Swindon Town';'Barrow';2;1),row('2022-04-30';2021;'Tranmere Rovers';'Oldham Athletic';2;0),row('2022-05-02';2021;'Port Vale';'Newport County';1;2),row('2022-05-02';2021;'Salford City';'Mansfield Town';2;2),row('2022-05-07';2021;'Barrow';'Northampton Town';1;3),row('2022-05-07';2021;'Bradford City';'Carlisle United';2;0),row('2022-05-07';2021;'Bristol Rovers';'Scunthorpe United';7;0),row('2022-05-07';2021;'Exeter City';'Port Vale';0;1),row('2022-05-07';2021;'Harrogate Town A.F.C.';'Sutton United';0;2),row('2022-05-07';2021;'Hartlepool United';'Colchester United';0;2),row('2022-05-07';2021;'Leyton Orient';'Tranmere Rovers';0;1),row('2022-05-07';2021;'Mansfield Town';'Forest Green Rovers';2;2),row('2022-05-07';2021;'Newport County';'Rochdale';0;2),row('2022-05-07';2021;'Oldham Athletic';'Crawley Town';3;3),row('2022-05-07';2021;'Stevenage Borough';'Salford City';4;2),row('2022-05-07';2021;'Walsall';'Swindon Town';0;3)
```

## Format settings[​](#format-settings "Direct link to Format settings")

Additional settings:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description Default|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [input\_format\_custom\_detect\_header](/docs/operations/settings/formats#input_format_custom_detect_header) enables automatic detection of header with names and types if any. `true`| [input\_format\_custom\_skip\_trailing\_empty\_lines](/docs/operations/settings/formats#input_format_custom_skip_trailing_empty_lines) skip trailing empty lines at the end of file. `false`| [input\_format\_custom\_allow\_variable\_number\_of\_columns](/docs/operations/settings/formats#input_format_custom_allow_variable_number_of_columns) allow variable number of columns in CustomSeparated format, ignore extra columns and use default values for missing columns. `false` | | | | | | | | | | | |

- [Description](#description)- [Example usage](#example-usage)
    - [Inserting data](#inserting-data)- [Reading data](#reading-data)- [Format settings](#format-settings)

Was this page helpful?