3

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.

contributor
  • 143
  • 3
  • 12

1 Answers1

3

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);
James
  • 1,819
  • 2
  • 8
  • 21
  • thanks for your answer, _Step 3: If your csv has headers to skip it use IGNORE 1 LINES_ , what do you mean by that `headers` ? – contributor Sep 18 '19 at 07:12
  • In your csv file you have `headers/ column name` like license plate, vehicle type, Brand ... when you try to import the first column will also gets imported into db. – James Sep 18 '19 at 07:15
  • 1
    Okay, the headers will be added to the `db` like `data`, is that right ? – contributor Sep 18 '19 at 07:17
  • Yes. Just experiment yourself so that you will learn from your mistakes. – James Sep 18 '19 at 07:17
  • Great, let me try that, will come back to make this as checked, thank you @james – contributor Sep 18 '19 at 07:18
  • Do I need to use exactly the same `column names`, as they are in `csv` file, or I can use the name of the `columns` that are there in my own `table` ? sorry for late reply , I was travelling ! – contributor Sep 18 '19 at 18:38
  • *(id, name, email);* this is the table.column_name. from csv it will just match based on the order. – James Sep 19 '19 at 04:09
  • I'm running the following query but this is not loading the data. `LOAD DATA LOCAL INFILE 'C:\carseller.csv' INTO TABLE registration_no FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id, license_plate,vehicle_type,brand,trade_name,apk_expiration,design,no_of_seats,@dummy1,@dummy2,@dummy3,@dummy4,@dummy5, @dummy6, @dummy7); ` – contributor Sep 19 '19 at 04:50
  • after column `I` in `csv` file,(see above image of csv) I don't want to load that data, that is why I have used `@dummy` for further 7 columns, – contributor Sep 19 '19 at 04:52
  • no error, the query run `successfully`, but the `data` is not imported – contributor Sep 19 '19 at 04:53
  • use should use forward slash when denoting the file path – James Sep 19 '19 at 04:55
  • my table looks like this `id`, `license_plate` , `vehicle_type`, `brand`, `trade_name`, `apk_expiration`, `design`, `no_of_seats` – contributor Sep 19 '19 at 04:56
  • `C:/carseller.csv` forward slash – James Sep 19 '19 at 04:57
  • Okay, just take a part of csv into new one by having only those columns before `I` then about one or two rows of data then try it out. – James Sep 19 '19 at 04:59
  • that is not working, I tried to make a `new` file with those columns with some `rows` but data is still not loading – contributor Sep 19 '19 at 05:15
  • I have `table.id`, and the `csv` don't have that `id` as column, although the rows are `numbered` in csv, will that make any problem ? – contributor Sep 19 '19 at 05:17
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199665/discussion-between-james-and-contributor). – James Sep 19 '19 at 05:29