Parse QIF File

[verdrm]

How can I parse this QIF file using PHP? I would like each line to be stored in a variable for each "set" of charges (delimiter is the record separator ^). Thanks!

 

!Type:Bank

D03/03/10

T-379.00

PCITY OF SPRINGFIELD

^

D03/04/10

T-20.28

PYOUR LOCAL SUPERMARKET

^

D03/03/10

T-421.35

PSPRINGFIELD WATER UTILITY

^

 

[silkfire]

Pretty easy:

 

$qifs = explode('^', file_get_contents('qif.qif'));

[Psycho]

If it is simply a text file, then read it line-by-line and parse it accordingly. I have no clue what each "piece" of information is in that data and what you need and don't need. Plus, I can make some assumptions on what formats the data will always take based upon the three records you have presented, but I would only be guessing. In the above each record appears to have a Type, a Date, a Price, and some type of description. But, I don't know whether the "D" always precedes the date and is static or if it is some type of code that can change and has significance. Same thing with the "T" that precedes what appears to be the price. It is always a "T" or is that a piece of data? Lastly, the same goes for the descriptions which are (at least in the above examples) preceded by a "P". Again, is this static or a piece of data?

[verdrm]

What I mean is that I would like to insert each line into a MySQL database. The charges are separated by the '^' character.

 

I would like to parse the file so that I can save each line into a variable. Ex:

 

$date = "D03/04/10";

$amount = "T-20.28";

$payee = "PYOUR LOCAL SUPERMARKET";

 

How can I accomplish this?

 

 

[silkfire]

Oh, why didn't you say that in the first place

 

$charges = array_slice(explode("\n", file_get_contents('file.qif')), 1);                 // Remove the first row
$charges = array_chunk($charges, 4);                                                     // Group 4 rows together

foreach($charges as $charge)
   mysql_query("INSERT INTO table (date, amount, payee) VALUES('$charge[0]', '$charge[1]', '$charge[2]')");

[Psycho]

Do NOT run queries in loops!!! It is horribly inefficient. Parse all the data then run ONE INSERT query. Plus, you need to escape teh input to prevent SQL Injection errors and I would expect you want at least some validation of the data. I'll post some sample code in a few minutes.

[Psycho]

OK, this is only sample code. YOU should add more validation/formatting of the data. For example, I would think you would want to store the date as an actual date value and not a string.

//Set input file
$file = 'somefile.qif';
//Read file into array
$lines = file($file);

//Set variables for processing loop
$type = false;
$insert_records = array();
$record = array();

//Process the data
foreach($lines as $line)
{
    if($line=="^")
    {
        $record = array();
    }
    elseif(preg_match("#^!Type:(.*)$#", $line, $match))
    {
        $type = mysql_real_escape_string2(trim($match[1]));
        $record = array();
    }
    else
    {
        switch(substr($line, 0, 1))
        {
            case 'D':
                $record['date']   = mysql_real_escape_string2(trim(substr($line, 1)));
                break;
            case 'T':
                $record['amount'] = mysql_real_escape_string2(trim(substr($line, 1)));
                break;
            case 'P':
                $record['descr']  = mysql_real_escape_string2(trim(substr($line, 1)));
                break;
        }
    }

    if(count($record)==3 && $type!==false)
    {
        $insert_records[] = "('{$type}', '{$record['date']}', '{$record['amount']}', '{$record['descr']}')";
        $record = array();
    }
}

//Create one INSERT query for all records
$insert_query = "INSERT INTO `transactions`
                     (`type`, `date`, `amount`, `description`)
                 VALUES " . implode(", ", $insert_records);

 

Using that code and your sample data, you would get the following query:

INSERT INTO `transactions`
    (`type`, `date`, `amount`, `description`)
VALUES
    ('Bank', '03/03/10', '-379.00', 'CITY OF SPRINGFIELD'),
    ('Bank', '03/04/10', '-20.28', 'YOUR LOCAL SUPERMARKET'),
    ('Bank', '03/03/10', '-421.35', 'SPRINGFIELD WATER UTILITY')

[silkfire]

Wow you do like complicating things, mj....

[Psycho]

Wow you do like complicating things, mj....

 

Complicating, no. Working, yes. The code you provided was horribly inefficient by trying to INSERT records in a loop. Database transactions are one of the most "expensive" things with respect to performance/scalability.

 

Plus, that code would easily break due to unexpected characters in the data and would not handle incomplete/invalid records. The code I provided ensure that records are 'complete' (i.e. have a value for all the fields) rather than simply assuming the lines will always be in the same order and format. For example, what if there was an "optional" field

^
D03/04/10
T-20.28
PYOUR LOCAL SUPERMARKET
OSome Optional Value
^

The code originally provided would be inserting values in the the incorrect fields.

 

If you take a half-assed approach to something like this you will spend countless hours "repairing" things later on. You should never assume that the data you receive will be the data you expect. Doesn't matter if it is data coming from a form post or reading a data file.

 

The above took me about 5-10 minutes and it is fairly fool-proof. But, I would have to know more about the input data to be sure. That doesn't sound like too much work for something that I would have high confidence in.