?
Solved

Read every lines in  text file and extract the information!

Posted on 2008-11-01
27
Medium Priority
?
650 Views
Last Modified: 2011-10-19
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
Comment
Question by:dinhchung82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 5
  • +1
27 Comments
 
LVL 28

Expert Comment

by:FishMonger
ID: 22856994
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22857070
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
 
LVL 28

Expert Comment

by:FishMonger
ID: 22857223
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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 5

Author Comment

by:dinhchung82
ID: 22857264
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
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 600 total points
ID: 22857482
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
 
LVL 27

Expert Comment

by:ddrudik
ID: 22857512
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22857537
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
 
LVL 28

Expert Comment

by:FishMonger
ID: 22857595
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
 
LVL 82

Accepted Solution

by:
hielo earned 1000 total points
ID: 22857742
<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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22857780
Wow, Hielo ,
You are so intelligent , perfect solution !!!!!!!
Very useful ....
0
 
LVL 5

Author Comment

by:dinhchung82
ID: 22857794
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
 
LVL 82

Assisted Solution

by:hielo
hielo earned 1000 total points
ID: 22858499
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
 
LVL 82

Assisted Solution

by:hielo
hielo earned 1000 total points
ID: 22858558
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22860640
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22861791
Does anyone help me ?
0
 
LVL 27

Expert Comment

by:ddrudik
ID: 22861842
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22861856
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
 
LVL 27

Expert Comment

by:ddrudik
ID: 22862039
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
 
LVL 28

Expert Comment

by:FishMonger
ID: 22862126
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22862189
hi ddrudik:
this file is attached in the first quesiton, file name is A0001002.txt  ( it is the same file , i use)
0
 
LVL 5

Author Comment

by:dinhchung82
ID: 22862221
Hi FisherMonger,
it is error , i am checking your code , you also use text file from the beginning of question to test
0
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 600 total points
ID: 22862261
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
 
LVL 28

Expert Comment

by:FishMonger
ID: 22862270
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 22862272
it worked , thanks so much , i can insert into db now :)
0
 
LVL 27

Assisted Solution

by:ddrudik
ddrudik earned 400 total points
ID: 22862463
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
 
LVL 5

Author Closing Comment

by:dinhchung82
ID: 31512314
Thanks so much for all experts ,  i study a lot from all of you and also solve my problem
0
 
LVL 27

Expert Comment

by:ddrudik
ID: 22863929
Thanks for the question and the points.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question