Geek asks and answers

Import large xls file into mysql database

I want to import a large xlsx file (spreadsheet file) into a mysql database. I did this before phpmyadmin, but this file is too large (205000 lines).

so when I want to do this with phpmyadmin, it took a long time and was not done.

What is the best and fastest way to import it into mysql database?

+3
source share
3 answers

You can do this using the command LOAD DATAin MySQL: http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html

Save your Excel data as a csv file (In Excel 2007 using Save As)
Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
Start the MySQL Command Prompt (I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
Enter this command:
LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);
[Edit: Make sure to check your single quotes (') and double quotes (") if you copy and paste this code]
Done!
+6
source

Navicat MySQL. 250 + xlsx, Navicat , .

, MySQL , max_allowed_packet my.ini , , 128M.

0

Toad for MySQL (Freeware) would be another alternative.

0
source

More articles:


All Articles