Subscribe
Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost everytime LOAD DATA INFILE come into rescue.
Here we will try to understand some of the very common scenarios for loading data into MySQL Database.
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. A simple comma-saperated file with column header:
#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. 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 / load data file have lesser number of columns than targetted 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:
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. Loading data with caculated 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. Other ways of loading separated files to MySQL:
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.
7. Loading multiple files:
Documentation says that MYSQL LOAD DATA will not be able to do it for us.
We have a separate option available for the same.
Refer: mysqlimport
Conclusion: I hope we have covered common scenarios which shall mostly help; rest will always be answered here.
Finally, If you want to load data to MySQL Server, LOAD DATA.
Related posts:
- MySQL Load Data Infile Syntax Generator Tool Download The LOAD DATA INFILE statement reads rows from a...
- MySQL Stored procedure – Split Delimited string into Rows This procedure will split a “;” separated column in...
- Quick Multi MySQL Server Installation with Master-Master Replication on Same Windows Box This article is a brief step-by-step tutorial on the...
Just tried it on my site. Works flawlessly. Thanks a bunch!
Hi paul,
Good to hear your successful try.
You can of-course go through other topics if you already haven’t.
Great article!
There is a third party utility to do the same.
http://www.sqldbu.com/eng/sections/tips/normalize.html
how can i convert date from excel csv to date in mysql.
i have to put column in mysql as VARCHAR so then it can read from csv. but if i change to DATE it read as 00-00-0000.
any idea?
Hi mcheali!
I’m not sure in what format you have your date stored in csv.
If you have csv as:
#x.csv
1,2010-05-21
2,2010-05-22
3,2010-05-20
#table x:
CREATE TABLE `x` (
`a` int(10) NOT NULL,
`b` date default NULL
) ENGINE=MyISAM;
then a very simple load data query will fill the date values correctly.
#Query:
LOAD DATA INFILE “c:/x.txt” INTO TABLE x
FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;
…
Yes, thank for the information a million! Should discover anyone organizing Tea Party. I wants to participate in organizing….
….greetings….
i have bunch of csv file where some of the files has column name order differ from each other and also differ from the table in database..
how can i load the data into the correct field?
the table have about 50 columns and each csv file have about 20-30 columns only..
any suggestion?
Greetings zeera,
Well I believe if you have column order different from each other and table as well, it’s case #3 describled above. You have to find out proper field positions and then changing the order in load data command should work. It’s like you must specify a column list if the order of the fields in the input file differs from the order of the columns in the table.
Eg.
LOAD DATA INFILE ‘file.csv’ INTO TABLE tablename (col1,col3,col5,col2…);
About 50 vs 20/30 Columns, if you have nullable columns, things will be fine.
thanks Kedar… i know it will work..
but how about the csv file is uploaded by the user?
how am i supposely know the table order?
and i won’t know what column is nullable for the file..
- i’m using php with mysql -
any idea to solve my solution? thanks in advance
sorry for the wrong word..
-what if the file is uploded by the user? how to know the column order?
Zeera,
- You should ask a formatted input, may be you can publish a template and reject user uploads which are not as per template.
- You can use ‘DESC tablename’, will show you column names in order. It will also show you data type information and whether the column is ‘allow-null’ or not.
- About parsing user inputs I believe instead of using “load data” you might need some data parsing script when your inputes are not consistent.
Hope this helps.
thanks…. but i’m developing new system since here they only use excel to store evrythng..
anyway thanks for the idea..
–> just wanna ask if u know how to autogenerate the excel file either to text or csv with the font color as one column after the next? mmm.. example is like below:
i want output in csv like this: (is it possible)
Zeera, blue, 22, blue, London, black
its mean Zeera and 22 are in blue color while London in black color..
i need to make comparison afterwords..
Hi,
I’m not much into dev but I can sense it’s possible. There are PHP classes to generate Excel-worksheets available. “Google”ing right keywords will give you the solution.
Thanks Kedar..
I managed to make it using perl.. just not yet captured the font color..
morning Kedar..
about the your earlier suggestion..
LOAD DATA INFILE ‘file.csv’ INTO TABLE tablename (col1,col3,col5,col2…);
i do it but it still not in order.. it does not follow the (col1,col3,col5,…..).. it just insert from col1,col2 until the end…
any suggestion?
How is your csv?
See below example:
Table:
——
CREATE TABLE `mytable` (
`a` int(11) default NULL,
`b` varchar(5) default NULL,
`c` varchar(5) default NULL,
`d` varchar(5) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CSV:
—-
1,b,d,c
1,bb,dd,cc
1,bbb,ddd,ccc
Syntax:
——-
Load data infile ‘d:/test.csv’ into table mytable fields terminated by ‘,’ optionally enclosed by ‘”‘ lines terminated by ‘\r\n’ (a,b,d,c);
This loads data in proper order.
ok.. lets say i use ur example.. only the syntax i choose some column..
load data infile into table mytable fields terminated by ‘,’ (a,c,d);
–> it insert into a,b,c column instead
how can i insert into another table when compare data from 2 table?
example:
——-
table a table b
——- ——-
col1 | col2 | col3 | col4 col1 | col2 | col3 | col4
————————- ————————-
a | b | c | d a | b | e | d
w | x | y | z w | x | m | n
the compare table – the result after compare two table
——————————————————
col1 | col2 | col3 | col4
————————-
True | True | False| True
True | True | False| False
–can anyone help me here? (T_T)
how can i insert into another table when compare data from 2 table?
example:
——-
table a
——-
col1 | col2 | col3 | col4
————————-
a | b | e | d
w | x | y | z
table b
——-
col1 | col2 | col3 | col4
————————-
a | b | c | d
w | x | m | n
the compare table – the result after compare two table
——————————————————
col1 | col2 | col3 | col4
————————-
True | True | False| True
True | True | False| False
–can anyone help me here? (T_T)
Yes… I try to help whenever possible and available.
1. About your comment on load data query… below is the output:
mysql> load data infile ‘d:/test.csv’ into table my2 fields terminated by ‘,’ (a
,c,d);
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 3
mysql> select * from my2;
+——+——+——+——+
| a | b | c | d |
+——+——+——+——+
| 1 | NULL | b | d |
| 1 | NULL | bb | dd |
| 1 | NULL | bbb | ddd |
+——+——+——+——+
3 rows in set (0.00 sec)
I’m not sure what you’re upto and what are you doing.
2. About ur true/false output you can join both tables:
select if(t1.col1=t2.col1,’true’,'false’) as Col1 from table1 t1,table2 t2 where t1.SMCOL=t2.SMCOL;
You may further mail me your exact requirements.
this is my query:
select
if(t1.StepName=t2.StepName,’true’,'false’) as StepName
from ProcessSpec t1,VerifiedData t2
where t1.StepName=t2.StepName;
it give all the output as true. if i write ‘false’,'true’
it will give all the output as false..
Please read how IF works on dev.mysql documentation.
Please understand what you’ve done in your query.
As you’re joining on StepName, you will get data where StepName from t1 and t2 will be same. So, IF condition is always going to fall in true part. So, when you write “false,true” it will show false and the reverse.
…
How’s it heading? I liked reading through this submit. My husband and I’ve been browsing for this kind of posting with the longest time and We realize that your details about the problem at hand is spot on. I’ll be sure to introduce this write-up to …
about my T/F table.. is it a problem as i don’t have a key for each tables? i’m not sure what id can i use since table a and b can be uploaded at different time
…
wonderful guidance and sharing,I’ll get this fantastic for me .many thanks!…