How to Load Delimited Data CSV Excel 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

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.
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.
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.
Filling Date Columns: Find out how to automatically fill timestamp columns with the current timestamp value while loading data.
Calculated Columns: Learn to load data with calculated columns, and even alter string variables using the SET command.
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.
Alternate Methods: Explore different methods of loading separated files into MySQL, including the use of mysqlimport and other creative techniques.
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.

Kedar

Related Tags

97 comments

Yogesh says:

January 28, 2013 at 6:04 am

Thanks Sir for your quick reply.
Actually I took some different approach I need not ignore column now.
But i got some other issue.
Thing is that, records in my CSV file have some blank spaces.. I know i can use trim function in php and remove the white spaces at the beginning and end of a string… but now since my data is getting inserted in database table using load data infile…how to remove that blank space across records while insertion…. because load data infile will insert every record as it is without trimming anything…

Thanks.

Reply
1. Kedar says:
  
  January 28, 2013 at 8:05 am
  
  Sure Yogesh,
  
  Check case #5.
  You may find and replace spaces for specific columns – use the replace function.
  
  Cheers.
  
  Reply
Yogesh says:

January 25, 2013 at 12:21 pm

Kedar Sir,
Skipping rows i know…
Any idea how to skip columns….

Thanx

Reply
1. Kedar says:
  
  January 26, 2013 at 5:11 pm
  
  Hi Yogesh,
  
  May be you can detect pattern in the csv and make the LOAD DATA to generate err and thus that line will be ignored!!
  Well, I’m not much sure about what you’re trying so if you can provide a sample, we may find some way out…
  🙂
  
  Reply
giovanni says:

December 30, 2012 at 6:56 pm

Dear KEDAR,
I bag your help…
I’m not a programmer and I need to edit and write again
a csv file in PHP.
I need to remove the ‘,’ from the end of each line.
If you copy and save as CSV the following rows that’s the csv file.

I’ve tried to use substr ($xxxx[$c],0,-1);
But I don’t know how write back the modified file.

“Cod. Meccanografico”,”Tessera”,”Nuo./Rin.”,”Cognome”,”Nome”,”Sesso”,”Data Nascita”,”Luogo Nascita”,”Indirizzo”,”Cap”,”Città”,”Provincia”,”Data Rilascio”,”Cod. MC”,”Motoclub”,”Telefono”,”Cellulare”,”Email”,”Cittadinanza”,”Residenza”,”Dirigente”,
” 10102685″,”12110285″,”R”,”ABATI”,”GABRIELE”,”M”,”27081974″,”BERGAMO”,”VIA DELLA GRUMELLINA 41″,”24127″,”BERGAMO”,”BG”,”10012012″,”0179″,”BERGAMO “,,”3402865027″,,”I”,”S”,,
” 12910500″,”12910500″,”N”,”ACERBIS”,”DAVIDE”,”M”,”11042000″,”ALZANO LOMBARDO”,”VIA PICCINELLI, 8″,”24020″,”SCANZOROSCIATE”,”BG”,”23022012″,”0179″,”BERGAMO “,,”3381082603″,,”I”,”S”,,
” 03515015″,”12110160″,”R”,”ACERBIS”,”FRANCO”,”M”,”12091946″,”ALBINO”,”VIA PEZZOTTA 3″,”24021″,”ALBINO”,”BG”,”06122011″,”0179″,”BERGAMO “,”035773577″,,”valeria.a@acerbis.it”,”I”,”S”,”CS”,
…

Many thanks
Giovanni

Reply
1. Kedar says:
  
  January 2, 2013 at 5:10 am
  
  Giovanni,
  
  well I’m no php expert too but google told me to:
  
  // read the file
  $file = file_get_contents(‘filename.csv’);
  // replace the data
  $file = str_replace(‘folder|oldValue’, ‘folder|newValue’, $file);
  // write the file
  file_put_contents(‘filename.csv’, $file);
  
  Instead of str_replace you may also use preg_replace to find your pattern.
  
  Let me know if this doesn’t help.
  Thanks.
  
  Reply
Andrea says:

November 30, 2012 at 9:14 pm

Just one sentence: you’ve made my day!

Thank you!

Reply
1. Kedar says:
  
  December 2, 2012 at 5:57 pm
  
  That’s what keep this going 🙂
  
  Happy “MySQL”ing…
  
  Reply
2. Kedar says:
  
  December 2, 2012 at 5:57 pm
  
  That’s what keep this going 🙂
  
  Happy “MySQL”ing… Andrea.
  
  Reply
sohaib says:

July 6, 2012 at 5:43 am

Hi,
You were a great help thanks alot, I found some things here which I couldnt find any where..
well
here is a query please i need to fix this ASAP

LOAD DATA INFILE ‘C://graphs data//chk.txt’ INTO TABLE movingaverages_ten FIELDS TERMINATED BY ‘,’LINES

TERMINATED BY ‘\n’
(Id,days,DateTime,stockId) SET ma = (SELECT SUM( Price ) FROM (Select Price from closingprice_ten WHERE

closingprice_ten.StockId = stockId and ‘DateTime’ > ‘closingprice_ten.DateTime’ ORDER BY

closingprice_ten.DateTime DESC limit days) ptscore);

so the error is like :
ERROR 1327 (42000): Undeclared variable: days

Can you help me with this! please

Reply
1. Kedar says:
  
  July 6, 2012 at 10:37 am
  
  Thanks sohaib,
  
  I’m not sure what are you trying to do
  “… DESC limit days” << HERE Is that by mistake or you need that value generated dynamicall? Hope I'm not too late in replying... Regards, Kedar.
  
  Reply
  1. sohaib says:
    
    July 6, 2012 at 10:55 am
    
    no its never too late,
    
    Limit days ,
    its like limit 20 or 50 or blabla..
    yea its dynamic 🙂
    
    See I guess now I have got the issue, but still with no clue.
    
    (Id,days,DT,stockId) SET ma = (SELECT SUM( Price ) FROM (Select Price from closingprice_ten WHERE
    DT > closingprice_ten.DateTime
    Look here, I am now getting this error.
    ERROR 1054 (42S22): Unknown column ‘DT’ in ‘where clause’
    
    Thanks alot for the reply.
    
    Reply
    1. Kedar says:
      
      July 9, 2012 at 11:23 am
      
      Sohaib,
      
      about your first query:
      <> You must find a way to pass a value to variable days. Eg. if days is something you want to use from the file you’re loading => days value from the table => you can use
      <<... order by movingaverages_ten.days>>
      
      ———————–
      
      consider example
      Table ld with columns (id, val, calc)
      File to load: “1,val”
      Generate value of calc from another table named “b” using value we passed in text.
      
      We can do this as follows:
      
      load data infile ‘/tmp/a.txt’ into table ld fields terminated by ‘,’ lines terminated by ‘\n’ (id, val) set calc=(select id from b where b.id=ld.id);
      
      I’m not sure what are you trying to do!!
      
      Understand that “(Id,days,DT,stockId)” these will be seen as columns by MySQL and hence you’ve got the error unknown column error.
      
      If you’re not using MySQL 5.5 it’s not possible for you to use variable-limit clause.
      Also you probably don’t need a variable-limit as you’re using aggregate function.
      
      Try with this query:
      Select sum(Price) from closingprice_ten WHERE
      closingprice_ten.StockId = stockId and ‘DateTime’ > ‘closingprice_ten.DateTime’ ORDER BY closingprice_ten.DateTime DESC
      
      If this doesn’t work, mail me your problem in detail at kedar@nitty-witty.com.
      
      – Kedar.
      
      Reply
sampath says:

February 19, 2012 at 7:42 am

hai kedar….

i had csv file where date is in dd:mm:yyyy format and time in hh:mm format can u plz suggest me a LOAD command to load the csv file to mysql table…..

thanks in advance…..

Reply
1. Kedar says:
  
  May 16, 2012 at 3:21 pm
  
  Sampath,
  
  Sorry for the delayed responce due to some “tech” issues on d’ blog I couldn’t answer you on timely basis.
  Btw surely you can use strtotime function to convert it into date-time value.
  
  Cheers,
  Kedar.
  
  Reply
Kebman says:

January 16, 2012 at 7:31 pm

How do you exclude lines with duplicate data from the import?

Reply
1. Kedar says:
  
  January 17, 2012 at 5:12 am
  
  Kebman,
  I’d use replace / ignore while loading data!
  
  load data infile ‘FILEPATH/FILENAME’ IGNORE into tablename…
  
  Make sure you have key column defined for the stuff to work.
  
  Reply
aijaz says:

January 6, 2012 at 2:08 pm

Hi Kedar,
I had a csv file containing 4 columns,
and my table has 3 columns.
is it possible to insert only 3 columns by ignoring any column from .csv file (say ignoring 4th column)?

Reply
1. Kedar says:
  
  January 6, 2012 at 3:18 pm
  
  Hey aijaz,
  
  I assume if you use simple load command, it will insert the data ignoring the extra column. You should get warning! This is the case when you want to ignore last (4th) column.
  
  Now there is very tricky but easy solution for inserting last 3 values and ignoring 1st column 🙂
  What you’ll do here is specify columns:
  load data infile ........ fields terminated by 'STRING' ... (col1,col1,col2,col3);
  I'll update this in main post too!
  Cheers, Kedar.
  
  Reply
Punitha says:

November 25, 2011 at 10:03 am

Hi Kedar,

Thanks it’s helps me lot.

Reply
punitha says:

November 22, 2011 at 8:10 am

Hi Kedar,

First great appreciation for your nice work.

I am having CSV file which holding column like

col-1:”TC1″,col-2:”TC2″,col-3:”id1,id2″col-4:”TC4″

Note that col-3 has 2 values, i have to add each rows into table and repeat the other column values for col-3 each value

Isn’t any possible to do this?.

Advance Thanks.

Reply
1. Kedar says:
  
  November 22, 2011 at 9:50 am
  
  Hi Punitha,
  
  I’m in a bit hurry! So I’d like you to try this kind of “workaround”:
  
  1. Load your csv as it is.
  2. Try Splitting the row later as shown in this article: http://kedar.nitty-witty.com/mysql-stored-procedure-split-delimited-string-into-rows
  
  Thanks,
  Kedar.
  
  Reply