CODEWITHSUNDEEP
mysqlcsvimportphpmyadmin
user1817626
user1817626

Reputation: 83

import single column csv file into mysql using phpmyadmin

i have a single column csv file with columns as 

Mahindra Logan 
Mahindra Xylo
Maruti Suzuki Swift

when I view the file with notepad+ it shows complete 514 lines but when I view in notepad , all the data is combined and shown into 3 lines. now when I import in phpmyadmin using CSV USING LOAD DATA, and selecting only "\n" as columns terminated by . 3-4 entries are imported. please help me out if possible

Upvotes: 1

Views: 3924

Answers (1)

Joe Seff
Joe Seff

Reputation: 1830

It might be as a result of several things:

  1. If your table has several columns and you are importing a file with one column, the data will be truncated by division e.g. Assume you have a table called mytable with two columns AutoID and fname. Your file however, only has one column and 20 rows. AutoID is an auto-increment column in this case. If you use the command:

    LOAD DATA LOCAL INFILE 'c:\\myfile.csv' INTO TABLE mydatabase.mytable

    Only ten rows of data will be imported. The reason is, mysql assumes that you are trying to import the single column of data into two columns of data. However, since AutoID values are automatically generated, the values to be inserted are discarded. To solve such a problem, I did the following. I opened up the csv file in excel and added a column to the left of the existing column with NULL values. My sql statement ended up being like"

    LOAD DATA LOCAL INFILE 'c:\\myfile.csv' INTO TABLE mydatabase.mytable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

    My file looked like:

    NULL, JAMES,
NULL, JOHN,

  2. Make sure your column values are properly delimited i.e. If the above values were all that you wanted to import into the mysql table. Use the following command

    LOAD DATA LOCAL INFILE 'c:\\myfile.csv' INTO TABLE mydatabase.mytable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

Upvotes: 2

Related Questions