How to Extract Data from CSV File in PHP

How to extract data from csv file in PHP

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

Extract information from some lines of a CSV file with PHP

Assuming that your format never changes (including for the invalid lines that you wish to ignore), the below would work.

Note: I have not tested this code so use this as a pointer and make adjustments as required.

$file = fopen("mails.csv","rb");

while(feof($file) == false){
    $contents = fgets($file);
    if (substr($contents,0,1) != "#"){
        $val = explode(",", $contents);
        echo $val[0]. "<br />";
    }
}

fclose($file);

Extract column from csv data in PHP

If this a comma delimited string, you could use str_getcsv() in this case:

// load the string and explode, then apply str_getcsv
$data = array_map('str_getcsv', explode("\n", $request->getResponseBody()));
array_shift($data); // remove the header
$fourth_column = array();
foreach($data as $line) {
    if(!empty($line[3])) {
        $fourth_column[] = $line[3]; // indices start at zero
    }   
}

How to parse a CSV file using PHP

Just use the function for parsing a CSV file

http://php.net/manual/en/function.fgetcsv.php

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

PHP Arrays: Extract from CSV

In the example given in the php docs for CsvImporter it's changing the default delimiter to \t. Try add ',' as the 3rd param for the class:

$importer = new CsvImporter($csv,true, ',');

Hope this helps!

How to extract data from CSV with PHP

Split the string into an array, then grab a random index from that array:

$lines = explode("\n", $csv);
$item = $lines[array_rand($lines)];

How to extract in a text file only the needed data and save it to csv using php

Does the source data contain a heading? According to your sample post the file is comma delimited, is that right? If yes, then I would use PHP to read the sample file and create an array. Something like this:

$file = fopen('sample_file.csv', 'r');
$fields = array();
if($file)
{
 while(($data=fgetcsv($file, ',')) !== false)
 {
  if(empty($fields))
  {
   $fields = $data;
   continue;
  }
  $output[] = array_combine($fields, $data);
 }
 fclose($file);

// After the above code runs you will have an array named $output that contains all the data from the source file.
// Then you just use a loop to read the array and extract the columns you want and write them to a file.
// The loop would look something like this:

 foreach($output as $main)
 {
  echo("$main['Heading1'], $main['Heading2'], $main['Heading3']");
 }
}

Instead of an echo command you would need to write code to write the data into a file.

EDIT - New program:

Here is a new program to try. Since you agreed with me that there were 7 columns this version will work. However, PHP date and time usually have a space between the date and time and not a tab. If there is a space and not a tab then this version will have to be tweaked a little.

if(!$myfile = fopen("data.txt", "r"))
{
 echo("Unable to open data file!");
 exit;
}
while(!feof($myfile))
{
 $line = explode("\t", fgets($myfile));
 $write[] = $line[2] . "\t" . $line[5] . "\t" . $line[6];
}
fclose($myfile);
if(!$handle = fopen("temp.csv", "w"))
{
 echo("Can't open file for writing!");
 exit;
}
foreach($write as $line)
{
 if(fwrite($handle, $line) === FALSE)
 {
  echo("Can't write to file!");
  exit;
 }
}
fclose($handle);

Enjoy!

2nd EDIT:

Ok, just replace this line of code:

 $write[] = $line[2] . "\t" . $line[5] . "\t" . $line[6];

with these 2 lines of code:

 $dt = explode(" ", $line[5]);
 $write[] = $line[2] . "\t" . $dt[0] . "\t" . $dt[1];

EDIT - Final version

Ok, based on the fact that there is 1 tab between each column except for 3 tabs between 01408156 and 33 this works for me.

if(!$myfile = fopen("data.txt", "r"))
{
 echo("Unable to open data file!");
 exit;
}
// The below line is to read the headers and discard
$line = feof($myfile);
while(!feof($myfile))
{
 $line = explode("\t", fgets($myfile));
 $dt = explode(" ", $line[7]);
 $write[] = $line[2] . "\t" . $dt[0] . "\t" . $dt[1];
}
fclose($myfile);
if(!$handle = fopen("temp.csv", "w"))
{
 echo("Can't open file for writing!");
 exit;
}
foreach($write as $line)
{
 if(fwrite($handle, $line) === FALSE)
 {
  echo("Can't write to file!");
  exit;
 }
}
fclose($handle);

Let me know how it goes.

Last EDIT

I added headers to my sample and found I made a mistake. This line of code:

$line = feof($myfile);

needs to be changed to this:

$line = fgets($myfile);

Unless, you want the 3 headers in your output file

New Final Version

Here is a version that doesn't use array to build the output data. This version will read the input and write the output all at once.

if(!$input = fopen("data.txt", "r"))
{
 echo("Unable to open data file!");
 exit;
}
if(!$output = fopen("temp.csv", "w"))
{
 echo("Can't open file for writing!");
 exit;
}
// The below line is to read the headers and discard
$line = fgets($input);
while(!feof($input))
{
 $line = explode("\t", fgets($input));
 $dt = explode(" ", $line[7]);
 if(fwrite($output, $line[2] . "\t" . $dt[0] . "\t" . $dt[1]) === FALSE)
 {
  echo("Can't write to file!");
  exit;
 }
}
fclose($input);
fclose($output);

Let me know how it works.

---

Related Topics