How to import CSV file to insert data into database table
Posted By : Tushar Paliwal | 20-Jun-2015
This tutorial shows you how can we load LOCAL INLINE FILE statement in mysql database which actually reads file and insert into database faster.
Writing the code to insert data into database may be time consuming, we can just use simple mysql script and import data.
Before importing data to database you need to prepare of following :
- A database table to which data needs to be imported.
- A CSV file which contains same no. Of columns as in table.
- Script which is used to import data using LOCAL INLINE FILE.
LOAD DATA LOCAL INFILE 'INPUT CSV FILE' INTO TABLE DATABASE.TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (COL1, COL2, COL3, COL4, @dummy)
In above script i used the following :
- INPUT CSV FILE : This is path of CSV file which exists into system.
- DATABASE : This is basically used as alias name of column for CSV which must be inserted into column of table in respective order.
- TABLE : This is used for name of table in which data need to be inserted.
- COL1...N : This is basically used as alias name of column for CSV which must be inserted into column of table in respective order.
- @dummy : This is used to ignore a particular index of column, suppose above example i'm trying ignore i.e. I don't wannna insert 5th column into table.
If you will try the above script using shell after login to mysql command prompt then may be you can face an error.
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql --local-infile -uUSERNAME -pPASSWORD DATABASE
I hope this will be meaningful for you, feel free to ask any query.
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Tushar Paliwal
Tushar is a developer with experience in Groovy and Grails , Spring and enterprise Java Technologies.