How to Load Delimited Data CSV Excel to MySQL

load-data-to-mysql

Loading CSV to MySQL or any delimited data files to MySQL database is a very common task frequently questioned about and almost every-time LOAD DATA INFILE come into rescue. I’ll guide you through various scenarios of loading data directly from CSV files into a MySQL database using a single command. Whether you’re a beginner or a seasoned developer, understanding these scenarios will make data import easy.

To make this easy to go through, I have prepared various scenarios that can be directly referred to simplify your task of importing CSV or text data to MySQL.

Load Data To MySQL 8 Scenarios

  1. Load Simple Comma-Separated File: Learn how to load a CSV file with column headers into a MySQL table. This is when your MySQL table matches the column sequence in the CSV file.
  2. Handling Different Column Sequence: Discover how to load data when the column sequence in the CSV file differs from that in the MySQL table. Specifying the column names in the CSV file ensures data lands in the right columns.
  3. Dealing with Lesser Columns: If your CSV file has fewer columns than the MySQL table, we’ll explore how to handle auto-increment columns, assign values using the SET command, and fill missing columns with values.
  4. Filling Date Columns: Find out how to automatically fill timestamp columns with the current timestamp value while loading data.
  5. Calculated Columns: Learn to load data with calculated columns, and even alter string variables using the SET command.
  6. Lookup Values from Another Table: Discover how to load data where one column’s value is a lookup from another table, using a sub-query.
  7. Alternate Methods: Explore different methods of loading separated files into MySQL, including the use of mysqlimport and other creative techniques.
  8. Loading Multiple Files: Understand how to use mysqlimport to load multiple files into MySQL, extending the capabilities of the LOAD DATA INFILE command.

Download PDF Tutorial: Load data CSV to MySQL

The Load Data Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,…)]
[SET col_name = expr,…]

Consider we have to load file with following contents:

File-name: example.csv
col-1,col-2,col-3
a,2,3
b,4,5
c,6,7

** Excel file can be easily exported as comma separated / delimited file (csv) by File-Save As option to load data.

1. How to load simple comma-saperated file with column header in MySQL

# Table structure: example
col-1 col-2 col-3

Considering our MySQL table having same column sequence we can issue:

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES ;

This is a very common and simple scenario.

Quick updates:

– Ofcourse, if we don’t have column headers (col-1,col-2,col-3) in example.csv, IGNORE 1 LINES is not required.

– Note the file path. Here you should make sure your slashes are proper.

You may give path as: C:\\path\\file.csv or C:/path/file.csv.

– If we have datafile to be loaded stored on client ( Not on server ), we will add LOCAL keyword as given in Syntax.

So, the command will become:

LOAD DATA LOCAL INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES ;

– If we want to replace existing data by data being loaded from file, we will add REPLACE keyword before INTO TABLE.

Similarly if we want input rows that duplicate an existing row on a unique key value to be skipped, we will use IGNORE keyword before INTO TABLE.

2. How to load data in MySQL when column sequence in file and table are different.

#table structure: example

col-2 col-1 col-3

In this case we need to specify column-name sequence of csv file in order to get data loaded in to proper columns.

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2,col-3);

3. csv / file have lesser number of columns than targeted table

#table structure: example

col-1 col-2 col-3 col-4

Consider, col-1 is auto-increment and not provided in csv.

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-2,col-3,col-4) set col-1=null;

Passing null value will make col-1 to take an auto-increment value.

Using SET you can assign values to those columns which were not available in csv and are not-null.

You may also use a function for doing some perticular task and set a value.

eg. SET col-x=rand();

4. Filling the extra date columns in MySQL while loading data

This is very similar to 3. Here, we are required col-4 to be filled with present timestamp value, a very simple way to do is altering table πŸ™‚

ALTER TABLE example CHANGE COLUMN col-4 col-4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

And then,

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2,col-2=3) set col-4=null;

It should automatically fill the current_timestamp values for us.

5. How to load data with calculated columns

#table: example

col-1 col-2 col-3 col-4
LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2,col-3, @var1) SET col-4 = @var1/100;

Similarly we can alter a string variable as well by altering the variable as follows:

SET col-4 = replace(@var1,"find","replace")

6.  How to load CSV in MySQL with table value lookup

Consider you’ve got a csv with col1 and col2 data and the 3rd column data is availble in another table. You can load the referenced data using a sub-query as follows. You have to make sure you get single row in return may be by using distinct or limit clause.

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2) SET col-3 = (SELECT field-name FROM linkedTable where keyfield=col-1);

7. Other ways of loading separated files to MySQL

CREATE TABLE t1 (
i INT NOT NULL INVISIBLE PRIMARY KEY,
j INT
) ENGINE = InnoDB;

ALTER TABLE t1 ADD COLUMN k INT INVISIBLE;

CREATE TABLE csv_foo LIKE foo;

ALTER TABLE csv_foo MODIFY COLUMN id INT(10) UNSIGNED NOT NULL;
// remove auto increment

ALTER TABLE csv_foo DROP PRIMARY KEY;
// drop key as no keys are supported in csv storage engine

Alternatively you may do:

CREATE TABLE csv_foo AS SELECT * FROM FOO LIMIT 0;
// Ignores key definitions and auto-increment
// Make sure you don't have any nullable columns.

Now,

STOP MYSQL SERVER

under data directory replace csv_foo.csv file by available data-file.csv. (Rename it to csv_foo.csv)

START MYSQL SERVER

We may need to do:

REPAIR TABLE csv_foo;

Well, this is not a “good” way though.

8. Loading multiple files to MySQL

Documentation says that MYSQL LOAD DATA will not be able to do it for us.

We have a separate option available for the same, mysqlimport.

You may want to refer: mysql-import

Conclusion: I hope we have covered common scenarios which shall mostly help; rest will always be answered here.

Go through user comments below, you may get some specific use cases for for typical load data or csv to mysql conversion questions and probable solutions too.

Also refer toΒ  the post where I’ve attempted to create a simple excel speadsheet to generate the Load Data Infile MySQL Syntax.

97 comments
  1. Hey Kedar,

    I refered to your link kedar.nitty-witty.com/blog/using-load-data-infile-with-stored-procedure-workaround-mysql.

    But the thing is I am using Windows XP. Can you tell me what should i do to make this work in Windows? I am a new bee to my sql.

  2. Help! I cannot get the SET command to work for my upload. I have tried just about everything that I can think of and found via web research. Not sure what I am missing. Everything works perfectly when the SET command is not present.

    load data local infile ‘/uploaded_files/aging_file.txt’ into table aging_pre_2007 fields terminated by ‘\t’ escaped by ” lines terminated by ‘\r\n’ (branch_number, customer_number, document_number, apply_to, order_number, document_date, due_date, order_type, line_amount, freight_tax) set aging_date = ‘2007-09-30’;

    Table Details:

    `aging_number` int(11) NOT NULL auto_increment,
    `aging_date` date default NULL,
    `branch_number` char(2) default NULL,
    `customer_number` varchar(6) default NULL,
    `document_number` varchar(6) default NULL,
    `apply_to` varchar(6) default NULL,
    `order_number` varchar(6) default NULL,
    `document_date` date default NULL,
    `due_date` date default NULL,
    `order_type` varchar(6) default NULL,
    `line_amount` double(15,2) default NULL,
    `freight_tax` double(15,2) default NULL,
    PRIMARY KEY (`aging_number`),
    UNIQUE KEY `aging_number` (`aging_number`)

    Tab delimited text file includes branch_number, customer_number, document_number, apply_to, order_number, document_date, due_date, order_type, line_amount and freight_tax. I need to add the aging date because it cannot be added to the text file, but I know what it is.

    Thanks.

    1. James,

      Your syntax appears proper to me and works perfectly when I tried! I hope you’ve your file at proper place with right permission for mysql to get hold of it.

      In the mean time if you’re in too hurry to find & shoot the problem with LOAD DATA, I can suggest you a quick work-around:
      1.
      – alter your table -> `aging_date` date default ‘2007-09-30’
      – run you syntax above without passing aging_date, it will be populated as default.

      2. if you can’t alter, just fire an update query.

  3. Hi Kedar,

    Nice work! I’m having a doubt regarding values from the file columns that have characters like ‘,’ (comma). how to handle these kind of columns while uploading?
    E.g. If there is file Example.csv which has col1: item1 col2:”Some description like a,b,c”. We are uploading based on ‘,’ delimiter. So, how to ignore ‘,’ from the values from a given column?

    Thanks,
    Vaibhav.

  4. I have stumbled upon an unexpected problem…

    Is there a way of handling input data with missing values?

    for example, what if a line had a missing value which should ideally be signified by a zero? Is there a way of making the 1st input below read: a 2 0

    col-1,col-2,col-3

    a,2,

    Likewise, what about a zero value in the middle of the input line, like this,

    col-1,col-2,col-3

    a,,3

    Thanks if you’re able to offer any ideas on this!!

    1. First case you can do:

      LOAD DATA INFILE ‘c:/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ set col3=if(col3=”,0,col3);

      and same for the next.

      Hope this works… I’m in bit hurry πŸ˜›

  5. hi,
    i want to know how to save fuzzy number to my sql.
    example :
    Equally important = (1,1,3)
    how to save three value into one field, equally important.

  6. Is there any way to select particular form of data from csv file using regular expression in “load data infile” query and using SET we assign that value into column in which we want that data ???

  7. http://www.agilefuseconsulting.co.cc/csvtomysql/

    This is the CSV to MySQL tool. The tool was developed through the use of html5 and file api. The tool works best on firefox and google chrome browser(latest version). Be that as it may,pls. be advised that importation of large data would require a substantial amount of time to complete, and may also cause memory limit error so use the tool with caution.

  8. You keep using words like ‘simple’ and ‘easy’, all the while providing the most convoluted explanation possible…

    1. Hey Chris,
      Things will be Simple & Easy once we do it… those words you may consider as placeholders to reduce the complexity πŸ˜‰
      Importantly, let me know how can I help you, if anything is unclear!
      Cheers.

  9. Since load data infile is prohibited in a stored procedure, what would be the workaround to import a csv file into MySQL d/b?

Leave a Reply to zeera Cancel reply

Your email address will not be published. Required fields are marked *