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 );