• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

Read every lines in text file and extract the information!

I have text file attached , my task is read file and extract this information based the fixed length of string on each line ( Specification is attached )and take its fields and input into database.
Would anybody give me the solution or example ?
I appreciate it very much .


spec.bmp
A0001002.txt
0
dinhchung82
Asked:
dinhchung82
  • 12
  • 7
  • 5
  • +1
6 Solutions
 
FishMongerCommented:
What have you tried?

What portion of the task do you not know how to do or is giving you problems?

You tagged both PHP and Perl...which one do you really want?

Is this to be done in a web environment?

If you want to use Perl, you might want to start by reading some of the related documentation.

http://perldoc.perl.org/functions/open.html
http://perldoc.perl.org/functions/close.html
http://perldoc.perl.org/functions/substr.html

http://search.cpan.org/~timb/DBI-1.607/DBI.pm
http://search.cpan.org/~lds/CGI.pm-3.42/CGI.pm
0
 
dinhchung82Author Commented:
Hi FishMonger,
For example , i want to use PHP ( web environment)

i want to user browser the file and my code open it , read each line .
To extract the field 1( PAN) , it is position from 0 to 19 .
To extract the field 2 ( Card_name) , it is position from 20 to 50 .
.....
I must loop for each line and take to each field .
I don't know how to extract it .
If we consider if we usse each field as an element of  array , how to process it .

Thanks for your advise!


0
 
FishMongerCommented:
My knowledge of php is minimal, so I'll back out and let one of the php experts help you.

You could start by reading some of the php docs

http://us3.php.net/manual/en/function.fopen.php
http://us3.php.net/manual/en/function.fread.php
http://us3.php.net/manual/en/function.substr.php
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dinhchung82Author Commented:
Hi FishMonger,
Thanks so much for your help!
i shall try with perl first. My target is solveed it  ( perl or php is okie)
My target is parsed this file and new take each field from this file on each line.
Would you give me some example , i shall reference the code
0
 
FishMongerCommented:
Here's a Perl example that parses the file and inserts it into the db.

#!/usr/bin/perl
 
use strict;
use warnings;
use DBI;
use CGI;
 
my $cgi = CGI->new;
print $cgi->header, $cgi->start_html;
 
my $data_file = $cgi->param('dats_file');
open my $FILE, $data_file or die "can't open '$data_file' $!";
 
my $dsn = 'DBI:mysql:database_name:server_name';
my $dbh = DBI->connect($dsn, 'username', 'password', { RaiseError = 1 } )
               or die "Failed to connect to the database " . $DBI::errstr;
 
my $sth = $dbh->prepare("INSERT INTO table VALUES(?,?,?,?,?,?,?)");
 
while (my $line = <$FILE>) {
    next if $line =~ /^\s*$/;  # skip over blank lines
    
    my $field1 = substr $line, 0, 19;
    my $field2 = substr $line, 19, 30;
    my $field3 = substr $line, 49, 26;
    my $field4 = substr $line, 75, 35;
    my $field5 = substr $line, 110, 5;
    my $field6 = substr $line, 115, 4;
    my $field7 = substr $line, 119, 3;
    
    $sth->execute($field1,$field2,$field3,$field4,$field5,$field6,$field7);
}
 
$dbh->close;
 
# the rest would be outputting the results page to the user.

Open in new window

0
 
ddrudikCommented:
In PHP, here's something similar you can modify for your needs:
<pre>
<?php
$lines=file('data.txt');
foreach($lines as $line){
  preg_match('/(.{19})(.{30})(.{26})(.{35})(.{05})(.{04})(.{03})(.{01})(.{04})(.{01})(.{04})(.{03})(.{03})(.{04})(.{06})(.{08})(.{100})?(.{100})?(.{100})?/',$line,$fields);
  echo print_r($fields,true);
}
?>

Open in new window

0
 
dinhchung82Author Commented:
Hi FishMonger,
Thanks for your useful code , i try to use it .

Hi ddrudik ,
your code is so short , it seems be perfect but I use your code, the results is below :


Array
(
)
Array
(
)

0
 
FishMongerCommented:
Using a long regex like that will work, but you sacrifice readability and maintainability.

The substr function is very fast/efficient and is available in both languages and when assigning the vars, they can be named the same as the db field names, which will increase the readability and maintainability.
0
 
hieloCommented:
<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('data.txt');
foreach($lines as $line){
      foreach($fieldLengths as $fieldLength){
            $t=substr($line, 0, $fieldLength);
            echo $t . "|"; //field/column separator
            $line=substr($line,$fieldLength);
      }
      echo "\n"; //end of record/line
}
exit;
?>
</pre>
0
 
dinhchung82Author Commented:
Wow, Hielo ,
You are so intelligent , perfect solution !!!!!!!
Very useful ....
0
 
dinhchung82Author Commented:
Hi Hielo ,
I have a  question , i try to code  and use
  "$lines=file('data.txt')" and substr as FishMonger reommends but
The results have problem that doen't skip over blank lines
Mean that it doesn't count the blank --> parse he wrong text.
Why is it so ?

0
 
hieloCommented:
try:
<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('data.txt');
foreach($lines as $line){
  if(!empty($line))
  {
      foreach($fieldLengths as $fieldLength){
            $t=substr($line, 0, $fieldLength);
            echo $t . "|"; //field/column separator
            $line=substr($line,$fieldLength);
      }
      echo "\n"; //end of record/line
  }
}
exit;
?>
</pre>

Open in new window

0
 
hieloCommented:
in case the previous doesn't works as expected:
<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('data.txt');
foreach($lines as $line){
  if(preg_match('/\S/',$line))
  {
      foreach($fieldLengths as $fieldLength){
            $t=substr($line, 0, $fieldLength);
            echo $t . "|"; //field/column separator
            $line=substr($line,$fieldLength);
      }
      echo "\n"; //end of record/line
  }
}
?>
</pre>

Open in new window

0
 
dinhchung82Author Commented:
Hi all,
We are successful to extract information but i want to input it into an array and use the array to input to DB but the result is not OK , array doesn't have new value ...
My code is below:
Help me pls!



<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('C:\way4_03_25_20_16\OWS_Work\Data\Card_Prd\Embs\A0001002.133');
$cards2 = array( "", "","","","","","", "", "","","", "", "", "",
			   "","","","","");
foreach($lines as $line){
	      foreach($fieldLengths as $fieldLength){
		             $i=0;
		             $t=substr($line, 0, $fieldLength);
		             echo $t . "|"; //field/column separator   
					                              $line=substr($line,$fieldLength);
					 $cards2[$i] = $t;
					
					 $i++;
					 
					 
      }
      echo "\n"; //end of record/line
	  print_r($cards2);
	  
 
 
	  
}
 
 
exit;
?>
</pre>
			  
			  
			  
			   
			   
			   
			   
 
 
 
                
                
              
               
			    
			  
			   
			   
			   

Open in new window

0
 
dinhchung82Author Commented:
Does anyone help me ?
0
 
ddrudikCommented:
That's hielo's code, but I can try to help.  Please attach to this question a text file to test the code with.
0
 
dinhchung82Author Commented:
my code is below :

<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('C:\way4_03_25_20_16\OWS_Work\Data\Card_Prd\Embs\A0001002.133');
$cards2 = array( "", "","","","","","", "", "","","", "", "", "",
                           "","","","","");
foreach($lines as $line){
              foreach($fieldLengths as $fieldLength){
                             $i=0;
                             $t=substr($line, 0, $fieldLength);
                             echo $t . "|"; //field/column separator  
                                                                      $line=substr($line,$fieldLength);
                                         $cards2[$i] = $t;
                                       
                                         $i++;
                                         
                                         
      }
      echo "\n"; //end of record/line
          print_r($cards2);
         
 
 
         
}
 
 
exit;
?>
</pre>
                 
0
 
ddrudikCommented:
Please use "Attach File" and attach the text file to this question that you are testing with:
C:\way4_03_25_20_16\OWS_Work\Data\Card_Prd\Embs\A0001002.133
0
 
FishMongerCommented:
Since I'm not a php expert, this may need a slight tweak, but give it a try.

<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('C:\way4_03_25_20_16\OWS_Work\Data\Card_Prd\Embs\A0001002.133');
$cards2 = array();
 
foreach($lines as $line){
    $i = 0;
    foreach($fieldLengths as $fieldLength){
        cards2[] = substr($line, $i, $fieldLength);
        $i += $fieldLength;
    }
}
print_r($cards2);
 
$sql_values = join(",", $cards2);
print $sql_values;
 
?>
</pre>

Open in new window

0
 
dinhchung82Author Commented:
hi ddrudik:
this file is attached in the first quesiton, file name is A0001002.txt  ( it is the same file , i use)
0
 
dinhchung82Author Commented:
Hi FisherMonger,
it is error , i am checking your code , you also use text file from the beginning of question to test
0
 
FishMongerCommented:
Ya, I made an error.  Try this:

<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$lines=file('A0001002');
 
foreach($lines as $line){
    $cards2 = array();
    $i = 0;
 
    foreach($fieldLengths as $fieldLength){
        $cards2[] = substr($line, $i, $fieldLength);
        $i += $fieldLength;
    }
 
    print_r($cards2);
 
    $sql_values = join(",", $cards2);
    print $sql_values;
}
?>
</pre>

Open in new window

0
 
FishMongerCommented:
Oops, I forgot to reset the path to the datafile.

change:
$lines=file('A0001002');

to:
$lines=file('C:\way4_03_25_20_16\OWS_Work\Data\Card_Prd\Embs\A0001002.133');
0
 
dinhchung82Author Commented:
it worked , thanks so much , i can insert into db now :)
0
 
ddrudikCommented:
You might consider a minor revision.  This version skip empty lines and removes the LF or CRLF that might be at the end of the lines (in your file it appeared to be CRLF).  If that is not removed then one of your optional items not present will contain the CR character.
<pre>
<?php
$fieldLengths=array(19,30,26,35,5,4,3,1,4,1,4,3,3,4,6,8,100,100,100);
$file='C:\way4_03_25_20_16\OWS_Work\Data\Card_Prd\Embs\A0001002.133';
$lines=preg_split('/\r?\n/',file_get_contents($file),-1,PREG_SPLIT_NO_EMPTY);
foreach($lines as $line){
    $cards2 = array();
    $i = 0;
    foreach($fieldLengths as $fieldLength){
        $cards2[] = substr($line, $i, $fieldLength);
        $i += $fieldLength;
    }
    print_r($cards2);
    $sql_values = join(",", $cards2);
    print $sql_values;
}
?>

Open in new window

0
 
dinhchung82Author Commented:
Thanks so much for all experts ,  i study a lot from all of you and also solve my problem
0
 
ddrudikCommented:
Thanks for the question and the points.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 12
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now