Read rpt file content column by column in PHP

Question

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

I would recommend that you remove the exact details of the customers that you have provided as it may be used to identify them on the internet. — ammportal, May 22 '19 at 16:37
@ammportal I had to do this because people here demand for actual codes and actual outputs so that they can better understand. if I edit the data, it might change some alignmets. Still considering your comment, I have updated the details. — ITSagar, May 22 '19 at 17:38
Thanks for considering. What you have done is exactly what I was pointing. Dummy data is enough to understand and answer your question. — ammportal, May 22 '19 at 17:43
Without knowing the number and width of columns it will be hard. Also because of the inconsistent data. I came up with [this demo at 3v4l.org by manually setting col width](https://3v4l.org/mZI5I#output) (was about to answer, but that won't work if col width varies within files - or you'd need to adjust for each file). — bobble bubble, May 30 '19 at 13:28
[Here a version with autodetect column offsets](https://3v4l.org/loeTn) from most top heading and shifting offsets left according to most left column value. Hope it helps, but without knowing how different your data can look like it's just a guess =D — bobble bubble, May 31 '19 at 08:09
@bobblebubble Thanks for the response. This code looks working in the current sample but in another file, it doesnt work. Although you have not hard-coded the columns offsets in second code, still this code fails in the second file. I dont understand why. — ITSagar, Jun 01 '19 at 06:51
@ITSagar As mentioned, without knowing how input can look like, it's impossible =) But was interesting to play with it. Maybe you can adjust it to fit your needs yourself. — bobble bubble, Jun 01 '19 at 11:25
@bobblebubble I have added another sample for your kind reference. — ITSagar, Jun 05 '19 at 10:23
@ITSagar That's very different from the first one. Yes, my idea won't work on that and I don't have the time/ideas to adjust it. — bobble bubble, Jun 05 '19 at 10:28

yavuzkavus · Answer 1 · 2019-06-05T12:52:35.130

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.

HI, your solution doesnt work. I tried the following code replacement to print what you are fetching and it gives only one character at a time, not the column data. if( !preg_match($pattern, $str, $matches) ) return null; echo trim($matches[1][0]) . " | " . trim($matches[2][0]) . " | " . trim($matches[3][0]) . "
"; — ITSagar, Jun 05 '19 at 10:20
Sorry, not being a php expert, misunderstood $matches object from php manual. I edited answer, it should work now. instead of **$matches[1][0]**, **$matches[1]** should be used. — yavuzkavus, Jun 05 '19 at 12:56

Read rpt file content column by column in PHP

1 Answers1