Parse a text file?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Parse a text file?

Post by sfresher »

I was trying to parse a text file and then insert the data into MySQL. I am new to php and I have checked php manual and but still cannot generate some useful code.

The file contains a line of header records as follows:

Date|Time|First name|Last name|Notes|Result
20081210|231053|Jack|Hendreson|Special action|Pass
20090115|120925|Mary|Whitichi||Fail

So a result after parse text, I can insert the data with the something like:

INSERT INTO tablename (Date, Time, First name, Last name, Notes, Result) VALUES (20081210, 231053, Jack, Henderson, Special action, Pass),
(20090115, 120925, Mary, Whititchi, NULL, Fail)

p.s. Not sure if was using the correct SQL command. Can someone help me with the php script?

Thanks in advance.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Parse a text file?

Post by jaoudestudios »

Can you get a version of the file that is separated by commas (,). That way it would be a CSV.
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

We can't use CSV because using "|" delimited text file is a team agreement.
jaoudestudios wrote:Can you get a version of the file that is separated by commas (,). That way it would be a CSV.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Parse a text file?

Post by jaoudestudios »

Just checking.

Read the file into an array, then explode it on the pipe (|) and build the query dynamically.
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

jaoudestudios wrote:Just checking.

Read the file into an array, then explode it on the pipe (|) and build the query dynamically.
I am really having difficulties reading the file. I tried use fgets() but seems doesn't work for me. Can someone provide the code to work me through?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Parse a text file?

Post by jaoudestudios »

Try file. It will read the file into an array.
Documentation...http://uk2.php.net/file
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

jaoudestudios wrote:Try file. It will read the file into an array.
Documentation...http://uk2.php.net/file
Thanks! But if I use file to read the entire file into a single array, how do I read the first line only (header record)?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Parse a text file?

Post by jaoudestudios »

If you do something like this....

Code: Select all

 
$data = file('FILENAME');
echo $data[0]; // this will be the header row
 
Once in an array you can loop through each row and create the insert query. Is this going to be a frequently used script, does efficiency need to be considered or is it a one off?
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

jaoudestudios wrote:If you do something like this....

Code: Select all

 
$data = file('FILENAME');
echo $data[0]; // this will be the header row
 
Once in an array you can loop through each row and create the insert query. Is this going to be a frequently used script, does efficiency need to be considered or is it a one off?
I see now. Yes, performance is very critical for us because the script will be used in our daily database data insertion.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Parse a text file?

Post by jaoudestudios »

Well I suggest get it working the way you want, then we can improve the efficiency afterwards and bench mark the results - I have a few ideas of how to improve the performance.
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

I feel I am really kind of dumb in coding this in a php script. And I do learn things from others sample script. Can someone please provide a complete code so I can start learning from there?
User avatar
nor0101
Forum Commoner
Posts: 53
Joined: Thu Jan 15, 2009 12:06 pm
Location: Wisconsin

Re: Parse a text file?

Post by nor0101 »

This should get you as far as reading the file, then hopefully you'll be able to explode() on the newline ("\n") to get an array of records, and explode() again on the pipe to separate each record into fields.

Code: Select all

 
        function readFile($input_file_path) {
            $input_handle = fopen($input_file_path, "r") or die("Error: Unable to open file '".$input_file_path."'.\n");
            if (filesize($input_file_path) > 0) { // checking for empty file
                if (!$raw_data = trim(fread($input_handle, filesize($input_file_path)))) {
                    die("Error: Unable to read file.\n");
                }
                fclose($input_handle);
            }
            else {
                die ("Error: 0 byte file provided.\n");
            }
            return $raw_data;
        }
 
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

Can you please expend the code into parsing the file with expode()?
User avatar
nor0101
Forum Commoner
Posts: 53
Joined: Thu Jan 15, 2009 12:06 pm
Location: Wisconsin

Re: Parse a text file?

Post by nor0101 »

Heh... what the hell. Try the code below. I haven't tested it but should get you going at least.

Code: Select all

 
<?php
 
    // set this constant to 'true' to toggle detailed output
    define("DEBUG", false);
 
    // location of the pipe separated values file   
    static $psv = "./data.psv";
 
    // mysql config
    static $mysql_server =      "your-server.com";
    static $mysql_database =    "your-database-name";
    static $mysql_user =        "your-username";
    static $mysql_password =    "your-password";
    static $mysql_table =       "your-table-name";
 
    // variable declaration for mysql aux. fxn.s:
    $mysql_connect;
 
    function make_db_cxn() {
        global $mysql_connect, $mysql_server, $mysql_database, $mysql_user, $mysql_password;
 
        /*  ERROR 1 */
        DEBUG ? $mysql_connect = mysql_connect("$mysql_server", "$mysql_user", "$mysql_password") or die("<br /><br />Error 1: Unable to connect to DB Server.<br />".mysql_error()) : $mysql_connect = mysql_connect("$mysql_server", "$mysql_database", "$mysql_password") or die("<br /><br />Error 1: Unable to connect to DB Server.<br />");
 
        /*  ERROR 2 */
        DEBUG ? $selected = mysql_select_db("$mysql_database", $mysql_connect) or die("<br /><br />Error 2: Unable to select the database.<br />".mysql_error()) : $selected = mysql_select_db("$mysql_database", $mysql_connect) or die("<br /><br />Error 2: Unable to select the database.<br />");
    }
 
    function close_cxn() {
        global $mysql_connect;
        mysql_close($mysql_connect);
    }
 
 
    $q = "INSERT INTO $mysql_table (Date, Time, First name, Last name, Notes, Result) VALUES ";
 
    $psv_recs = file($psv, FILE_IGNORE_NEW_LINES);
    array_shift($psv_recs);
    foreach($psv_recs as $raw_rec) {
        $rec = explode('|', $raw_rec);
        $q .= "('$rec[0]','$rec[1]','$rec[2]','$rec[3]','$rec[4]'),";
    }
    $q = rtrim($q, ",");
    $q .= ";";
    
    make_db_cxn();
    mysql_query($q);
    close_cxn();
    
?>
 
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Re: Parse a text file?

Post by sfresher »

Thanks! But I am afraid of that your code does not work out for my case because:

1. The text data file may come with more or less data fields. e.g.

Date|Time|First name|Last name|Notes|Result

Date|First name|Notes|Extra field


2. The text data file may come with different order of the fields. e.g.

Date|Time|First name|Last name|Notes|Result

Time|Date|Last name|First name|Result|Notes
Post Reply