# Load Data from Local Files

To load data from local files, enable `LOCAL INFILE` by starting the SingleStore client with the `--local-infile` option. This option allows the client to read files from the local filesystem and send them to the database for ingestion using `LOAD DATA LOCAL INFILE`.

The basic format for loading data into a table using local file is:

```
LOAD DATA LOCAL INFILE '/example-directory/<file_name>'
    INTO TABLE <table_name>
    COLUMNS TERMINATED BY ',';
```

## Example Using a Local CSV File

Using this table format:

```sql
CREATE TABLE employees(emp_id INT,
  emp_lastname VARCHAR(25),emp_firstname VARCHAR(25),
  emp_title VARCHAR(25),dept VARCHAR(25),emp_city VARCHAR(25),
  emp_state VARCHAR(5),emp_ccode VARCHAR(4));
```

Load data from a local file location:

```sql
LOAD DATA LOCAL INFILE '/tmp/emp_data.csv' 
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"';
```

Verify the file loaded correctly:

```sql
SELECT * FROM employees;


```

```output

+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
| emp_id | emp_lastname | emp_firstname | emp_title     | dept      | emp_city     | emp_state | emp_ccode |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
|    102 | Karras       | Damien        | Doctor        | R&D       | NYC          | NY        | US        |
|    110 | Strode       | Laurie        | VP Sales      | Sales     | Haddonfield  | IL        | US        |
|     89 | Whateley     | Wilbur        | CEO           | Sen_Mgmt  | Dunwich      | MA        | US        |
|    817 | Eriksson     | Oskar         | Support       | IT        | Stockholm    | NULL      | SE        |
|    298 | Denbrough    | Bill          | Salesperson   | Sales     | Bangor       | ME        | US        |
|    399 | Torrance     | Jack          | PR Dir        | PR        | Estes Park   | CO        | US        |
|    410 | Wilkes       | Annie         | HR Mgr        | HR        | Silver Creek | CO        | US        |
|    312 | Cady         | Max           | IT Dir        | IT        | New Essex    | FL        | US        |
|    691 | Grevers      | Nick          | Rep           | PR        | Grimnetz     | NULL      | CH        |
|     14 | Bateman      | Patrick       | Prod_Mgr      | prod_dev  | NYC          | NY        | US        |
|     75 | White        | Carrie        | Receptionist  | HR        | Chamberlain  | ME        | US        |
|    263 | MacNeil      | Regan         | R&D Mgr       | R&D       | Washington   | DC        | US        |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+

```

## Stream Data with LOAD DATA LOCAL INFILE

Instead of loading data from a file on the local filesystem, applications can stream data directly from memory into `LOAD DATA LOCAL INFILE`. This is useful when:

* Data is generated programmatically (for example, by an ETL script) and would otherwise need to be written to a temporary file first.
* Data is produced incrementally from another source (for example, another database, a queue, or a stream processor) and must be loaded without buffering the full dataset on disk.
* The application already has the rows in memory and does not need file-based persistence.

Compared with multi-row `INSERT`, streaming can reduce client and network overhead by avoiding the construction and transmission of a large SQL statement. It can also use compression to reduce the amount of data sent over the network. For large datasets, `LOAD DATA` can load the data with a single statement, whereas `INSERT` statements may need to be split into multiple statements because the SQL query size is limited by `max_allowed_packet`.

Streaming uses the same MySQL protocol mechanism as file-based `LOAD DATA LOCAL INFILE`, SingleStore requests the file contents from the client, and the client responds with in-memory bytes. All standard `LOAD DATA` clauses (`FIELDS TERMINATED BY`, `ENCLOSED BY`, `SET`, `SKIP DUPLICATE KEY ERRORS`, `FORMAT AVRO`, `FORMAT JSON`, `FORMAT PARQUET`, etc.) are supported.

The application must use a SingleStore client library that supports stream-based `LOAD DATA LOCAL INFILE`. Supported clients include:

* [SingleStore Python client](https://docs.singlestore.com/db/v9.0/developer-resources/connect-with-application-development-tools/connect-with-python.md) (`singlestoredb`)
* [SingleStore JDBC driver](https://docs.singlestore.com/db/v9.0/developer-resources/connect-with-application-development-tools/connect-with-java-jdbc/the-singlestore-jdbc-driver.md)
* [SingleStore ADBC connector](https://github.com/singlestore-labs/singlestore-adbc-connector)
* [SingleStore .NET connector](https://github.com/memsql/SingleStoreNETConnector)
* [SingleStore Go driver](https://github.com/singlestore-labs/go-singlestore-driver)

Each supported client provides its own mechanism for attaching an in-memory stream to `LOAD DATA LOCAL INFILE`.

Supported clients and connectors expose stream-based `LOAD DATA LOCAL INFILE` through their own APIs:

* SingleStore Python client (`singlestoredb`): Use `:stream:` as the `INFILE` value and pass the in-memory iterator or stream through the `infile_stream` argument.
* SingleStore JDBC driver: When an input stream is registered with `Statement.setNextLocalInfileInputStream(InputStream`), the filename is ignored; no specific filename format is required.
* SingleStore .NET connector: Use `SingleStoreBulkLoader` to load the stream. A filename is not required.
* SingleStore Go driver: Use `Reader::<name>`, where `<name>` is the exact string passed to `RegisterReaderHandler`.

Refer to the documentation for the specific client or connector for the API used to attach the in-memory stream.

For example, the following example uses the SingleStore Python client to stream CSV data generated dynamically into a table:

```python
import singlestoredb as s2

conn = s2.connect('user:password@host:3306/my_db?local_infile=true')
cur = conn.cursor()

cur.execute('CREATE TABLE generator (a INT, b INT, c INT)')

def upload_csv():
    yield '1,2,3\n'
    yield '4,5,6\n'
    yield '7,8,9\n'
    # Chunks do not need to align to row boundaries.
    yield '10,11,12\n13,14,15\n'

cur.execute(
    """
    LOAD DATA LOCAL INFILE ':stream:' INTO TABLE generator
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    """,
    infile_stream=upload_csv(),
)
```

The generator streams chunks to the server. Each chunk contains raw CSV bytes. Row boundaries are determined by the line terminator (`\n` in this example), not by chunk boundaries.

For patterns where the data producer runs in a different thread from the SQL client, use a queue. For example:

```python
import singlestoredb as s2
from queue import Queue
from threading import Thread

conn = s2.connect('user:password@host:3306/my_db?local_infile=true')
cur = conn.cursor()

cur.execute('CREATE TABLE queue_load (a INT, b INT, c INT)')

data_feeder = Queue()

t = Thread(
    target=cur.execute,
    args=(
        """
        LOAD DATA LOCAL INFILE ':stream:' INTO TABLE queue_load
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        """,
    ),
    kwargs=dict(infile_stream=data_feeder),
)
t.start()

data_feeder.put('1,2,3\n')
data_feeder.put('4,5,6\n')
data_feeder.put('7,8,9\n')
data_feeder.put('10,11,12\n13,14,15\n')

# An empty string ends the stream.
data_feeder.put('')

t.join()
```

Streamed data is subject to the same parsing, error handling, and load data error logging as file-based loads. Error rows can be captured with `ERRORS HANDLE '<handle>'` and inspected in `information_schema.LOAD_DATA_ERRORS`.

Client-side connection settings that disable `LOAD DATA LOCAL INFILE` (for example, `local_infile=0`) reject stream-based loads with the same client-side error as file-based loads.

## CSV FILE DATA

Below is the contents of the csv file used in the examples above.

```csv
014, Bateman, Patrick,Prod_Mgr, prod_dev, NYC, NY, US
102, Karras, Damien, Doctor,R&D, NYC, NY, US
298, Denbrough, Bill, Salesperson,Sales, Bangor, ME, US
399, Torrance, Jack, PR Dir, PR,Estes Park,CO, US
410, Wilkes, Annie, HR Mgr,HR,Silver Creek, CO, US
110, Strode, Laurie, VP Sales,Sales, Haddonfield, IL, US
312, Cady, Max, IT Dir, IT, New Essex,FL, US
089, Whateley, Wilbur, CEO, Sen_Mgmt, Dunwich, MA, US
075, White, Carrie, Receptionist, HR,Chamberlain, ME, US
263, MacNeil, Regan, R&D Mgr,R&D, Washington, DC, US
817, Eriksson, Oskar, Support, IT, Stockholm, NULL, SE
691, Grevers, Nick, Rep, PR, Grimnetz, NULL, CH

```

***

Modified at: October 8, 2026

Source: [/db/v9.0/load-data/load-data-from-files/load-data-from-local-files/](https://docs.singlestore.com/db/v9.0/load-data/load-data-from-files/load-data-from-local-files/)

(An index of the documentation is available at /llms.txt)