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
It might be as a result of several things:
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,
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'
