csv2sql.php 1.1 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
  1. #!/usr/bin/php
  2. <?php
  3. define('CHARSET', 'UTF8');
  4. define('FIELD_SEP', "\t");
  5. ini_set("memory_limit", '32M');
  6. define('ROWSPERLINE', 3000);
  7. $tablename=$argv[1];
  8. $f = fopen($argv[2], "r");
  9. if(!$f) die("Cannot open file");
  10. $line=trim(fgets($f,65535));
  11. $words=explode(FIELD_SEP, $line);
  12. $fields_count = count($words);
  13. print "SET NAMES ".CHARSET.";\n\n";
  14. print "DROP TABLE IF EXISTS `".$tablename."`;\n";
  15. print "CREATE TABLE `".$tablename."` (\n";
  16. print "\t`".implode("` varchar(255),\n\t`", $words).'` varchar(255)';
  17. print "\n) CHARSET=".CHARSET.";\n\n";
  18. $fieldsstr='`'.implode("`,`",$words).'`';
  19. $linen=0;
  20. while(!feof($f)) {
  21. if(!($linen%ROWSPERLINE))
  22. $outline = 'INSERT INTO `'.$tablename.'` ('.$fieldsstr.')VALUES';
  23. $outline .= '(';
  24. $line = str_replace('"', '\"', trim(fgets($f, 65536)));
  25. $words = explode(FIELD_SEP, $line);
  26. while (count($words) < $fields_count)
  27. $words[] = '';
  28. $outline .='"'.implode('","',$words).'"';
  29. $outline .= "),";
  30. $linen++;
  31. if(!($linen%ROWSPERLINE))
  32. print substr($outline, 0, -1).";\n";
  33. }
  34. if($linen%ROWSPERLINE)
  35. print substr($outline, 0, -1).";\n";
  36. fwrite(STDERR, "rows: ".$linen."\n");
  37. fclose($f);
  38. ?>