Convert a tab-delimited file to SQL inserts

This is useful when I’m migrating data from one system to another. It uses the quotesplit code I talked about here: Parsing CSV data files with PHP, using quotesplit.

You want your source file to be tab delimited, with the header row containing the database field names that you are going to load each piece of data into. I use Excel to prepare the file. Then you run this file using the PHP interpreter at the console, and redirect the output to a .sql file. In the Mysql console, source your new .sql file. For convienience, I’ve attached the file tabdelim-to-sqlinsert.zip

You can use this for comma-separated or pipe-separated or whatever as well, if you change t in your call to quotesplit.

< ?php
  ini_set('display_errors',1); 
  error_reporting(E_ALL);

  # User config variables:
  $Filename = 'sourcedata-tabdelim-file.txt';
  $dbname = 'databasename';


  ####################################################
  function format($in)
  {
    $out = trim($in);
    if (($out == '') || ($out == 'NULL')) {
      return 'NULL';
    } elseif (preg_match ('/bd{1,2}/d{1,2}/d{4}b/', $out)) {
      # incorrectly formatted date detected (ie 10/31/2012 or 12/31/9999)

      $datetimeparts = explode(' ', $out);
      $dateparts = explode('/', $datetimeparts[0]); # consider date part only
      $out = $dateparts[2] . '-' . $dateparts[0] . '-' . $dateparts[1];
    }
    return "'" . $out . "'";
  }
  #######################################
  function RemoveArrayElement($array, $removeKey)
  {
    unset($array[$removeKey]);
      foreach ($array as $value)
        $return[] = $value;
    return ($return);
  }
  #######################################
  function DealWithMultipleSurroundingQuotes($splitter, &$getstrings)
  {
   for($x = 0; $x < count($getstrings); $x += 2) //foreach even key
   {
      if (!stristr($getstrings[$x], $splitter)) //if splitter is not in row
      {
         if (trim($getstrings[$x-1]) == '') //if previous row is empty
            //remove previous row
            $getstrings = RemoveArrayElement($getstrings, $x-1);
         else
            //remove current row
            $getstrings = RemoveArrayElement($getstrings, $x);

         return false;
      }
   }
   return true; //Function finished successfully!
  }
  #######################################
  function quotesplit( $splitter=',', $s, $restore_quotes=false )
  {
   # First step is to split it up into the bits that are surrounded by quotes
   # and the bits that aren't. Adding the delimiter to the ends simplifies
   # the logic further down

   $getstrings = explode('"', $splitter . $s . $splitter);

   while(!DealWithMultipleSurroundingQuotes($splitter, $getstrings));

   # $instring toggles so we know if we are in a quoted string or not
   $delimlen = strlen($splitter);
   $instring = 0;

   while (list($arg, $val) = each($getstrings))
   {
      if ($instring == 1)
      {
         if($restore_quotes)
         {
            # Add string with quotes to the previous value in the array
            $result[count($result)-1] = $result[count($result)-1]. '"' . addslashes(trim($val)) . '"';
         } else {
            # Add the whole string, untouched to the array
            $result[count($result)-1] = addslashes(trim($val));
         }
         $instring = 0;
      } else {
         # Break up the string according to the delimiter character
         # Each string has extraneous delimiters around it (inc the ones
         #  we added above), so they need to be stripped off
         $temparray = explode($splitter, substr($val, $delimlen, strlen($val)-$delimlen-$delimlen+1 ) );
         while(list($iarg, $ival) = each($temparray))
            $result[] = addslashes(trim($ival));
         $instring = 1;
      }
   }
   return $result;
  }

  ####################################################
  $file = fopen($Filename, 'r');
  if($file == false)
  {
    print 'Error in opening file';
    exit();
  }
  $Filesize = filesize($Filename);
  $filerow = fgets($file, $Filesize);
  $headerrow = quotesplit("t", $filerow);
  $numcols = 0;

  while($filerow = fgets($file, $Filesize))
  {
    $row = quotesplit("t", $filerow);
    print 'INSERT INTO `' . $dbname . '` (';
    $first = true;
    foreach ($headerrow as $field)
    {
      if ($field == '') {
        # Do nothing
      } elseif ($first) {
        print '`' . $field . '`';
        $first = false;
        $numcols+=1;
      } else {
        print ', `' . $field . '`';
        $numcols+=1;
      }
    }
    print ') VALUES (';
    for ($i=0; $i

Leave a Reply

Your email address will not be published. Required fields are marked *