CODEWITHSUNDEEP
mysqlexcel
wingerse
wingerse

Reputation: 3796

Inserting many rows into mysql database

I have about 400,000 rows. I have a MS excel file which contains all the data. So I changed ms excel rows to this eg:

INSERT INTO `numberlist`(`Names`, `Numbers`) VALUES ("  Amdadul  Haque >Haque >Sun    Island     > > >  "," 7899975 ");

But the inserting fails after 300 secs, is there anyway to do this? I can't even convert excel files to sql.

I am using xampp in Windows 7 32 bit .

Upvotes: 0

Views: 2709

Answers (2)

Jatinder Kumar
Jatinder Kumar

Reputation: 513

You can use following syntax for MySQL

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The length of query is limited by max_allowed_packet in general. But you can easily add around 200-300 rows in single query based on length of data in query.

This should solve your problem. I am assuming you are using MySQL Database. It is not mentioned in query. MS SQL Server 2008 and above also support this format of query.

For other databases you can check manual to see similar solution.

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

1) Save your Excel data as a csv file (In Excel 2007 using Save As)

2) 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.

3) Start the MySQL Command Prompt

4) use below script

LOAD DATA LOCAL INFILE 'C:\\temp\\yourfile.csv' INTO TABLE database.table FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (field1, field2);

5) Done!

reference link: http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html

Upvotes: 0

Related Questions