Page 1 of 2
Parse a text file?
Posted: Tue Jan 13, 2009 1:41 pm
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.
Re: Parse a text file?
Posted: Tue Jan 13, 2009 2:25 pm
by jaoudestudios
Can you get a version of the file that is separated by commas (,). That way it would be a CSV.
Re: Parse a text file?
Posted: Tue Jan 13, 2009 2:32 pm
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.
Re: Parse a text file?
Posted: Tue Jan 13, 2009 2:53 pm
by jaoudestudios
Just checking.
Read the file into an array, then explode it on the pipe (|) and build the query dynamically.
Re: Parse a text file?
Posted: Tue Jan 13, 2009 3:02 pm
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?
Re: Parse a text file?
Posted: Tue Jan 13, 2009 3:05 pm
by jaoudestudios
Try
file. It will read the file into an array.
Documentation...
http://uk2.php.net/file
Re: Parse a text file?
Posted: Tue Jan 13, 2009 3:18 pm
by sfresher
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)?
Re: Parse a text file?
Posted: Tue Jan 13, 2009 3:24 pm
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?
Re: Parse a text file?
Posted: Tue Jan 13, 2009 4:10 pm
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.
Re: Parse a text file?
Posted: Tue Jan 13, 2009 4:34 pm
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.
Re: Parse a text file?
Posted: Thu Jan 15, 2009 1:16 pm
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?
Re: Parse a text file?
Posted: Thu Jan 15, 2009 1:24 pm
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;
}
Re: Parse a text file?
Posted: Thu Jan 15, 2009 1:41 pm
by sfresher
Can you please expend the code into parsing the file with expode()?
Re: Parse a text file?
Posted: Thu Jan 15, 2009 1:55 pm
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();
?>
Re: Parse a text file?
Posted: Thu Jan 15, 2009 2:01 pm
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