PHP CSV File – Creation and Reading
CSV is a very convenient format for generation, as it is very simple in structure. In this note, we will explore how files with the extension .csv are structured, how to create and parse them in PHP. It's very simple to do.
Below are simple PHP functions for creating and parsing CSV files. No libraries are needed - they are unnecessary for CSV!
CSV Format
To understand the essence of things, it is necessary to understand the specification of CSV files, how the format is structured. Let's briefly...
CSV (Comma-Separated Values) is a text format designed for representing tabular data.
-
Each line of the file is one row of the table.
-
The column value delimiter is the symbol:
,
(comma). For the Russian language,;
(semicolon) is used, because in Russian, a comma is used in decimal numbers. -
Values containing reserved characters:
",; \r\n or \n or \r
(double quote, comma, semicolon, newline) are enclosed in double quotes"
. -
If double quotes
"
are present in the value, they should look like two consecutive quotes""
. - The file line can be separated by symbols:
\r\n
or\n
.
That's all you need to know to work with CSV!
Example for the Russian language:
1965;Пиксель;E240 – формальдегид (опасный консервант)!;"красный, зелёный, битый";3000,00 1965;Мышка;"А правильней использовать ""Ёлочки""";;4900,00 "Н/д";Кнопка;Сочетания клавиш;"MUST USE! Ctrl, Alt, Shift";4799,00
Example for the English language:
1997,Ford,E350,"ac, abs, moon",3000.00 1999,Chevy,"Venture «Extended Edition»","",4900.00 1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00
Wiki-help
Most programs understand a more general format called DSV (delimiter-separated values), which allows the use of other characters as delimiters. In particular, in Russian and other locales, the comma is reserved for the decimal separator by default. Therefore, a semicolon or tab (TSV format) is used as the delimiter.
Today, CSV is understood as a set of values separated by any delimiters, in any encoding with any line endings. This significantly complicates data transfer from one program to another, despite the simplicity of the format.
Creating a CSV File in PHP
To create a CSV file, simply create a text file using the necessary column and row delimiters.
An important point is the file encoding. To display Cyrillic correctly, the encoding cp1251 (windows-1251) should be used.
Column delimiter
For the Russian language, the delimiter is ;
(semicolon). For English, ,
(comma).
Strings containing special characters: ",; \r\n
or \n
or \r
must be enclosed in double quotes "string".
Double quotes inside a string should be "cleaned" by placing another double quote before the quote: string "csv"
will become "string ""csv"""
. Enclosing in quotes allows the use of delimiters ;
, ,
inside column values without worrying about breaking the file when reading it.
Row delimiter
To separate rows in CSV files, you can use \r\n
(carriage return and line feed, CR LF). In this case, if a line break is needed inside a column value, it is indicated simply as \n
.
Also, a simple \n
(line feed, LF) can be used to separate a row. In this case, a line break inside a column value should be indicated as \r
(carriage return, CR).
Function for creating a CSV file
/** * Creates a CSV file from the provided array of data. * * @param array $create_data Array of data from which to create a CSV file. * @param string $file File path 'path/to/test.csv'. If not specified, it will simply return the result. * @param string $col_delimiter Column delimiter. Default: `;`. * @param string $row_delimiter Row delimiter. Default: `\r\n`. * * @return false|string CSV string or false if the file creation failed. * * @version 2 */ function kama_create_csv_file( $create_data, $file = null, $col_delimiter = ';', $row_delimiter = "\r\n" ){ if( ! is_array( $create_data ) ){ return false; } if( $file && ! is_dir( dirname( $file ) ) ){ return false; } // The string that will be written to the csv file $CSV_str = ''; // Loop through all the data foreach( $create_data as $row ){ $cols = array(); foreach( $row as $col_val ){ // Strings should be in quotes "" // Double quotes " inside strings should be preceded by another double quote " if( $col_val && preg_match('/[",;\r\n]/', $col_val) ){ // Fix line breaks if( $row_delimiter === "\r\n" ){ $col_val = str_replace( [ "\r\n", "\r" ], [ '\n', '' ], $col_val ); } elseif( $row_delimiter === "\n" ){ $col_val = str_replace( [ "\n", "\r\r" ], '\r', $col_val ); } $col_val = str_replace( '"', '""', $col_val ); // Precede " $col_val = '"'. $col_val .'"'; // Enclose in " } $cols[] = $col_val; // Add column to the data } $CSV_str .= implode( $col_delimiter, $cols ) . $row_delimiter; // Add row to the data } $CSV_str = rtrim( $CSV_str, $row_delimiter ); // Set the windows-1251 encoding for the string if( $file ){ $CSV_str = iconv( "UTF-8", "cp1251", $CSV_str ); // Create the csv file and write the string to it $done = file_put_contents( $file, $CSV_str ); return $done ? $CSV_str : false; } return $CSV_str; }
Now, to generate a CSV file, use this function like this:
$create_data = array( array( 'Header 1', 'Header 2', 'Header 3', ), array( 'string 2 "column 1"', '4799.01', 'string 2 "column 3"', ), array( '"Christmas trees"', 4900.01, 'red, green', ) ); echo kama_create_csv_file( $create_data, THEME_PATH .'csv_file.csv' ); /* Result: Header 1;Header 2;Header 3 "string 2 ""column 1""";"4799.00";"string 2 ""column 3""" """Christmas trees""";4900.01;"red, green" */
Reading a CSV File in PHP
When you need to get data from a CSV file, i.e., parse it and get the data into a variable, you can use the built-in PHP function str_getcsv().
There is also the fgetcsv() function, but it turned out to be capricious and does not always work as needed (it may mix up line breaks)...
Variant based on the str_getcsv() function:
/** * Reads a CSV file and returns the data as an array. * * @param string $file_path Path to the csv file. * @param array $file_encodings * @param string $col_delimiter Column delimiter (automatically determined by default) * @param string $row_delimiter Row delimiter (automatically determined by default) * * @version 6 */ function kama_parse_csv_file( $file_path, $file_encodings = ['cp1251','UTF-8'], $col_delimiter = '', $row_delimiter = '' ){ if( ! file_exists( $file_path ) ){ return false; } $cont = trim( file_get_contents( $file_path ) ); $encoded_cont = mb_convert_encoding( $cont, 'UTF-8', mb_detect_encoding( $cont, $file_encodings ) ); unset( $cont ); // Determine the delimiter if( ! $row_delimiter ){ $row_delimiter = "\r\n"; if( false === strpos($encoded_cont, "\r\n") ) $row_delimiter = "\n"; } $lines = explode( $row_delimiter, trim($encoded_cont) ); $lines = array_filter( $lines ); $lines = array_map( 'trim', $lines ); // Automatically determine the delimiter from two possible options: ';' or ','. // To calculate, take no more than 30 lines if( ! $col_delimiter ){ $lines10 = array_slice( $lines, 0, 30 ); // If one of the delimiters is not in the string, then the other is definitely the delimiter... foreach( $lines10 as $line ){ if( ! strpos( $line, ',') ) $col_delimiter = ';'; if( ! strpos( $line, ';') ) $col_delimiter = ','; if( $col_delimiter ) break; } // If the first method did not yield results, then delve into the task and count the number of delimiters in each line. // where more of the same number of occurrences of the found delimiter, that is the delimiter... if( ! $col_delimiter ){ $delim_counts = array( ';'=>array(), ','=>array() ); foreach( $lines10 as $line ){ $delim_counts[','][] = substr_count( $line, ',' ); $delim_counts[';'][] = substr_count( $line, ';' ); } $delim_counts = array_map( 'array_filter', $delim_counts ); // remove zeros // the number of equal values in the array is a potential delimiter $delim_counts = array_map( 'array_count_values', $delim_counts ); $delim_counts = array_map( 'max', $delim_counts ); // take only the maximum values of occurrences if( $delim_counts[';'] === $delim_counts[','] ) return array('Failed to determine the column delimiter.'); $col_delimiter = array_search( max($delim_counts), $delim_counts ); } } $data = []; foreach( $lines as $key => $line ){ $data[] = str_getcsv( $line, $col_delimiter ); // linedata unset( $lines[$key] ); } return $data; }
Usage:
$data = kama_parse_csv_file( '/path/to/file.csv' ); print_r( $data );
Converting .lsx, .xlsx file to .csv
To convert an Excel file to CSV, open it in Excel and save it in the .csv format:
If you need to do this conversion programmatically, look into online converters with an API or ready-made libraries.
-
Have you encountered such a task and know a more universal way? Please share in the comments.
I encountered it and spent several hours sifting through all sorts of non-universal "junk" from Google - one thing doesn't work, then another, you need to install a "clever" library... And that's how this note came about...
How to deliver the generated CSV file for download
To do this, you need to set the headers:
<?php header( 'Content-Type: text/csv; charset=utf-8' ); header( 'Content-Disposition: attachment; filename="file.csv"' ); $create_data = [ [ 'Header 1', 'Header 2', 'Header 3', ], ]; echo kama_create_csv_file( $create_data );