ITSagar
Reputation: 703
Read rpt file content column by column in PHP
I have .rpt files and I need to read their data and then store the data in MYSQL Database. I tried the following solution:
$lines = file("aa.rpt");
echo '<table>';
foreach ($lines as $line_num) {
$str = trim(htmlspecialchars($line_num));
echo '<tr>';
echo '<td>' . getColumnText("/\s([a-zA-Z\s]+)/", $str) .'</td>';
$secondCol = getColumnText("/\s([0-9]+)/", $str);
echo '<td>' . $secondCol .'</td>';
$thirdCol = end(explode(" ", $str));
if (trim($secondCol) === $thirdCol) {
echo '<td style="text-align:right">' . str_repeat(" ", 10) .'</td>';
} else {
echo '<td style="text-align:right">' . str_repeat(" ", 10) . $thirdCol .'</td>';
}
echo '</tr>';
}
echo '</table>';
function getColumnText($pattern, $str) {
preg_match($pattern, $str, $matches);
return trim(current($matches));
}
I got this code idea from another post in stackoverflow however I still have two problems with it:
I dont know the number of columns available in my file. It may vary with files.
Every File consists of numerous records where in after some records header is repeated again. Also, header is not consistent among different files so I cannot estimate the number of lines to remove from top and between the content whenever header occurs again.
I edited the file in Notepad++ which I am pasting here as a sample, however I am not sure whether all hidden characters will be replicated as it is or not.
PSOMENAMEK PASREP 1/29
========================
Address wise list of customers for loans and advances for Sol Id : 119600 Date of Report 06-12-2018
===============================================================================
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SNO Name of the Borrower Communication Address City State Pin Customer Id Account No Mobile Number_1 Mobile Number_2 Mobile Number_3 Mobile Number_4 Pan Number SchemeCode Date of Loan Limit Purpose of Advance Bal. O/S BC Entered on
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 CHANDER SO RAMKALA AND MANSA RAM VP VPO DALAMWALA,DIST JIND JIND CITY HARYANA 126102 ABC111111 1111111111111111 1111111111 CCAKC 02-03-2009 1500000.00 RAISING CROPS 1505269.22 15-02-2017
2 ISHWAR SO JITA VPO DALAMWALA,JIND JIND CITY HARYANA 126102 ABC111111 1111111111111111 2222222222 CCAKC 02-03-2009 800000.00 RAISING CROPS 607925.78 06-02-2017
3 ROHTASH SO RAMPHAL #78/2 BLOCK NO 87 NEAR GOVT GIRLS SCHOOL,VPO SHAHPUR DISTT JIND HARYANA JIND CITY HARYANA 126125 ABC111111 1111111111111111 2222222222 CCAKC 30-10-2017 640000.00 RAISING CROPS 631757.63
4 RAM PHAL S/O SH GIANI RAM KANDEL 112 BLOCK 96 KENDELA KANDELA,JIND JIND CITY HARYANA 126125 ABC111111 1111111111111111 1111111111 2222222222 3333333333 CCAKC 28-10-2007 995000.00 RAISING CROPS 998043.05 01-09-2017
5 SAT NARAIN S/O SH JHANDU RAM SRI 1156 13 BHUNA ROAD OLD BAJRANG ROAD,TOHANA RURAL DISTT FATEHABAD HARYANA TOHANA DISTT-F HARYANA 125120 ABC111111 1111111111111111 1111111111 3333333333 2222222222 CCAKC 28-10-2007 600000.00 RAISING CROPS 354523.05 21-02-2017
6 RAJESH SO DARIYA SINGH 146 V RAJ PANA PO SHAHPUR TEH JIND,JIND HARYANA JIND CITY HARYANA 126125 ABC111111 1111111111111111 1111111111 CCAKC 29-06-2018 550000.00 RAISING CROPS 245288.37
7 JAI BHAGWAN SO ATAR SINGH 1585/7 JAWAHAR NAGAR,PATIALA CHOWK JIND CITY HARYANA 126102 ABC111111 1111111111111111 3333333333 CCOTH 06-03-2017 40000.00 WORKING CAPITAL F 39636.00
PSOMENAMEK PSTREP 1/29
========================
Address wise list of customers for loans and advances for Sol Id : 119600 Date of Report 06-12-2018
===============================================================================
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SNO Name of the Borrower Communication Address City State Pin Customer Id Account No Mobile Number_1 Mobile Number_2 Mobile Number_3 Mobile Number_4 Pan Number SchemeCode Date of Loan Limit Purpose of Advance Bal. O/S BC Entered on
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
172 RAJKUMAR SO JAGTU V-JIWANPUR PO-DALAMWALA TEH-JIND,JIND HARYANA JIND CITY HARYANA 126110 ABC111111 1111111111111111 1111111111 CCAKC 28-10-2007 300000.00 RAISING CROPS 294446.29 02-05-2016
173 RAM MEHAR S/O MOJI RAM V-JIWANPUR PO-DALAMWALA TEH-JIND,JIND HARYANA JIND CITY HARYANA 126110 ABC111111 1111111111111111 2222222222 AAAAAAAAAA CCAKC 07-10-2010 400000.00 RAISING CROPS 401412.40 04-10-2015
174 RAMKARAN SO SHIVDHAN V-JIWANPUR PO-DALAMWALA TEH-JIND,JIND HARYANA JIND CITY HARYANA 126110 ABC111111 1111111111111111 1111111111 CCAKC 28-10-2007 550000.00 RAISING CROPS 417816.51 06-01-2017
175 RISHI LAL SO SHIVDHAN V-JIWANPUR PO-DALAMWALA TEH-JIND,JIND HARYANA JIND CITY HARYANA 126110 ABC111111 1111111111111111 CCAKC 28-10-2007 995000.00 RAISING CROPS 997902.96 14-02-2017
176 SATYAWAN URF SATNARAIN S/O JAGE RAM V-JIWANPUR PO-DALAMWALA TEH-JIND,JIND HARYANA JIND CITY HARYANA 126110 ABC111111 1111111111111111 2222222222 CCAKC 17-11-2010 300000.00 RAISING CROPS UND 308424.76 09-12-2014
177 SHYAM S/O SATYA NARAYAN V-JIWANPUR PO-DALAMWALA TEH-JIND,JIND HARYANA JIND CITY HARYANA 126125 ABC111111 1111111111111111 1111111111 CCAKC 16-05-2017 301000.00 RAISING CROPS 301836.88
178 SHAMSHER SINGH SO BIRU RAM V-SIRIRAG PO-SHAHPUR TEH-JIND,JIND HARYANA JIND CITY HARYANA 126125 ABC111111 1111111111111111 3333333333 CCKGS 17-12-2007 600000.00 RAISING CROPS 603381.00 26-06-2018
179 JAI BHAGWAN S/O SH BARU RAM SRI V-SRI RAG KHERA PO- SHAHPUR TEH-JIND,JIND HARYANA JIND CITY HARYANA 126125 ABC111111 1111111111111111 4444444444 AAAAAAAAAA CCAKC 28-10-2007 600000.00 RAISING CROPS 663130.17 26-06-2018
OTHER SAMPLE
BANK NAME Report Date: 06-12-2018 Page No. : 1
Daily Outstanding Report
CIRCLE : 119600 - KANDELA Amount in Account Currency
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S No. A/c No. Name of the Account Account Dt of Sanction Limit Value of Drawing Power Balance IRREGULARITY Interest
Cur. Sanction Security Outstanding Rate Clas
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Scheme Code : CCAKC
------------------------------------------
1 1196008800001006 CHHAJJU RAM SO SH HIRA LAL INR 28-01-2014 3,00,000.00 5,00,000.00 3,00,000.00 2,88,588.31 Dr 0.00 7.00 STANDARD AS
2 1196008800001158 RAMKARAN SO SHIVDHAN INR 03-10-2016 5,50,000.00 11,00,000.00 5,50,000.00 4,17,816.51 Dr 0.00 11.30 STANDARD AS
3 1196008800001219 BALJIT SINGH SO SH RAM DHAN INR 12-01-2015 18,00,000.00 30,00,000.00 18,00,000.00 17,99,175.06 Dr 0.00 11.30 STANDARD AS
4 1196008800001325 RAM NIWAS SO RAM KISHAN INR 09-06-2014 30,000.00 1,50,000.00 30,000.00 30,402.62 Dr 402.62 7.00 STANDARD AS
5 1196008800001352 SAJANPAL S/O SAMAT INR 18-11-2015 1,50,000.00 3,00,000.00 1,50,000.00 1,47,677.50 Dr 0.00 11.25 STANDARD AS
6 1196008800001361 MUNSHI RAM SO SH JAI RAM SR INR 19-06-2015 80,000.00 80,000.00 80,000.00 1,585.63 Dr 0.00 11.25 STANDARD AS
7 1196008800001440 RICHH PAL SO RANPAT INR 20-10-2018 2,50,000.00 4,00,000.00 2,50,000.00 2,49,819.36 Dr 0.00 7.00 STANDARD AS
8 1196008800001477 RAM DIYA SO HARI RAM INR 19-05-2015 80,000.00 5,00,000.00 80,000.00 79,671.79 Dr 0.00 14.00 STANDARD AS
9 1196008800001486 SIYA RAM SO JOGI RAM INR 01-05-2017 9,95,000.00 13,00,000.00 9,95,000.00 5,03,155.07 Dr 0.00 11.30 STANDARD AS
10 1196008800001547 GANGA BISAN S/O RAI SINGH INR 27-03-2018 1,85,000.00 2,50,000.00 1,85,000.00 1,84,895.47 Dr 0.00 7.00 STANDARD AS
11 1196008800001556 SUNHERA SINGH SO MANGE RAM INR 06-01-2014 4,00,000.00 8,00,000.00 4,00,000.00 1,79,359.39 Dr 0.00 7.00 STANDARD AS
Upvotes: 3
Views: 906
Answers (1)
yavuzkavus
Reputation: 1266
What about something like this.
// SNO Name Addr City State Pin CustId AccNo Mobiles Pan Scheme DateLoan Limit PrAdv BalOs BCEntered
$pattern = '/(\d+\s*)(.{37})(.{80})(.{15})(.{20})(.{20})(.{15})(.{21})(.{17})(.{17})(.{17})(.{17})(.{12})(.{12})(\d\d-\d\d-\d\d\d\d\s*)(.{15})(.{26})(.{12})((?:\d\d-\d\d-\d\d\d\d)?)/';
function readAccount($str) {
if( !preg_match($pattern, $str, $matches) ) return null;
return array(
"sno"=>trim($matches[1]),
"name"=>trim($matches[2]),
"addr"=>trim($matches[3]),
//...
);
}
$lines = file("aa.rpt");
foreach ($lines as $line_num) {
$str = trim(htmlspecialchars($line_num));
$account = readAccount($str);
if( $account)
saveToDb($account);
}
If currently processing line is an invalid data row, readCustomer will not match and return null. Otherwise, method will read info groups one by one, and return account object. In the loop, if returned value is a valid account, you can save it to db.
Upvotes: 2
Related Questions
- scraping text from a file to view - with non standard format - php
- Reading a file and extracting data using a regex in PHP
- Using a PHP variable in an RLIKE regex
- How to fix line formats when reading a file
- PHP wont recognise double line feed
- Parse Fixed Width File PHP Regular Expression
- PHP - Reading ftp text files and extract required data
- RexExp in PHP for this pattern
- php/dos : How do you parse a regedit export file?
- problem with regxp