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

I was given a text file that contains more than a hundred of rows with 6 columns but the data that i need are on column 3 and 6 and save it to a csv file. The text file contains like this:

00001   1   01408156                33  0   2014/11/17  15:21:18
00002   1   00000007                33  0   2014/11/17  15:39:59
00003   1   01409179                33  0   2014/11/18  07:39:45
00004   1   01410352                33  0   2014/11/18  07:40:07
00005   1   01404048                33  0   2014/11/18  07:40:12
00006   1   01411402                33  0   2014/11/18  07:40:30
00007   1   01409227                33  0   2014/11/18  07:40:34
00008   1   01410323                33  0   2014/11/18  07:40:43
00009   1   01409242                33  0   2014/11/18  07:40:46
00010   1   01010042                33  0   2014/11/18  07:40:49
00011   1   01409192                33  0   2014/11/18  07:40:53
00012   1   01409192                33  0   2014/11/18  07:40:56
00013   1   01409171                33  0   2014/11/18  07:41:21
00014   1   01403005                33  0   2014/11/18  07:41:34
00015   1   01010205                33  0   2014/11/18  07:42:00
00016   1   01411465                33  0   2014/11/18  07:42:07
00017   1   01411381                33  0   2014/11/18  07:42:13
00018   1   01403018                33  0   2014/11/18  07:42:20
00019   1   01411447                33  0   2014/11/18  07:42:24
00020   1   01410308                33  0   2014/11/18  07:42:31
00021   1   01411381                33  0   2014/11/18  07:42:36
00022   1   01411427                33  0   2014/11/18  07:43:15
00023   1   01404029                33  0   2014/11/18  07:43:28
00024   1   01411452                33  0   2014/11/18  07:43:58
00025   1   01404061                33  0   2014/11/18  07:44:07
00026   1   01409278                33  0   2014/11/18  07:44:11
00027   1   01409266                33  0   2014/11/18  07:44:17
00028   1   01404113                33  0   2014/11/18  07:44:21

By the way the text file came from a biometrics thumb print machine that records the following data above. Any help would be appreciated. Thanks in advance.

EDIT:

I tried this code but when i try to extract the necessary data i am getting an offset error:

if (file_exists($myFile))
{
 $fileContent = file($myFile);

 foreach($fileContent as $line_num => $line) {
 { 
   $data = explode(", ", $line);

   $fileUsername[] = trim($data[0]);
   $filePassword[] = trim($data[1]);
 }
}

This is the code that i successfully extract the data that i need but i want to explode the last column to separate date from time:

$file=fopen("D:/Documents/My Documents/GLG_001_12215C.txt","r");

$i=0;

$line=array();
while(($data=fgetcsv($file,1000,"t"))!==FALSE){
if($i>0){
$data[0]="";
$data[1]="";
$data[3]="";
$data[4]="";
$data[5]="";

unset($data[0],$data[1],$data[3],$data[4],$data[5]);
$line[]=$data;
} 
$i++;
//print_r(array_values($data));

}

fclose($file);
//array_shift($line);

$converted=fopen("C:/xampp/htdocs/HRMS/temprec.txt","w");

foreach($line as $li){
fputcsv($converted,$li);
//print_r(array_values($li));
}
fclose($converted);

echo "import successful";

Answer

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.