Importing a CSV file to MySQL

Kembreg posted 4 years ago in General
I am trying to import a CSV file to MySQL.
I do the neccessary in HeidiSql and click "Import".
The computer is busy for a moment and then nothing happens.
No error message, no result message.
I check the database and the data is not there.

I see that HeidiSql is actually carrying out SQL commands.

The command is:

LOAD DATA LOCAL INFILE 'C:\\phrases.txt' INTO TABLE `w6wehfc9_baseX`.`phrases` CHARACTER SET utf8 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`Pindex`,`Lesson`,`English`);

My service provide told me that they do not allow the command LOAD DATA LOCAL INFILE for security reasons and that I should use HeidiSql to upload data to MySql.

Any help would be appreciated.
ansgar posted 4 years ago
If they do not allow so, you would get some error message - did you? I rather guess you have given wrong field terminator, encloser and/or line terminator?
Kembreg posted 4 years ago
Here is the csv:

272;15;the apples
273;15;the beds
274;15;the books
275;15;the boys
276;15;the brothers
277;15;the cats

Here is a jpg showing how I have HeidiSql setup:

Kembreg posted 4 years ago
Another suggestion would be to look in the first row and if any data matches a field name than too automatically select that field name. (i.e. default for each field is deselected).

For example, if the first line had:

Index, Lesson, English, German

Then the fields "Index", "Lesson", "English", "German" would be selected and everything else would be deselected.
ansgar posted 4 years ago
Hm, I think you should post these ideas into issue #1379, so they don't get lost.
Kembreg posted 4 years ago
Yes. I got a little confused in my posting.
ansgar posted 4 years ago
Avoid importing of first x lines can be done already by using the "Ignore X lines". Apart from that, your settings look exactly like they should. If you are sure there is no error message in the log panel I don't know what more to check. Probably test the same thing on a different server?
Kembreg posted 4 years ago

You mention the "log panel".
I looked and looked and could not find a log panel.

ansgar posted 4 years ago
The SQL log panel is the lower area with the line numbers and the SQL command history:
Kembreg posted 4 years ago
It worked!!

Thank you very muuuuuuch!


Please login to leave a reply, or register at first.