CODEWITHSUNDEEP
phpmysqlphpexcel
Kim Stacks
Kim Stacks

Reputation: 10822

how to convert 500,000 rows of data with 39 columns from MySQL into Excel

I have a PHP web app that can convert data into an excel file using PHPExcel. The web app is running inside a Linux machine.

These are the steps involved.

  1. I need to run several join queries to get the data I want.
  2. I then take the data and write them into the excel file using PHPExcel.
  3. Repeat step 1-2 in batches of ~5000 rows.

I noticed that the time taken to generate ~50000 rows alone (which is about 10%) is nearly 15 minutes which is too long.

I need to generate this excel file once a day.

Off the top of my head, I have the following options.

a) Instead of doing step 1 and 2 repeatedly for about 10 times, I could retrieve all the data I need in step 1 and store the data into a separate MySQL table.

Then I try PHPExcel and read this table once and write the excel file once.

At the end, I will wipe this table clean again.

b) Do the same as a) but instead of using PHPExcel, find a way to run MySQL For Excel in a cloud Windows instance to generate the Excel file.

If need further formatting, I will use PHPExcel to format this Excel file.

The way to send data between Linux machine and the windows cloud instance is through email attachments because the Linux machine is within a firewall.

I am posting this question to see if anybody has a better idea.

In the meantime, I will try option a).

I have no idea how to run a windows instance and microsoft excel for option b).

Upvotes: 0

Views: 1234

Answers (1)

Dmitriy.Net
Dmitriy.Net

Reputation: 1500

As for me, the best way, is use mysql SELECT INTO OUTFILE. You can export your result to csv, that Microsoft Excel will open correctly

Example:

SELECT * INTO OUTFILE '/tmp/result.csv'
   FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
    LINES TERMINATED BY '\n'
FROM table

After that, you can use php library like PHPexcel for convertion csv to xls

include 'PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('CSV');

// If the files uses a delimiter other than a comma (e.g. a tab), then tell the reader
$objReader->setDelimiter("\t");
// If the files uses an encoding other than UTF-8 or ASCII, then tell the reader
$objReader->setInputEncoding('UTF-16LE');

$objPHPExcel = $objReader->load('MyCSVFile.csv');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('MyExcelFile.xls');

Upvotes: 1

Related Questions