Pan Fashion

And finally, here it is all together, download the gzipped csv to your server, unpack it on the server, read the first two lines of the csv to create the table and import the data.

You don’t need to know the structure of the csv, this will automatically construct the mysql table for you.

<?php
//Configuration

$dbhost = 'localhost'; //normally localhost

$dbuser = 'your_dbase_username'; //your dbase username

$dbpass = 'your_dbase_password'; //your dbase password

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');

$dbname = 'your_dbase_name'; //your dbase name

$tablename = 'your_table_name'; //which doesn't have to exist

mysql_select_db($dbname);

$url = "your_download_url";   // insert your download url for a gzipped, csv file

$filename = "temp.csv.gz"; // the name you want for the saved gzipped file

$newfilename='temp.csv'; //change this to whatever filename you want
?>
<?php

// maximum execution time in seconds
set_time_limit(120);
//set memory limit
ini_set("memory_limit","3072M");

$newfname = $filename;

$file = fopen ($url, "rb");
	if ($file) {
  		$newf = fopen ($newfname, "wb");

  	if ($newf)
  		while(!feof($file)) {
    		fwrite($newf, fread($file, 1024 ), 1024 );
  		}
	}

	if ($file) {
  		fclose($file);
	}

	if ($newf) {
  		fclose($newf);
	}

$zd = gzopen($filename, "r");
$contents = gzread($zd, 10000000); //because I have no idea how big your file will be
gzclose($zd);

$tempvar = fopen($newfilename,"w");
fwrite($tempvar, $contents);
fclose($tempvar);

?>
<?php
// GENERATE TABLE WITH FIELD_NAMES FROM FIRST LINE OF CSV FILE AND FIELD TYPES FROM SECOND LINE

$inputFile = $newfilename;

//OPEN CSV FILE FOR READING
$infile = fopen($inputFile, 'r');
//READ 5K, WHICH SHOULD BE ENOUGH TO READ THE FIRST TWO LINES FROM ANY CSV FILE
$contents = fread($infile, 5120);
// CLOSE CSV FILE
fclose($infile);
//CREATE AN ARRAY OF THE LINES FROM THE CSV FILE
$infilelines = explode("\n", $contents);
// CREATE AN ARRAY OF FIELD NAMES FROM THE FIRST LINE OF THE CSV
$fieldnames = explode(',', $infilelines[0]); // CHANGE THIS TO '\t' IF ITS TAB DELIMITED OR '|' IF ITS PIPE DELIMITED
// CREATE AN ARRAY OF FIELD CONTENT FROM THE SECOND LINE OF THE CSV
$fieldtype = explode(',', $infilelines[1]);
// BEGIN WRITING THE SQL QUERY
$thequery = 'CREATE TABLE IF NOT EXISTS  '.$tablename."\n\t".' ('."\n\n"; 

// THE LOOP
for($i = 0; $i <= count($fieldtype)-1; $i++) {  if(strlen($fieldtype[$i]) == 0) $type = 'char(80)';
// IF THERE IS A NUMBER DEFINE IT AS INT
if(preg_match('/[0-9]/', $fieldtype[$i])) $type = 'int(11)';
// IF ITS IN THIS FOMRAT 0000-00-00 DEFINE IT AS A DATE (thanks James)
if(preg_match('/\d\d\d\d-\d\d-\d\d/', $fieldtype[$i])) $type = 'datetime';
// IF THERE IS A FORWARD SLASH DEFINE IT AS A DATE
if(preg_match('/[\/]/', $fieldtype[$i])) $type = 'datetime';
// IF THERE IS A DECIMAL POINT DEFINE IT AS A DOUBLE (PROBABLY A CURRENCY)
if(preg_match('/[\.]/', $fieldtype[$i])) $type = 'double';
// THIS WILL BE THE DEFAULT VARCHAR(255) AFTER ALL THE OTHER OPTIONS HAVE BEEN CHECKED
if(preg_match('/[a-z\\\']/i', $fieldtype[$i])) $type = 'char(255)';
// IF ITS LONGER THAN 255 CHARACTERS DEFINE IT AS LONGTEXT
if(strlen($fieldtype[$i]) >=255) $type = 'longtext';
 if ($i == count($fieldtype)-1) {
 $thequery .= "\t".trim($fieldnames[$i])."\t".$type."\n";
 }
 else
 {
 $thequery .= "\t".$fieldnames[$i]."\t".$type.', '."\n";
 }

} // END THE LOOP
//FINISH THE SQL QUERY
$thequery .= ')';
$thequery = str_replace('-','_',$thequery);
$thequery = str_replace('condition','condition1',$thequery);
echo $thequery;
$result = mysql_query($thequery);

?>

<?php
//load the data from the csv file into the database
$sql = 'LOAD DATA LOCAL INFILE \''.$newfilename.'\' INTO TABLE '.$tablename.' FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' IGNORE 1 LINES';
$result = mysql_query($sql) or die (mysql_error()); 

?>

<?php
//close dbase connection
mysql_close($conn);
?>