How to import data from CSV file into MySQL database

Question

I have to import data from a CSV file into MySQL database using JSP/Servlet. While searching I got this link! where it is said that we can use MySQL command "LOAD DATA INFILE ..." in hibernates createSQLQuery.

But in my case the table in which I need to upload the data is distributed on multiple database server. I would be getting a API through which I would get the connection to the database.

My query is, where should I save the CSV file -- in my tomcat server or on the system where I would be executing the "LOAD DATA INFILE ..." command.

To mark a question as answered, just set an accepted answer, do not put "Solved" in title like as you would do in an old fashioned discussion forum. This makes no sense. Stack Overflow is a Question & Answer site already. Questions with accepted answers already appear differently in the listing and are searchable by accepted:1. — BalusC
– BalusC, Commented Dec 2, 2011 at 14:15

Sergii Kudriavtsev · Accepted Answer · 2011-12-01 07:45:19Z

1

CSV file should be saved on server running MySQL DB. Also access privileges should be set that the user which MySQL Server is run under is allowed to access this file.

You might also find article on LOAD DATA INFILE from MySQL docs useful.

answered Dec 1, 2011 at 7:45

Sergii Kudriavtsev

10.5k5 gold badges48 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Natraj Over a year ago

Thanks for the quick reply. But I would be getting only the connection to that database. I would not have access to save the file on that system. Is there any way to directly load the CSV data into MySQL database?

Sergii Kudriavtsev Over a year ago

Unfortunately, no (at least, not that I'm aware of). You'll have to either parse CSV on client or write a stored procedure that will do parsing of incoming text on DB level. I'd suggest parsing on client, MySQL server seems to be not very fast and/or efficient in string manipulations. If anyone knows the other way, I'd be glad to learn it.

Sergii Kudriavtsev Over a year ago

UPDATE: MySQL docs say it's possible to specify LOCAL keyword after LOAD DATA - it will force client to read file from client filesystem, pass it to server (it will be saved in server /tmp or other temporary directory) and read by using usual LOAD DATA statement. So you should do LOAD DATA LOCAL INFILE 'file_name' INTO ...

Natraj Over a year ago

Thanks for the reply. I will check it an let you know the result.

Natraj Over a year ago

Thanks '@Sergey' your suggestion of using the 'LOCAL' keyword in 'LOAD DATA' works charmingly.

Collectives™ on Stack Overflow

How to import data from CSV file into MySQL database

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related