Parse a text file?

sfresher

I was trying to parse a text file and then insert the data into MySQL.

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.

farius

make sure to put quotes in the values section (' '). But yes INSERT INTO tablename (XXX,XXX) VALUES ('XXX', 'XXXXX') is correct

sfresher

farius;10899268 wrote:
make sure to put quotes in the values section (' '). But yes INSERT INTO tablename (XXX,XXX) VALUES ('XXX', 'XXXXX') is correct

Thanks! I have updated my post. Can you also show me how to use php script to parse the text file?

wilku

Read file into an array using [man]file[/man]
Prepare start of your SQL:
```
$sql = "INSERT INTO table_name";
```
prepare column headers using [man]explode[/man] and [man]implode[/man]
```
$sql .=" (".implode(",", explode("|",$file_array[0])).")";
```
In similar manner, in the for loop using [man]count[/man] on the file array, add values to your sql

Weedpacket

You can read the file using [man]fgetcsv[/man]; don't forget to (a) tell it that you're using a '|' as the field delimiter, and (b), don't try and insert the first row 🙂.

Using the PDO interface would make this nice and tidy:

$insert = $db_handle->prepare("INSERT INTO table_name (Date, Time, ...) VALUES (:date, :time, ....)");
$insert->bindParam(':date', $date);
$insert->bindParam(':time', $time);
//...

// Open the file and start reading. Inside the loop....
list($date, $time, ...) = fgetcsv($file_handle, 0, '|');
$insert->execute();

wilku

@
It has one disadvantage though (or advantage, depending if you look for flexibility or security). In your example the column names are set in the code, not read from file.
In mine 3. takes care of this, preparing headers for the columns from the first line in file and 4. is suppose to add VALUES (...,...),(...,...)...

This approach lets user prepare the file in any column order, if only the column headers match column name in the database - it is a security threat but if properly implemented, is very flexible.

Other thing, and I'm not sure here, won't one Insert statement with multiple values be faster then sending separate Insert for every line?

Weedpacket

wilku wrote:
In your example the column names are set in the code, not read from file.

As you say, it's an example: none of the hardwired bits needed to be. But the columns still have to be in the database table anyway.