Import csv file

I am trying to import a csv file. It is an Excel csv file and is almost 400,000 rows. HeidiSQL was only able to import 70,900 rows. I need all rows imported. How can I do that?

Mike

You have to find the right control characters. Especially the line ending is important, but also the field terminator and field quote.

It took me a few minutes to find those parameters, but I found them posted on a website, gave them a try, and the import worked. I used the following:

Fields terminated by: , (comma)
Fields enclosed by: " (double quotes)
Fields escaped by: " (double quotes)
Lines terminated by: \r\n (I would never have guessed this)

As I said, I was able to import 70,900 lines correctly, so the import must be working correctly, but the csv file has almost 400,00 lines (rows). Is there a limit to how many lines I can import?

There is no limit. You could post the generated LOAD DATA command here so we can find out more.

I believe you still have a wrong line ending character, probably you have UNIX style linebreaks (\n), not Windows linebreaks (\r\n). Or the escape char should be \ not ". Can you post a few lines of your textfile here?

Ansgar,

I figured it out. I split a column in Excel (saved to csv file)and the resulting leftmost column is my Primary Key field which, as a result of the split, now has many duplicates. There is nothing wrong with the import. I will have to create a new Primary Key field.

Thanks