4

Possible Duplicate:
PHP code to convert a MySQL query to CSV

What is the best and cleanest way to export mysql query to CSV (for Excel 2003 and later)? "Best" in terms of being tested, maintained, configurable ... so something like a (standard) PHP library would be the ideal solution.

It should correctly handle enclosure, escaping, charset, cr-lf, field names in 1st row, ... etc.

There have been couple of questions on this here at SO (like this one) but none really focuses on the properties I require above.

Some ideas of mine:

  • use fputcsv? (but charset, escaping and cr-lf seemingly cannot be set!)
  • extract some code from phpmyadmin...?
  • SELECT * INTO OUTFILE doesn't work at my server (access denied)
  • system("mysqldump ...") is not possible (safe mode hosting)
Community
  • 1
  • 1
Tomas
  • 57,621
  • 49
  • 238
  • 373
  • Hmm. I'm not quite sure what it is you want. A CSV file is not an XLS file. It's simply a comma (or semi-colon) separated file. That doesn't have much to do with Excel 2003 specifically. I don't see why the "or" in my above linked `answer` wont cater for your issue? Are you looking for an out of the box solution? Like I said, it is just a CSV.. Above solution takes care of escaping and all you need to do is add "field names in 1st row" – Richard Dec 17 '11 at 18:14
  • 1
    It warms my heart to see a well formatted and researched question. You have my +1. – Madara's Ghost Dec 17 '11 at 18:23
  • Thanks @Truth. I tried hard :) – Tomas Dec 17 '11 at 19:30
  • @Richard, I wrote "csv for excel" because I guess there are probably nuances in the CSV formats - in PHPmyAdmin you have options CSV and "CSV for MS Excel" ... – Tomas Dec 17 '11 at 19:36

1 Answers1

1

Like you say, fputcsv is rather limited for your requirements. Extracting from phpMyAdmin is certainly possible (here's the export logic), but you might have more success using a dedicated library. Here are a couple of choices:

  • php-csv-utils. It's lightweight, configurable and looks like it'd drop easily into most applications.
  • File_CSV from PEAR, lacking in documentation but at least there was a release in 2011.

Having used neither of these, I'd be reluctant to pick one but based on documentation and usability I'd go for the first one.

Additional: I've just spotted this SO question. Charles's answer is so eerily like mine I thought I ought to link to it here!

Community
  • 1
  • 1
cmbuckley
  • 40,217
  • 9
  • 77
  • 91
  • In the end I have used [php-csv-utils](http://code.google.com/p/php-csv-utils/). Looking at the reference to the "new home" which doesn't actually work, the tool seems currently unmaintained though. But works well and perhaps needs no mtce :-) – Tomas Feb 14 '14 at 08:35