html table to CSV or Excel

Question

Alright, I'm starting to go a little crazy...

I'm looking at that thread.

It does work, but the output does everything by each LINE not by the HTML table rows. For example, I have an address that is in one HTML cell:

1234 Berryman Lane
Atlanta, GA 12345
Unit # 54A

That will be THREE rows when it outputs to excel, instead of one cell, with returns in it.

Further, there's no way to strip out the HTML that is inside of the HTML cells with that solution, as far as I know...

Finally, Excel gives a warning when opening that file.

What I'm getting at, is that I'd rather just have something that can take the inner most data in the HTML cells (not including HTML inside of a cell), and rip it to CSV. Is there anything that does this well these days?

UPDATE

Well, I just found the best thing yet, this is pretty perfect:

$table = 'myTable';
$file = 'exportFile';

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= "\"" . $row['Field']."\",";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= "\"" . $rowr[$j]."\",";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

I think this is my keeper. It goes to a CSV file with no issues, loads in Excel with no issues, it's my dream come true!

Either really, but due to my issues with excel, I figured CSV would be simpler? — Shackrock, May 22 '11 at 22:29
Do you need to rip offline files? Otherwise, why not look at [excel web query](http://stackoverflow.com/questions/3660844/how-do-you-query-a-website-in-vb6/3660926#3660926)? — GSerg, May 22 '11 at 22:32
If you want to write the files server side, the PHPExcel does a pretty good job (http://phpexcel.codeplex.com). — El Yobo, May 22 '11 at 22:34
@gserg, excel web query is problematic for a few reasons: namely because I'd like the files to be available offline when exported. But more than anything, because my site is behind a private password protected area, which I've read Excel doesn't do well with. — Shackrock, May 22 '11 at 22:36
@el, but PHPExcel won't take it from an already displayed HTML table will it? — Shackrock, May 22 '11 at 22:38
No, that's why I said "if you want to write the files server side". Presumably you've got code that wrote the table; just have the same code write the same data using PHPExcel instead. If you really want to write it from a rendered table (which seems a long and complex way to do things), then Rikhal's answer is correct; HTML can be saved as .xls and Excel will open it. — El Yobo, May 22 '11 at 23:02

score 1 · Answer 1 · answered May 22 '11 at 22:32

1

well, you can always use the same markup in excel as it can render html if you use the .xls file type, render the html table and change the content type to "application/vnd.ms-excel" specify a filename for that response as *.xls and you should have a usable excel sheet.

answered May 22 '11 at 22:32

Rikal

153
8

that's the problem I noted above. It lists every line break as another cell, which is not acceptable for the data as I'm trying to sort it later in excel... I need every HTML CELL to be a single cell in excel. Minus any HTML, if possible. – Shackrock May 22 '11 at 22:34
hmm... I use this approach quite often, can you give me a sample table ? – Rikal May 22 '11 at 22:36
test, looks like you could use the comment field if its not a big table. – Rikal May 22 '11 at 22:39
Well I was going to send as it is in Excel... which is every
gets turned into another cell row in excel when exported. – Shackrock May 22 '11 at 22:42

score 0 · Answer 2 · answered May 22 '11 at 22:55

If there are no dobule quotes in the data, you can wrap the content of each cell in double quotes so your data looks like:

"...","...","..."

Now you can have commas in the data. You may also need to deal with new lines and returns in the data, probably best to remove them completely but that's up to you.

score 0 · Accepted Answer · answered Oct 01 '11 at 00:14

Thanks for all suggestions. As stated in my edited post, this script below was a great solution:

$table = 'myTable';
$file = 'exportFile';

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= "\"" . $row['Field']."\",";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= "\"" . $rowr[$j]."\",";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

I think this is my keeper. It goes to a CSV file with no issues, loads in Excel with no issues, it's my dream come true!

I've since had an even BETTER experience with this: http://www.kunalbabre.com/projects/table2CSV.php . Or some newer forks of that: https://github.com/ZachWick/TableCSVExport or https://github.com/rubo77/table2CSV — Shackrock, Oct 31 '13 at 09:12

html table to CSV or Excel

3 Answers3