Parse text file and insert into database

[Anon]

Some code that I was working on that I thought the community might find useful.

Basically it reads a source text file delimited by ':' (colons), it reads a line and explodes the line into an array. The unfortunate thing is that most of the lines end with a '\n' (new line character). The textfile looks like this:
field1:field2:field3'\n'
and i want to insert into a table that is structured as
col1: $date
col2: $time
col3: field1
col4: field2
col5: field3
col6: blank (for future comments)
col7: auto increment field

so the INSERT STATEMENT should look something like

INSERT into tableName VALUES('$date', '$time', 'field1', 'field2', field3', '', '');

so the following code snippet accomplishes this.

you can store the insert statements into another array or execute them then and there.

enjoy!

jm ^_

while (!feof($sourcefd)) {
$fstring = fgets($sourcefd, 1024);
$sLine = explode(":", $fstring);
$dLine = "'$dateSub','$timeSub',";
for ($i=0; $i<3; $i++) {
if (($i+1) % 3 == 0) {
$pos = strpos($sLine[$i], "\n");
if ($pos === false) {
$dLine = $dLine . "'$sLine[$i]'";
}
else {
$tmp = substr($sLine[$i], 0, $pos-1);
$dLine = $dLine . "'$tmp'";
}
}
else {
$pos = strpos($sLine[$i], "\n");
if ($pos === false) {
$dLine = $dLine . "'$sLine[$i]',";
}
else {
$sLine[$i] = substr($sLine[$i], 0, $pos);
$dLine = $dLine . "'$sLine[$i]',";
}
}
}

$sql = "INSERT INTO logFiles VALUES(" . "$dLine" . ",'','')";
print "<b>$sql</b><br>\n";
// Clear the next set of lines
$dLine = "";
$sql = "";

}

[toma42]

Try this instead:
foreach (file("source.txt") as $line) {
$db -> query("insert into logFiles VALUES ('" . trim(str_replace(':', "','", $line)) . "', '', '')");
}

[toma42]

k, there's a missing ) in the foreach() but it should work the same if I understand what you're trying to do.

[yelvington]

Did you consider the MySQL command LOAD DATA INFILE, which can read data from delimited files directly?

[Anon]

Thank you all for your messages and suggestions. Tom and Steve. thanks a bunch!

[groberts23]

Guys-

Your code is really helpful for me, but I have a problem that is a slight twist on this issue. Was hoping you could help. I have a text file (non-delimited, just straight text) that I want to split into 4kb parts and insert into an oracle database. I want each 4 kb chunk to represent a paragraph. I.e. first chunk=paragraph 1, 2nd chunk=paragraph 2, etc.

Any ideas?

Thanks for your help.....

[toma42]

do the same thing, but for that part of the file, just do a substr. should work.