CODEWITHSUNDEEP
mysql
contributor
contributor

Reputation: 153

How to import some columns and its data from csv file into mysql phpmyadmin

I have a big myfile.csv file that looks like this

enter image description here

From this file I want to import some columns and obivously rows and its relevant data into mysql database phpmyadmin.

This file is located on my local computer, I want to import some columns,rows with data from that file to my live database.

Here is what I have tried after searching google.

I created a table with following columns

id name email

Then tried to run the following query in my live database

LOAD DATA LOCAL INFILE '/tmp/myfile.csv' INTO TABLE registration_no
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
(@col1,@col2,@col3) set id=@col1,name=@col2,email =@col3

Note

myfile.csv is located on my computer, in C: drive.

Am I running the correct query,is the path /tmp/myfile.csv is correct ?

Query runs but the data isn't loaded into my live DB, please help me, I've spent one and half day figuring this out .

I have read this.

Upvotes: 3

Views: 2646

Answers (1)

James
James

Reputation: 1829

Step 1: (preferred) Try to have only the columns(csv file) which to be imported into DB.

ex: If 3 columns to be imported then in your myfile.csv remove other unnecessary columns

Step 2: Since you are using a windows system make sure the path is specified properly when loading the file.

Step 3: If your csv has headers to skip it use IGNORE 1 LINES.

So, the query would be like below.

LOAD DATA LOCAL INFILE 'C:/myfile.csv' INTO TABLE registration_no
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, name, email);

Step 4: (optional) If you need to import only specific columns from csv.

ex: csv contains 5 columns like id, name, reg_no, dob, email, but need to import only 3 columns id, name, email. Just insert the column into a non-existing variable.

Then the query should be like

LOAD DATA LOCAL INFILE 'C:/myfile.csv' INTO TABLE registration_no
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES #to remove first `n` rows usually used to remove header of a file.
(id, name, @dummy, @dummy, email);

Upvotes: 3

Related Questions