I have a table in mysql which is quite big for having over 100k rows and I want to export it to excel. However, I tried the export to excel function on phpmyadmin but it takes forever to dump the excel file. It's not even dumping. The error is always, "the connection is reset". Is there an alternative way on how to do this??
8
-
1100,000 rows will require either an OfficeOpenXML-format .xlsx file, or 2 worksheets in a BIFF-format .xls file, because an xls file can have a maximum of 65,536 rows per worksheetMark Baker– Mark Baker2014-03-05 23:33:30 +00:00Commented Mar 5, 2014 at 23:33
-
what about reading all the rows with php and use this to export as a excel file github.com/PHPOffice/PHPExcel/blob/develop/Documentation/…Jorge Y. C. Rodriguez– Jorge Y. C. Rodriguez2014-03-05 23:33:36 +00:00Commented Mar 5, 2014 at 23:33
-
Dump it to a csv file.Tony Hopkinson– Tony Hopkinson2014-03-05 23:35:02 +00:00Commented Mar 5, 2014 at 23:35
-
@MarkBaker Actually, I was able to figure out something and deleted some rows now it was down 40k. How am I supposed to export the table?user3310979– user33109792014-03-05 23:37:13 +00:00Commented Mar 5, 2014 at 23:37
-
Depends if you want a real Excel format file, or if a csv file is adequateMark Baker– Mark Baker2014-03-05 23:37:52 +00:00Commented Mar 5, 2014 at 23:37
|
Show 3 more comments
3 Answers
First, 100k rows in Excel sounds like a horrible idea and of course it'll take awhile. This is going to take awhile just to open. If you MUST do this try:
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
This should give you a file called: /tmp/orders.csv which will open in Excel.
Comments
The slowness of your export is likely due to the server on which phpmyadmin runs. I regularly export millions of rows with surprising speed.
If you have access to the file system of your database server, this is a very fast way of converting to .csv, which in turn can be opened by Excel.
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
See this: http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/
Comments
I've used the OUTFILE method before to do this:
SELECT
'First Name',
'Last Name',
'Email Address'
UNION
SELECT
First_Name,
Last_Name,
Email
INTO OUTFILE
'/tmp/data_2012-05-03.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
FROM
`data`
WHERE
Reg_Date >= '2012-05-02 00:00:00' AND
Reg_Date <= '2012-05-02 23:59:59' AND
Email_Status = 1;
2 Comments
user3310979
Hi thanks for the reply. May i know where did you execute this code?
Quixrick
Sorry, I'm not sure what you mean.