Data Import
Detection Steps
#### Dialect Detection {#docs:data:csv:auto_detection::dialect-detection}  
Dialect detection works by attempting to parse the samples using the set of considered values. The detected dialect is the dialect that has (1) a consistent number of columns for each row, and (2) the highest number of columns for each row.  
The following dialects are considered for automatic dialect detection.  
<div class="narrow_table"></div>  
<!-- markdownlint-disable MD056 -->  
| Parameters | Considered values     |
|------------|-----------------------|
| `delim`    | `,` `|` `;` `\t`      |
| `quote`    | `"` `'` (empty)       |
| `escape`   | `"` `'` `\` (empty)   |  
<!-- markdownlint-enable MD056 -->  
Consider the example file [`flights.csv`](https://duckdb.org/data/flights.csv):  
```csv
FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-01|AA|New York, NY|Los Angeles, CA
1988-01-02|AA|New York, NY|Los Angeles, CA
1988-01-03|AA|New York, NY|Los Angeles, CA
```  
In this file, the dialect detection works as follows:  
* If we split by a `|` every row is split into `4` columns
* If we split by a `,` rows 2-4 are split into `3` columns, while the first row is split into `1` column
* If we split by `;`, every row is split into `1` column
* If we split by `\t`, every row is split into `1` column  
In this example – the system selects the `|` as the delimiter. All rows are split into the same amount of columns, and there is more than one column per row meaning the delimiter was actually found in the CSV file.  
#### Type Detection {#docs:data:csv:auto_detection::type-detection}  
After detecting the dialect, the system will attempt to figure out the types of each of the columns. Note that this step is only performed if we are calling `read_csv`. In case of the `COPY` statement the types of the table that we are copying into will be used instead.  
The type detection works by attempting to convert the values in each column to the candidate types. If the conversion is unsuccessful, the candidate type is removed from the set of candidate types for that column. After all samples have been handled – the remaining candidate type with the highest priority is chosen. The default set of candidate types is given below, in order of priority:  
<div class="narrow_table monospace_table"></div>  
|   Types   |
|-----------|
| BOOLEAN   |
| BIGINT    |
| DOUBLE    |
| TIME      |
| DATE      |
| TIMESTAMP |
| VARCHAR   |  
Note everything can be cast to `VARCHAR`. This type has the lowest priority, i.e., columns are converted to `VARCHAR` if they cannot be cast to anything else. In [`flights.csv`](https://duckdb.org/data/flights.csv) the `FlightDate` column will be cast to a `DATE`, while the other columns will be cast to `VARCHAR`.  
The set of candidate types that should be considered by the CSV reader can be explicitly specified using the [`auto_type_candidates`](#docs:data:csv:overview::auto_type_candidates-details) option.  
In addition to the default set of candidate types, other types that may be specified using the `auto_type_candidates` options are:  
<div class="narrow_table monospace_table"></div>  
|   Types   |
|-----------|
| DECIMAL   |
| FLOAT     |
| INTEGER   |
| SMALLINT  |
| TINYINT   |  
Even though the set of data types that can be automatically detected may appear quite limited, the CSV reader can configured to read arbitrarily complex types by using the `types`-option described in the next section.  
Type detection can be entirely disabled by using the `all_varchar` option. If this is set all columns will remain as `VARCHAR` (as they originally occur in the CSV file).