How to extract data from csv file in PHP

Question

I have a csv file which looks like this

$lines[0] = "text, with commas", "another text", 123, "text",5;
$lines[1] = "some without commas", "another text", 123, "text";
$lines[2] = "some text with commas or no",, 123, "text";

And I would like to have a table:

$t[0] = array("text, with commas", "another text", "123", "text","5");
$t[1] = array("some without commas", "another text", "123", "text");
$t[2] = array("some text, with comma,s or no", NULL , "123", "text");

If I use split($lines[0],",") I'll get "text" ,"with commas" ... Is there any elegant way to do it?

Use a tool built for the purpose (e.g. `fgetcsv` to name one) instead of `explode` — salathe, May 10 '10 at 18:59
One method (fgetcsv is better though) is to join adjacent fields until the merged field ends in a quote if the field starts with a quote... — Gert van den Berg, May 17 '18 at 08:11

score 52 · Accepted Answer · edited Feb 16 '12 at 14:59

52

You can use fgetcsv to parse a CSV file without having to worry about parsing it yourself.

Example from PHP Manual:

$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

edited Feb 16 '12 at 14:59

Gordon

312,688
75
539
559

answered May 10 '10 at 18:39

Matt

43,482
6
101
102

Is there similar function for a line? – liysd May 10 '10 at 18:45
Yes, `fgets` will get an entire line. – Mitch Dempsey May 10 '10 at 18:45
@liysd - Yes, you can pass a string to [str_getcsv](http://php.net/str_getcsv), however this is only available in PHP 5.3+. The comments section has a replacement, though. – Matt May 10 '10 at 18:48
I mean a line is input not a file handle – liysd May 10 '10 at 18:48
If the line is input, not from a file, please clarify that in the question. – salathe May 10 '10 at 18:56
1

New versions can have "0" passed for the second (optional parameter), for unlimited line length – Gert van den Berg May 17 '18 at 08:13

score 24 · Answer 2 · edited May 23 '17 at 11:54

In addition to Matt's suggestion, you can also use SplFileObject to read in the file:

$file = new SplFileObject("data.csv");
$file->setFlags(SplFileObject::READ_CSV);
$file->setCsvControl(',', '"', '\\'); // this is the default anyway though
foreach ($file as $row) {
    list ($fruit, $quantity) = $row;
    // Do something with values
}

^{source: http://de.php.net/manual/en/splfileobject.setcsvcontrol.php}

score 4 · Answer 3 · answered Nov 02 '16 at 16:19

4

you can read the data using the following function.

  function readCSV() {
    $csv = array_map('str_getcsv', file('data.csv'));
    array_shift($csv); //remove headers


}

http://www.pearlbells.co.uk/how-to-sort-a1a2-z9z10aa1aa2-az9az10-using-php/

answered Nov 02 '16 at 16:19

Liz Eipe C

265
1
5

score 3 · Answer 4 · answered May 10 '10 at 19:14

here is also a simple method to get read csv file.

$sfp = fopen('/path/to/source.csv','r'); 
$dfp = fopen('/path/to/destination.csv','w'); 
while ($row = fgetcsv($sfp,10000,",","")) { 
 $goodstuff = ""; 
 $goodstuff = str_replace("¦",",",$row[2]); 
 $goodstuff .= "\n"; 
 fwrite($dfp,$goodstuff); 
} 
fclose($sfp); 
fclose($dfp);

score 2 · Answer 5 · answered Sep 09 '18 at 00:28

Maybe my code solves your problem:

// Parse all content from csv file and generate array from line.
function csv_content_parser($content) {
  foreach (explode("\n", $content) as $line) {
    // Generator saves state and can be resumed when the next value is required.
    yield str_getcsv($line);
  }
}
// Get content from csv file.
$content = file_get_contents('your_file.csv');
// Create one array from csv file's lines.
$data = array();
foreach (csv_content_parser($content) as $fields) {
  array_push($data, $fields);
}

In result you have an array with all values from csv. It would be something like:

Array
(
    [0] => Array
        (
            [0] => text, with commas
            [1] => another text
            [2] => 123
            [3] => text
            [4] => 5
        )

    [1] => Array
        (
            [0] => some without commas
            [1] => another text
            [2] => 123
            [3] => text
        )

    [2] => Array
        (
            [0] => some text, with comma,s or no
            [1] =>  NULL 
            [2] => 123
            [3] => text
        )

)

score 1 · Answer 6 · edited Jul 02 '16 at 02:27

1

Suppose you have a create a function for same things, Then it should look like

function csvtoarray($filename='', $delimiter){

    if(!file_exists($filename) || !is_readable($filename)) return FALSE;
    $header = NULL;
    $data = array();

    if (($handle = fopen($filename, 'r')) !== FALSE ) {
        while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE)
        {   
            if(!$header){
                $header = $row;
            }else{
                $data[] = array_combine($header, $row);
            }
        }
        fclose($handle);
    }
    if(file_exists($filename)) @unlink($filename);

    return $data;
}

$data = csvtoarray('file.csv', ',');

print_r($data);

edited Jul 02 '16 at 02:27

Pang

9,564
146
81
122

answered Sep 10 '15 at 10:31

Harikesh Yadav

61
1
1
8

3

Ummm.. this deletes the source csv file, which has got to be the wrong thing to do, like 99% of the time. – ftrotter Oct 14 '17 at 11:19
You have to read code. I edit code for my use https://stackoverflow.com/a/59472196/5714639 – Lukáš Kříž Dec 24 '19 at 18:11

score 0 · Answer 7 · answered Oct 10 '17 at 07:58

You could use something like https://github.com/htmlburger/carbon-csv that allows column mapping:

$csv = new \Carbon_CSV\CsvFile('path-to-file/filename.csv');
$csv->set_column_names([
    0 => 'first_name',
    1 => 'last_name',
    2 => 'company_name',
    3 => 'address',
]);
foreach ($csv as $row) {
    print_r($row);
}

The result of the below code would be something like:

Array
(
    [0] => Array
        (
            [first_name] => John
            [last_name] => Doe
            [company_name] => Simple Company Name
            [address] => Street Name, 1234, City Name, Country Name
        )
    [1] => Array
        (
            [first_name] => Jane
            [last_name] => Doe
            [company_name] => Nice Company Name
            [address] => Street Name, 5678, City Name, Country Name
        )
)

Another library that does the same thing(and much more) is http://csv.thephpleague.com/9.0/reader/

score 0 · Answer 8 · answered Apr 11 '18 at 17:08

When you want to keep the index (first line) for multidimensional result array, you can use:

$delim      = ';';
$csvFile    = file($csv_file);
$firstline  = str_getcsv($csvFile[0], $delim);
$data       = array();
foreach ($csvFile as $line) {
    $line   = str_getcsv($line, $delim);
    $data[] = array_combine($firstline, $line);
}

score 0 · Answer 9 · edited Sep 02 '18 at 00:59

I've built an application to extract data from a CSV file , this php application was used to show a daily quote for users.

The full project on github: 365-quotes-php-csv.

Also this is the class Code for the application i've built

  <?php
/*
Main Class 
please note :
1- the CSV file must be comma separated (,) and each line must End with (;).
2- Feel free to edit the all.CSV file and add all of your 366 New Quotes.
3- don't change any thing specially the CSV file Location.
---------------------------------------------------------------------------
RISS.WORK all copy rights reserved 2018
please Don't Remove
Github/RissWork
Email : info@riss.work
*/
class Quote{

    //properties
        private $_quote,$_allQuotes;
        private static $_instance = null;

    //Constructor
        private function __construct(){
            //Day Count
            $dayCount = date(z);

            if($this->readCsvAndGetQuote($dayCount)){
                return $this->getQuote();
            }else{
                echo 'Error Cannot open the .CSV File';
            }
        }





    //Methods

    //get Instance
    public function getInstance(){
            if(!isset(self::$_instance)){
                self::$_instance = new Quote();
            }
            return self::$_instance;
        }//end of get Instance




    //get daily Quote   
    public function getQuote(){
            return $this->_quote;
        }//end of get Quote




    //Read CSV
    private function readCsvAndGetQuote($dayCount = 1 ){

        if(($handel = fopen("csv/all.csv" , "r")) !== false){
            $this->_allQuotes = fgetcsv($handel,1000000,';');
            $this->_quote = explode(',',$this->_allQuotes[$dayCount]);
            return true;
        }
        return false;

    }//end of read CSV



}//end of Class

score 0 · Answer 10 · answered May 20 '19 at 15:35

Return a php mapping array with the column of interests :

public function extractCSVDatas($file_uri) {
    $AliasToSystemPathMappingArray = [];
    if (($handle = fopen($file_uri, "r")) !== FALSE) {
      $csv = array_map('str_getcsv', file($file_uri));

      //remove header and choose columns among the list:
      foreach((array_slice($csv,1)) as $line) {
        list($id, $alias, $systemPath) = explode(';',$line[0]);
        $AliasToSystemPathMappingArray[] = [$alias, $systemPath];
      }
      fclose($handle);
    }
    return $AliasToSystemPathMappingArray;
  }

score 0 · Answer 11 · answered Dec 24 '19 at 18:11

/**
 * @return mixed[]
 */
public function csvToArray(string $delimiter, string $filename = ''): array
{
    $data = [];
    if (file_exists($filename) && is_readable($filename)) {
        $header = null;

        if (($handle = fopen($filename, 'r')) !== false) {
            while (($row = fgetcsv($handle, 1000, $delimiter)) !== false) {
                if (!$header) {
                    $header = $row;
                } else {
                    $data[] = array_combine($header, $row);
                }
            }
            fclose($handle);
        }
    }

    return $data;
}

score 0 · Answer 12 · answered Mar 18 '21 at 11:07

And why must it be simple when it can be complicated?

Joke aside, here is a quick and easy solution using the PHP function str_getcsv()

Here is an example:

function parse_csv( $filename_or_text, $delimiter=',', $enclosure='"', $linebreak="\n" )
{
    $return = array();
    
    if(false !== ($csv = (filter_var($filename_or_text, FILTER_VALIDATE_URL) ? file_get_contents($filename_or_text) : $filename_or_text)))
    {
        $csv = trim($csv);
        $csv = mb_convert_encoding($csv, 'UTF-16LE');   
        
        foreach(str_getcsv($csv, $linebreak, $enclosure) as $row){
            $col = str_getcsv($row, $delimiter, $enclosure);
            $col = array_map('trim', $col);
            $return[] = $col;
        }
    }
    else
    {
        throw new \Exception('Can not open the file.');
        $return = false;
    }
    
    return $return;
}

Imagine a situation where you need a function that works with both URL and comma delimited text. This is exactly the function that works like that. Just simply insert a CSV URL or comma separated text and it work nicely.

Crimin4L · Answer 13 · 2021-05-26T20:20:00.680

Just created a function that extracts data from .csv file or csv formatted text and then parses it for more convenient usage (presuming the first line is the columns). Works with however many rows/columns you'd like/have, just simply point the function to the file location/string and it will return the results!

function csvParse($input, $callback = false){
    $results = [];
    $raw_array = (is_file($input)) ? array_map('str_getcsv', file($input)) : array_map('str_getcsv', explode("\n", $input));
    $array = array_splice($raw_array, 1, count($raw_array));
    foreach($raw_array[0] as $c) $columns[] = $c;
    foreach($array as $key0 => $val0) 
        foreach($val0 as $key1 => $val1) 
            $results[$key0][$columns[$key1]] = $val1;
    if(is_callable($callback)) call_user_func_array($callback, array($results));
    else return $results;
}

# Either One Would Work
$input = "dir/file.csv";
$input = "name,age,occupation,city\nCrimin4L,24,Programmer,New York\nMrAwesome,20,Gamer,Los Angeles";

# Usage #1
$array = csvParse($input);
var_export($array);

# Usage #2
csvParse($input, function($array){
    var_export($array);
});

Input:

name,age,occupation,city
Crimin4L,24,Programmer,New York
MrAwesome,20,Gamer,Los Angeles

Array Output:

array (
  0 => 
  array (
    'name' => 'Crimin4L',
    'age' => '24',
    'occupation' => 'programmer',
    'city' => 'New York',
  ),
  1 => 
  array (
    'name' => 'MrAwesome',
    'age' => '20',
    'occupation' => 'gamer',
    'city' => 'Los Angeles',
  ),
)

Live Demo: https://ideone.com/Sa1aMO

How to extract data from csv file in PHP

13 Answers13

Linked

Related