Parse a text file?
Moderator: General Moderators
Parse a text file?
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.
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.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Parse a text file?
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?
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.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Parse a text file?
Just checking.
Read the file into an array, then explode it on the pipe (|) and build the query dynamically.
Read the file into an array, then explode it on the pipe (|) and build the query dynamically.
Re: Parse a text file?
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?jaoudestudios wrote:Just checking.
Read the file into an array, then explode it on the pipe (|) and build the query dynamically.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Parse a text file?
Try file. It will read the file into an array.
Documentation...http://uk2.php.net/file
Documentation...http://uk2.php.net/file
Re: Parse a text 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)?jaoudestudios wrote:Try file. It will read the file into an array.
Documentation...http://uk2.php.net/file
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Parse a text file?
If you do something like this....
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?
Code: Select all
$data = file('FILENAME');
echo $data[0]; // this will be the header row
Re: Parse a text file?
I see now. Yes, performance is very critical for us because the script will be used in our daily database data insertion.jaoudestudios wrote:If you do something like this....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?Code: Select all
$data = file('FILENAME'); echo $data[0]; // this will be the header row
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Parse a text file?
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?
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?
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?
Can you please expend the code into parsing the file with expode()?
Re: Parse a text file?
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?
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
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