> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/reference-sql/functions-reference/table-valued/read_csv",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Reference material for READ_CSV function

# READ_CSV

A table-valued function (TVF) that reads CSV files from Amazon S3. The function can use either a location object (recommended) or direct credentials to access the data. `READ_CSV` returns a table with data from the specified CSV file, where each cell is read as `TEXT`.

## Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- Using LOCATION object (recommended)
READ_CSV ( 
  LOCATION => 'location_name'
  [, PATTERN => <pattern>]
  [, COMPRESSION => <file_compression>]
  [, HEADER => { TRUE | FALSE }]
  [, DELIMITER => <field_delimiter>]
  [, QUOTE => { "'" | '"' | SINGLE_QUOTE | DOUBLE_QUOTE}]
  [, NULL_STRING => <null_string>]
  [, ESCAPE => <escape_character>]
  [, SKIP_BLANK_LINES => { TRUE | FALSE }]
  [, EMPTY_FIELD_AS_NULL => { TRUE | FALSE }]
  [, INFER_SCHEMA => { TRUE | FALSE }]
)
|
-- Using static credentials
READ_CSV ( 
  URL => <url>
  [, COMPRESSION => <file_compression>]
  [, AWS_ACCESS_KEY_ID => <aws_access_key_id>]
  [, AWS_SECRET_ACCESS_KEY => <aws_secret_access_key>]
  [, AWS_SESSION_TOKEN => <aws_session_token>]
  [, AWS_ROLE_ARN => <aws_role_arn>]
  [, AWS_ROLE_EXTERNAL_ID => <aws_role_external_id>]
  [, HEADER => { TRUE | FALSE }]
  [, DELIMITER => <field_delimiter>]
  [, QUOTE => { "'" | '"' | SINGLE_QUOTE | DOUBLE_QUOTE}]
  [, NULL_STRING => <null_string>]
  [, ESCAPE => <escape_character>]
  [, SKIP_BLANK_LINES => { TRUE | FALSE }]
  [, EMPTY_FIELD_AS_NULL => { TRUE | FALSE }]
  [, INFER_SCHEMA => { TRUE | FALSE }]
)
```

## Parameters

| Parameter               | Description                                                                                                                                                                                                                                                                                                                                                                                  | Supported input types |
| :---------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| LOCATION                | The name of a location object that contains the Amazon S3 URL and credentials. Firebolt recommends using `LOCATION` to store credentials for authentication . `LOCATION` must be specified as a string literal (e.g., `LOCATION => 'my_location'`). Unlike `URL`, it cannot be used as a positional parameter. For a comprehensive guide, see [LOCATION objects](/guides/security/location). | `TEXT`                |
| `PATTERN`               | When using `LOCATION`, an optional glob pattern to filter files within the location's URL path. The pattern is applied relative to the location's base path. For example, `PATTERN => 'week_1/*.csv'` will match all `.csv` files in the `week_1` subdirectory.                                                                                                                              | `TEXT`                |
| `URL`                   | The location containing your files in an Amazon S3 bucket. The expected format is `s3://{bucket_name}/{full_file_path_glob_pattern}`.                                                                                                                                                                                                                                                        | `TEXT`                |
| `COMPRESSION`           | The [compression type](/reference-sql/commands/data-definition/create-external-table#compression) of the input file. If `compression` is not set, `compression` is inferred from the file extension.                                                                                                                                                                                         | `TEXT`                |
| `AWS_ACCESS_KEY_ID`     | The AWS access key ID.                                                                                                                                                                                                                                                                                                                                                                       | `TEXT`                |
| `AWS_SECRET_ACCESS_KEY` | The AWS secret access key.                                                                                                                                                                                                                                                                                                                                                                   | `TEXT`                |
| `AWS_SESSION_TOKEN`     | The AWS session token.                                                                                                                                                                                                                                                                                                                                                                       | `TEXT`                |
| `AWS_ROLE_ARN`          | The AWS role arn.                                                                                                                                                                                                                                                                                                                                                                            | `TEXT`                |
| `AWS_ROLE_EXTERNAL_ID`  | The AWS role external ID.                                                                                                                                                                                                                                                                                                                                                                    | `TEXT`                |
| `HEADER`                | Set to `TRUE` if the first row of the CSV file contains a header row containing the column names.                                                                                                                                                                                                                                                                                            | `BOOL`                |
| `DELIMITER`             | Specify the character used to separate fields. The default delimiter is a comma (`,`).                                                                                                                                                                                                                                                                                                       | `TEXT`                |
| `QUOTE`                 | Specify the character used for quoting fields. The default is double quote (`"`). If a single quote is specified, the quote character will be set to (`'`). Accepts only `DOUBLE_QUOTE`, `SINGLE_QUOTE`, `'`, or `"`.                                                                                                                                                                        | `TEXT`                |
| `NULL_STRING`           | Specify the string used to represent `NULL` values. The default is an empty string, which means that empty strings are interpreted as `NULL` values.                                                                                                                                                                                                                                         | `TEXT`                |
| `ESCAPE`                | Specify the character used to escape special characters. The default character is the quote (`'`) character.                                                                                                                                                                                                                                                                                 | `TEXT`                |
| `SKIP_BLANK_LINES`      | Set to `TRUE` to ignore blank lines in the file.                                                                                                                                                                                                                                                                                                                                             | `BOOL`                |
| `EMPTY_FIELD_AS_NULL`   | Specify whether empty fields should be interpreted as `NULL` values. The default is `TRUE`. If set to `FALSE`, empty fields are interpreted as empty strings.                                                                                                                                                                                                                                | `BOOL`                |
| `INFER_SCHEMA`          | Specify whether column data types should be inferred from the data, instead of using `TEXT`.                                                                                                                                                                                                                                                                                                 | `BOOL`                |

## Return Type

The result is a table with the data from the CSV file. Each cell is read as a `TEXT`.

<Note>
  **When loading multiple files, Firebolt infers the schema from the most recently modified file.** The remaining files must have compatible data types. If types vary between files (e.g., a column contains integers in one file but doubles in another, or is numeric in one file but text in another), the inferred schema may not match all files and thus cause data type errors or query failures. In such cases, we recommend defining an explicit schema using either [external tables](/reference-sql/commands/data-definition/create-external-table) or [`COPY FROM`](/reference-sql/commands/data-management/copy-from) into existing tables.
</Note>

## Examples

### Using LOCATION object

**Best practice**

Firebolt recommends using a `LOCATION` object to store credentials for authentication.

When using `READ_CSV()`, the URL parameter in the location should contain only CSV files (see [location table-valued functions](https://docs.firebolt.io/guides/security/location#table-valued-functions-tvfs)).

The following code example reads a CSV file from the location specified by `my_csv_location`, treating the first row as a header containing column names:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV(
    LOCATION => 'my_csv_location',
    HEADER => true
);
```

**Example: Using location object with pattern**

This example shows how to use the `PATTERN` parameter with a location object to filter specific files:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE LOCATION firebolt_sample_dataset WITH
  SOURCE = AMAZON_S3
  URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/';


SELECT $source_file_name, "Name", "LevelType" FROM READ_CSV(
    LOCATION =>  'firebolt_sample_dataset', PATTERN => 'levels*',
    HEADER => true
) limit 5;
```

**Returns**

| \$source\_file\_name                                      | Name                | LevelType       |
| :-------------------------------------------------------- | :------------------ | :-------------- |
| help\_center\_assets/firebolt\_sample\_dataset/levels.csv | Thunderbolt Circuit | FastestLap      |
| help\_center\_assets/firebolt\_sample\_dataset/levels.csv | Velocity Vale       | FirstToComplete |
| help\_center\_assets/firebolt\_sample\_dataset/levels.csv | Raceway Ridge       | FastestLap      |
| help\_center\_assets/firebolt\_sample\_dataset/levels.csv | Nitro Narrows       | FirstToComplete |
| help\_center\_assets/firebolt\_sample\_dataset/levels.csv | Thunder Road        | FirstToComplete |

This reads only the CSV files matching the pattern `levels*` within the location's base path, showing specific columns and the source file for verification.

### Using static credentials

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV(
    URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv',
    HEADER => true
);
```

The `url` can represent a single file or a [glob](https://en.wikipedia.org/wiki/Glob_\(programming\)) pattern. If a glob pattern is used, all files matching the pattern will be read. A special column `$source_file_name` can be used to identify the source file of each row in the result set:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT *, $source_file_name FROM READ_CSV(
    URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/*.csv',
    HEADER => TRUE
);
```

When using glob patterns, the wildcard (`*`) can only be used at the end of the path. You can use it with any text before or after, such as `*.csv`, `date=2025*.csv`, or `data_*.csv`.

The pattern will recursively match files in all subdirectories. For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV('s3://firebolt-publishing-public/*.csv')
```

will read all CSV files in the bucket, including those in subdirectories like `help_center_assets/firebolt_sample_dataset/*.csv`.

**Example**

In the following example, the `URL` is set as the first positional parameter and reads a CSV file:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV('s3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv');
```

**Returns**:

| f0      | f1     | f2    | f3                  | f4              | f5        | f6              | f7        | f8           | ... |
| :------ | :----- | :---- | :------------------ | :-------------- | :-------- | :-------------- | :-------- | :----------- | :-- |
| LevelID | GameID | Level | Name                | LevelType       | NextLevel | MinPointsToPass | MaxPoints | NumberOfLaps | ... |
| 1       | 1      | 1     | Thunderbolt Circuit | FastestLap      | 2         | 5               | 20        | 5            | ... |
| 2       | 1      | 2     | Velocity Vale       | FirstToComplete | 3         | 15              | 30        | 10           | ... |
| 3       | 1      | 3     | Raceway Ridge       | FastestLap      | 4         | 25              | 40        | 20           | ... |
| 4       | 1      | 4     | Nitro Narrows       | FirstToComplete | 5         | 60              | 100       | 10           | ... |
| 5       | 1      | 5     | Thunder Road        | FirstToComplete | 6         | 80              | 150       | 15           | ... |
| 6       | 1      | 6     | Burnout Boulevard   | Drift           | 7         | 50              | 80        | 8            | ... |
| 7       | 1      | 7     | Speed Street        | FastestLap      | 8         | 40              | 70        | 7            | ... |
| 8       | 1      | 8     | Racing Ravine       | FastestLap      | 9         | 60              | 100       | 20           | ... |
| 9       | 1      | 9     | Drift District      | Drift           | 10        | 100             | 250       | 25           | ... |
| 10      | 1      | 10    | Acceleration Alley  | FirstToComplete | null      | 200             | 500       | 50           | ... |

**Example**

The following example accepts `URL` as a named parameter and reads a CSV file with column names in the first row:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV(URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv', 
        HEADER => true);
```

**Returns**

| LevelID | GameID | Level               | Name            | LevelType | NextLevel | MinPointsToPass | MaxPoints | NumberOfLaps | ... |
| :------ | :----- | :------------------ | :-------------- | :-------- | :-------- | :-------------- | :-------- | :----------- | :-- |
| 1       | 1      | Thunderbolt Circuit | FastestLap      | 2         | 5         | 20              | 5         | 20           | ... |
| 2       | 1      | Velocity Vale       | FirstToComplete | 3         | 15        | 30              | 10        | 10           | ... |
| 3       | 1      | Raceway Ridge       | FastestLap      | 4         | 25        | 40              | 20        | 20           | ... |
| 4       | 1      | Nitro Narrows       | FirstToComplete | 5         | 60        | 100             | 10        | 10           | ... |
| 5       | 1      | Thunder Road        | FirstToComplete | 6         | 80        | 150             | 15        | 15           | ... |
| 6       | 1      | Burnout Boulevard   | Drift           | 7         | 50        | 80              | 8         | 8            | ... |
| 7       | 1      | Speed Street        | FastestLap      | 8         | 40        | 70              | 7         | 7            | ... |
| 8       | 1      | Racing Ravine       | FastestLap      | 9         | 60        | 100             | 20        | 20           | ... |
| 9       | 1      | Drift District      | Drift           | 10        | 100       | 250             | 25        | 25           | ... |
| 10      | 1      | Acceleration Alley  | FirstToComplete | null      | 200       | 500             | 50        | 50           | ... |

**Example**

The following example reads a CSV with headers and reads empty values as empty strings, rather than `NULL` values:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV(URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv',
        HEADER => true, EMPTY_FIELD_AS_NULL => false);
```

**Returns**:

| LevelID | GameID | Level               | Name            | LevelType | ... |
| :------ | :----- | :------------------ | :-------------- | :-------- | :-- |
| 1       | 1      | Thunderbolt Circuit | FastestLap      | 2         | ... |
| 2       | 1      | Velocity Vale       | FirstToComplete | 3         | ... |
| 3       | 1      | Raceway Ridge       | FastestLap      | 4         | ... |
| 4       | 1      | Nitro Narrows       | FirstToComplete | 5         | ... |
| 5       | 1      | Thunder Road        | FirstToComplete | 6         | ... |
| 6       | 1      | Burnout Boulevard   | Drift           | 7         | ... |
| 7       | 1      | Speed Street        | FastestLap      | 8         | ... |
| 8       | 1      | Racing Ravine       | FastestLap      | 9         | ... |
| 9       | 1      | Drift District      | Drift           | 10        | ... |
| 10      | 1      | Acceleration Alley  | FirstToComplete |           | ... |

**Example**

The following example accepts `URL` as a named parameter, reads a CSV file with column names in the first row, and infers types for all columns. In this example it allows filtering using numeric comparisons, since the `MaxPoints` and `MinPointsToPass` columns are properly typed as numbers rather than strings:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM READ_CSV(URL => 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv', 
        HEADER => true, INFER_SCHEMA => true) WHERE "MaxPoints" > 2 * "MinPointsToPass";
```

**Returns**

| LevelID | GameID | Level               | Name            | LevelType | NextLevel | MinPointsToPass | MaxPoints | NumberOfLaps | ... |
| :------ | :----- | :------------------ | :-------------- | :-------- | :-------- | :-------------- | :-------- | :----------- | :-- |
| 1       | 1      | Thunderbolt Circuit | FastestLap      | 2         | 5         | 20              | 5         | 20           | ... |
| 9       | 1      | Drift District      | Drift           | 10        | 100       | 250             | 25        | 25           | ... |
| 10      | 1      | Acceleration Alley  | FirstToComplete | null      | 200       | 500             | 50        | 50           | ... |