Load Data from Local Files
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
The basic format for loading data into a table using is 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:
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:
LOAD DATA INFILE '/tmp/emp_data.csv'INTO TABLE employeesFIELDS TERMINATED BY ','ENCLOSED BY '"';
Verify the file loaded correctly:
SELECT * FROM employees;
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
| 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 |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
CSV FILE DATA
Below is the contents of the csv file used in the examples above.
014, Bateman, Patrick,Prod_Mgr, prod_dev, NYC, NY, US102, Karras, Damien, Doctor,R&D, NYC, NY, US298, Denbrough, Bill, Salesperson,Sales, Bangor, ME, US399, Torrance, Jack, PR Dir, PR,Estes Park,CO, US410, Wilkes, Annie, HR Mgr,HR,Silver Creek, CO, US110, Strode, Laurie, VP Sales,Sales, Haddonfield, IL, US312, Cady, Max, IT Dir, IT, New Essex,FL, US089, Whateley, Wilbur, CEO, Sen_Mgmt, Dunwich, MA, US075, White, Carrie, Receptionist, HR,Chamberlain, ME, US263, MacNeil, Regan, R&D Mgr,R&D, Washington, DC, US817, Eriksson, Oskar, Support, IT, Stockholm, NULL, SE691, Grevers, Nick, Rep, PR, Grimnetz, NULL, CH
Last modified: September 26, 2023