CODEWITHSUNDEEP
javamysqlsql
amit
amit

Reputation: 45

CSV to MySQl Import

i need to insert csv into mysql database in proper column.

let say csv has header and then data A B C

and Mysql has table with column C A B

i need to know best way to insert csv data to mysql table

Upvotes: 1

Views: 1800

Answers (3)

Ifedi Okonkwo
Ifedi Okonkwo

Reputation: 3646

To load CSV data into MySQL database in the correct order, regardless of column order in the file, do this:

LOAD DATA INFILE path/to/datafile.csv INTO TABLE tablename (colA, colB, colC)

Ordinarily, the column list (colA, colB, colC) is optional. However, when (as in this question) the order of columns is different between the CSV file and the DB table, then arrange the column names in the order you want them inserted into the table, like so:

LOAD DATA INFILE path/to/datafile.csv INTO TABLE tablename (colC, colB, colA)

To see other options for loading CSV data into mysql, check out http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 1

Husman
Husman

Reputation: 6909

I believe you can use the following syntax for mysql:

"INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('test', 'test', 'test', 'test', 'test', 'test')"

So you can build up your query, using the header and the column it falls into, like so (pseudocode):

"Insert into table (header1, header2, header3) values (column1, column2, column3)"

Regardless of what order the data is in the table, that will insert data into the correct column.

Upvotes: 1

Ninad Pingale
Ninad Pingale

Reputation: 7069

Use tools like OpenCsv.Jar

Example are given here - http://opencsv.sourceforge.net/

It handles large volume of data.

Upvotes: 3

Related Questions