Parsing a file of values in order to change into an SQL insert

Question

Hey, trying to figure out a way to use a file I have to generate an SQL insert to a database. The file has many entries of the form:

100090 100090 bill smith 1998

That is,an id number, another id(not always the same), a full name and a year. These are all separated by a space.

Basically what i want to to is be able to get variables from these lines as I iterate through the file so that i can,for instance give the values on each line the names: id,id2,name,year. I then want to pass these to a database.So for each line id be able to do (in pseudo code)

INSERT INTO BLAH VALUES(id, id2,name , year)

This is in php, I noticed I haven't outlined that above, however i have also tried using grep in order to find the regex but cant find a way to paste the code: eg:"VALUES()" around the information from the file.

Any help would be appreciated. I'm kind of stuck on this one

Answer 1

If you can rely on the file's structure (and don't need to do additional sanitation/checking), consider using LOAD DATA INFILE.

GUI tools like HeidiSQL come with great dialogs to build fully functional mySQL statements easily.

Alternatively, PHP has fgetcsv() to parse CSV files.

Answer 2

Try something like this:

$fh = fopen('filename', 'r');
$values = array();
while(!feof($fh)) {
  //Read a line from the file
  $line = trim(fgets($fh));

  //Match the line against the specified format
  $fields = array();
  if(preg_match(':^(\d+) (\d+) (.+) (\d{4})$:', $line, $fields)) {
    //If it do match, create a VALUES() block
    //Don't forget to escape the string part
    $values[] = sprintf('VALUES(%d, %d, "%s", %d)',
      $fields[1], $fields[2], mysqli_real_escape_string($fields[3]), $fields[4]);
  }
}
fclose($fh);

$all_values = implode(',', $values);
//Check out what's inside $all_values:
echo $all_values;

If the file is really big you'll have to do your SQL INSERTs inside the loop instead of saving them to the end, but for small files I think it's better to save all VALUEs to the end so we can do only one SQL query.

Answer 3

If all of your lines look like the one you posted, you can read the contents of the file into a string (see http://www.ehow.com/how_5074629_read-file-contents-string-php.html)

Then use PHP split function to give you each piece of the query. (Looks like preg_split() as of PHP 5.3).

The array will look like this:

myData[0] = 10090
myData[1] = 10090
myData[2] = Bill Smith
myData[3] = 1998

.....And so on for each record

Then you can use a nifty loop to build your query.

for($i = 0, $i < (myData.length / 4); $i+4)
{
$query = 'INSERT INTO MyTABLE VALUES ($myData[$i],$myData[$i+1],$myData[$i+2],myData[$i+3])'

//Then execute the query
}

This will be better and faster than introducing a 3rd party tool.