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";
Advertisement
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.