Php открыть страницу как csv

Convert HTML to CSV in php?

I would like to convert this into a csv/excel file by php.

So each is a row in excel, and each is a cell in the row,

Please how can this be done?

I have researched and found Converting HTML Table to a CSV automatically using PHP? but the answer does not work properly for me, Im getting all the cell results in one ‘cell’, so each row only have one cell.

This is what i have tried;

Where $table is the html above. Using simple html dom plugin

3 Answers 3

it seems that produced CVS has problems with some MS excel version. according to this page:

so i modified the code as:

but says also this:

So i changed the ID. to id. All in all, with lower case ‘id’ and ‘;’ as delimiter this loaded as expected in MS excel 2003.

UPDATED:

i found a way to properly load a UTF8 .csv into excel by adding the BOM signature in the file. In PHP this can be done:

these 3 characters (1 unicode actually) forces excel and the likes to understand the .csv file AS utf8 and therefore decoding it internally.

Creating a CSV file with PHP.

This a short tutorial on how to create a CSV file with PHP.

In this guide, we will also show you how to “force” the browser to download it as a file. This is extremely useful for creating “Export to CSV” buttons.

Take a look at the following PHP sample.

Here are the steps that we took.

1. We gave our CSV file a name. This is the name that the file will have when the user downloads it.
2. After that, we set our Content-Type and Content-Disposition headers. These headers will force the user’s browser to download the file.
3. We then created a multidimensional array for our CSV data. This array should be an array that consists of arrays. Each array inside the $data array represents a row in our CSV file. The elements inside these arrays represents a column. If you look at the code above, you will see that we used a simple example of 4 rows and 2 columns. To understand this better, you can modify the array and change things around.
4. After that, we opened up a PHP output stream.
5. We then looped through the $data array. Inside the loop, we write each row to the output stream by using the function fputcsv.
6. Finally, we closed the output stream.

If you run this PHP script on your server, you will see that a CSV file is automatically downloaded by the browser and that it contains the test data from our array.

Reading and Writing CSV Files in PHP

CSV for Comma-Separated Values is a well-established file format to import and export data.

In this tutorial, we will learn how to read and write CSV files in PHP with the help of examples.

We'll also explore how to perform the most common manipulation such as filtering, sorting, and transforming the data.

Last but not least, we'll learn how to parse a CSV file of 1 million lines in PHP in a fast and memory-efficient manner.

I used PHP8 while coding and testing the examples:

• The examples with native functions are compatible with very old PHP versions.
• The examples using league/csv require a PHP7 version as a minimum.
• The whole code and examples are available in a GitHub repository; it comes packaged as a simple Symfony project and provides a Docker image.

Our Example of CSV File​

We'll use a CSV file containing data about movies in this tutorial.

Here is a sample of this data set in plain text:

Usually, in the CSV format:

1. The first line contains the headers
2. Each following line is a data row
3. A comma separates each cell in a row
4. A double-quote encapsulate cells containing text

Sometimes, we can find different characters to separate the data, encapsulate strings, or escape special characters.

Let's look at our sample file in a tabular way:

id	title	poster	overview	release_date	genres
181808	Star Wars: The Last Jedi	https://. /mWII.jpg	Rey develops her newly discovered abilities with the guidance of Luke Skywalker, [. ]	1513123200	Documentary
383498	Deadpool 2	https://. /3VAd.jpg	Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad [. ]	1526346000	Action, Comedy, Adventure
157336	Interstellar	https://. /BvIx.jpg	Interstellar chronicles the adventures of a group of explorers who make use of a [. ]	1415145600	Adventure, Drama, Science Fiction

Read and Write CSV Files (Old-Fashioned Way)​

Read a CSV File Using fgetcsv​

The steps to read a CSV file in PHP with native functions:

1. Open the file with fopen using the read mode
2. Parse the CSV rows from the file with fgetcsv
3. Close the file with fclose

It loads each row as an array; the first row contains the headers.

In some cases, we want to manipulate rows independently, let's transform each CSV row into an associative array:

We now have each row as an associative array, with headers as indexes of each array. We can manipulate the rows independently as simple JSON objects.

Write a CSV File Using fputcsv​

The steps to create a CSV file in PHP with native functions:

1. Open the file with fopen using the write mode
2. Write each array in a CSV row with fputcsv
3. Close the file with fclose

The default separator is the comma, we can specify different parameters such as separator , enclosure , escape and eol when using fgetcsv and fputcsv functions.

Append New Rows in a CSV File Using fputcsv​

Let's append rows at the end of an existing file:

The essential piece is to understand the mode parameter used in the fopen function:

• r is a read-only mode
• w is a write-only mode, starting at the beginning of the file (erase an existing file)
• w+ is a read/write mode, starting at the beginning of the file (erase an existing file)
• a is a write-only mode, starting at the end of the file (append content at the end of the file)
• a+ is a read/write mode, starting at the end of the file (append content at the end of the file)

Using the modes w , w+ , a , a+ will create the CSV file if it does not exist yet.

Read and Write CSV Files (Modern Way)​

This previous reading and writing approach is totally fine, but some modern libraries can make our lives easier and our code a bit more readable.

This tutorial uses the excellent league/csv library, which provides a clean and straightforward CSV manipulation API.

We can also note the great box/spout library, which is very strong too, offers support for other formats and can be used to read and write Excel files in PHP.

Install league/csv​

Let's install the league/csv library by using the composer package manager:

fputcsv

fputcsv() formats a line (passed as a fields array) as CSV and writes it (terminated by a newline) to the specified file stream .

Parameters

The file pointer must be valid, and must point to a file successfully opened by fopen() or fsockopen() (and not yet closed by fclose() ).

An array of string s.

The optional separator parameter sets the field delimiter (one single-byte character only).

The optional enclosure parameter sets the field enclosure (one single-byte character only).

The optional escape parameter sets the escape character (at most one single-byte character). An empty string ( "" ) disables the proprietary escape mechanism.

The optional eol parameter sets a custom End of Line sequence.

Note:

If an enclosure character is contained in a field, it will be escaped by doubling it, unless it is immediately preceded by an escape .

Return Values

Returns the length of the written string or false on failure.

Changelog

Version	Description
8.1.0	The optional eol parameter has been added.
7.4.0	The escape parameter now also accepts an empty string to disable the proprietary escape mechanism.

Examples

Example #1 fputcsv() example

$list = array (
array( ‘aaa’ , ‘bbb’ , ‘ccc’ , ‘dddd’ ),
array( ‘123’ , ‘456’ , ‘789’ ),
array( ‘»aaa»‘ , ‘»bbb»‘ )
);

$fp = fopen ( ‘file.csv’ , ‘w’ );

foreach ( $list as $fields ) <
fputcsv ( $fp , $fields );
>

The above example will write the following to file.csv :

Notes

Note: If PHP is not properly recognizing the line endings when reading files either on or created by a Macintosh computer, enabling the auto_detect_line_endings run-time configuration option may help resolve the problem.

See Also

• fgetcsv() — Gets line from file pointer and parse for CSV fields

User Contributed Notes 28 notes

If you need to send a CSV file directly to the browser, without writing in an external file, you can open the output and use fputcsv on it..

<?php
$out = fopen ( ‘php://output’ , ‘w’ );
fputcsv ( $out , array( ‘this’ , ‘is some’ , ‘csv «stuff», you know.’ ));
fclose ( $out );
?>

If you need to save the output to a variable (e.g. for use within a framework) you can write to a temporary memory-wrapper and retrieve it’s contents:

<?php
// output up to 5MB is kept in memory, if it becomes bigger it will automatically be written to a temporary file
$csv = fopen ( ‘php://temp/maxmemory:’ . ( 5 * 1024 * 1024 ), ‘r+’ );

fputcsv ( $csv , array( ‘blah’ , ‘blah’ ));

// put it all in a variable
$output = stream_get_contents ( $csv );
?>

Sometimes it’s useful to get CSV line as string. I.e. to store it somewhere, not in on a filesystem.

<?php
function csvstr (array $fields ) : string
<
$f = fopen ( ‘php://memory’ , ‘r+’ );
if ( fputcsv ( $f , $fields ) === false ) <
return false ;
>
rewind ( $f );
$csv_line = stream_get_contents ( $f );
return rtrim ( $csv_line );
>
?>

if you want make UTF-8 file for excel, use this:

$fp = fopen($filename, ‘w’);
//add BOM to fix UTF-8 in Excel
fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

Please note, that fputcsv ist not always enclosing strings with the enclosure character.

<?php
$fh = fopen ( ‘file.csv’ , ‘w’ );

$a = [ ‘One 1’ , ‘Two’ , ‘Three 3’ ];

fputcsv ( $fh , $a , «\t» );

fclose ( $fh );
?>

results in a file containing the line:
«One 1» Two «Three 3»

It seems that only strings containing at least one of the following characters are enclosed:

— the delimiter character
— the enclosure character
— the escape character
— \n (new line)
— \r (line feed)
— \t (tab)
— blank

I hope this saves you the hour it took me to get to the bottom of this behaviour.

Using fputcsv to output a CSV with a tab delimiter is a little tricky since the delimiter field only takes one character.
The answer is to use the chr() function. The ascii code for tab is 9, so chr(9) returns a tab character.

<?php
fputcsv ( $fp , $foo , ‘\t’ ); //won’t work
fputcsv ( $fp , $foo , ‘ ‘ ); //won’t work

it should be:
<?php
fputcsv ( $fp , $foo , «\t» );
?>
you just forgot that single quotes are literal. meaning whatever you put there that’s what will come out so ‘\t’ would be same as ‘t’ because \ in that case would be only used for escaping but if you use double quotes then that would work.

the solution for how to solve the encoding problem while converting an array to csv file is below.

$fp = fopen(‘php://memory’, ‘w’);
//add BOM to fix UTF-8 in Excel
fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
// output the column headings
//fputcsv($fp, array(‘Topic’, ‘Title’, ‘URL’, ‘Keywords’, ‘Score’, ‘FB_count’, ‘TW_count’, ‘|’));
if(isset($trend)) <
foreach ( $trend as $myField ) <
fputcsv($fp, $myField, ‘|’);
>
>

Utility function to output a mysql query to csv with the option to write to file or send back to the browser as a csv attachment.

<?php
function query_to_csv ( $db_conn , $query , $filename , $attachment = false , $headers = true ) <

if( $attachment ) <
// send response headers to the browser
header ( ‘Content-Type: text/csv’ );
header ( ‘Content-Disposition: attachment;filename=’ . $filename );
$fp = fopen ( ‘php://output’ , ‘w’ );
> else <
$fp = fopen ( $filename , ‘w’ );
>

$result = mysql_query ( $query , $db_conn ) or die( mysql_error ( $db_conn ) );

if( $headers ) <
// output header row (if at least one row exists)
$row = mysql_fetch_assoc ( $result );
if( $row ) <
fputcsv ( $fp , array_keys ( $row ));
// reset pointer back to beginning
mysql_data_seek ( $result , 0 );
>
>

while( $row = mysql_fetch_assoc ( $result )) <
fputcsv ( $fp , $row );
>

// Using the function
$sql = «SELECT * FROM table» ;
// $db_conn should be a valid db handle

// output as an attachment
query_to_csv ( $db_conn , $sql , «test.csv» , true );

// output to file system
query_to_csv ( $db_conn , $sql , «test.csv» , false );
?>

I’ve created a function for quickly generating CSV files that work with Microsoft applications. In the field I learned a few things about generating CSVs that are not always obvious. First, since PHP is generally *nix-based, it makes sense that the line endings are always \n instead of \r\n. However, certain Microsoft programs (I’m looking at you, Access 97), will fail to recognize the CSV properly unless each line ends with \r\n. So this function changes the line endings accordingly. Secondly, if the first column heading / value of the CSV file begins with uppercase ID, certain Microsoft programs (ahem, Excel 2007) will interpret the file as being in the SYLK format rather than CSV, as described here: http://support.microsoft.com/kb/323626

This function accommodates for that as well, by forcibly enclosing that first value in quotes (when this doesn’t occur automatically). It would be fairly simple to modify this function to use another delimiter if need be and I leave that as an exercise to the reader. So quite simply, this function is used for outputting CSV data to a CSV file in a way that is safe for use with Windows applications. It takes two parameters + one optional parameter: the location of where the file should be saved, an array of data rows, and an optional array of column headings. (Technically you could omit the headings array and just include it as the first row of the data, but it is often useful to keep this data stored in different arrays in practice.)

function mssafe_csv ( $filepath , $data , $header = array())
<
if ( $fp = fopen ( $filepath , ‘w’ ) ) <
$show_header = true ;
if ( empty( $header ) ) <
$show_header = false ;
reset ( $data );
$line = current ( $data );
if ( !empty( $line ) ) <
reset ( $line );
$first = current ( $line );
if ( substr ( $first , 0 , 2 ) == ‘ID’ && ! preg_match ( ‘/[«\\s,]/’ , $first ) ) <
array_shift ( $data );
array_shift ( $line );
if ( empty( $line ) ) <
fwrite ( $fp , «\» < $first >\»\r\n» );
> else <
fwrite ( $fp , «\» < $first >\»,» );
fputcsv ( $fp , $line );
fseek ( $fp , — 1 , SEEK_CUR );
fwrite ( $fp , «\r\n» );
>
>
>
> else <
reset ( $header );
$first = current ( $header );
if ( substr ( $first , 0 , 2 ) == ‘ID’ && ! preg_match ( ‘/[«\\s,]/’ , $first ) ) <
array_shift ( $header );
if ( empty( $header ) ) <
$show_header = false ;
fwrite ( $fp , «\» < $first >\»\r\n» );
> else <
fwrite ( $fp , «\» < $first >\»,» );
>
>
>
if ( $show_header ) <
fputcsv ( $fp , $header );
fseek ( $fp , — 1 , SEEK_CUR );
fwrite ( $fp , «\r\n» );
>
foreach ( $data as $line ) <
fputcsv ( $fp , $line );
fseek ( $fp , — 1 , SEEK_CUR );
fwrite ( $fp , «\r\n» );
>
fclose ( $fp );
> else <
return false ;
>
return true ;
>

Alright, after playing a while, I’m confident the following replacement function works in all cases, including the ones for which the native fputcsv function fails. If fputcsv fails to work for you (particularly with mysql csv imports), try this function as a drop-in replacement instead.

Arguments to pass in are exactly the same as for fputcsv, though I have added an additional $mysql_null boolean which allows one to turn php null’s into mysql-insertable nulls (by default, this add-on is disabled, thus working identically to fputcsv [except this one works!]).

function fputcsv2 ( $fh , array $fields , $delimiter = ‘,’ , $enclosure = ‘»‘ , $mysql_null = false ) <
$delimiter_esc = preg_quote ( $delimiter , ‘/’ );
$enclosure_esc = preg_quote ( $enclosure , ‘/’ );

$output = array();
foreach ( $fields as $field ) <
if ( $field === null && $mysql_null ) <
$output [] = ‘NULL’ ;
continue;
>

$output [] = preg_match ( «/(?: $ < delimiter_esc >| $ < enclosure_esc >|\s)/» , $field ) ? (
$enclosure . str_replace ( $enclosure , $enclosure . $enclosure , $field ) . $enclosure
) : $field ;
>