Create MySQL table dynamically from Excel CSV file

Question

My goal is to create a MySQL table containing data from my CSV file. I know how to create a MySQL table and how to load data from excel in it.

But the problem is: I have a large CSV file containing long column names (questions labels for example "Q27 : Are you happy with the after sales service?") so it would be boring to create a MySQL table by copying all column names(almost 35) and add 'VARCHAR(100) NOT NULL'.

That's why I would like to write a small php script to create a MySQL table by getting the first row of my file, and then fill it with the rest of the csv file data.

For now, my script looks like this :

<?php

$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'test';

$db = @mysql_connect($host, $user, $pass) or die('mysql connection pb');
@mysql_select_db($database) or die('database selection pb');
/********************************************************************************/
// Parameters: filename.csv table_name

$argv = $_SERVER['argv'];

if($argv[1]) { $file = $argv[1]; }
else {
    echo "Please provide a file name\n"; exit; 
}
if($argv[2]) { 
$table = $argv[2];
}
else {
    echo "Please provide a table name\n";
    $table = pathinfo($file);
    $table = $table['filename'];
}

/****************************************************************************    ****/
// Get the first row to create the column headings

$fp = fopen($file, 'r');
$frow = fgetcsv($fp,";");
$columns=false;
print_r($frow);
foreach($frow as $column) {
    if($columns) $columns .= ', ';
    $columns .= "`$column` VARCHAR(250) NOT NULL";
}
$create = "create table if not exists $table ($columns);";
@mysql_query($create, $db) or die('table creation pb');

/****************************************************************************    ****/
// Import the data into the newly created table.
$file = addslashes(realpath(dirname(__FILE__)).'\\'.$file);

$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
@mysql_query($q, $db);

?>

And when i run in command line : php myscript.php csvfile.csv mytable, it appears that the problem is in the table creation query. And on top of that the column names are not well identified even though they are separated by ";" in the csv.

Have you looked into other options than `php`? It seems that what you need is a data integration tool (I say this because you are handling _large_ csv files) rather than a php script. Having said that, please add the php error throwed to your question — luchosrock, Sep 24 '15 at 22:02
@luchosrock , I would like to use php because after my table will be created in mysql, i want to send queries and draw graphs with jpgraph extension, that's why i was intended to coding evrything in php. — Adri L., Sep 25 '15 at 07:30
@HawasKaPujaari , where are the deprecated functions here please ? — Adri L., Sep 25 '15 at 07:30
@AdriL. `mysql_*` functions are deprecated because of many security issues related to it's use. Please take a look at http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — luchosrock, Sep 25 '15 at 13:31

Parfait · Answer 1 · 2015-09-28T02:00:58.130

As mentioned refrain from using mysql_ functions which as of PHP 7 (current version) this extension is no longer available. Use either mysqli or PDO.

Below is a PDO example with try/catch (more informative than die()). Also, the csv read is handled slightly different and its concatenation in SQL create table string.

<?php

$host="localhost"; 
$username="root"; 
$password="password"; 
$database="test"

// Parameters: filename.csv table_name
$argv = $_SERVER['argv'];

if($argv[1]) { 
   $file = $argv[1]; 
} else {
   echo "Please provide a file name\n"; 
   exit;
} 

if($argv[2]) { 
   $table = $argv[2];
} else {
   echo "Please provide a table name\n"; 
   $table = pathinfo($file); 
   $table = $table['filename'];
}

// Read in only first row of CSV file
$handle = fopen($file, "r");

$row = 1; 
$columns = [];

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE AND $row==1) {
   $columns = $data;
   $row++; 
}

//SQL string commands
$createSQL = "CREATE TABLE IF NOT EXISTS $table 
              (".implode(" VARCHAR(255) NOT NULL, ", $columns). " 
               VARCHAR(255) NOT NULL);";

$file = addslashes(realpath(dirname(__FILE__)).'\\'.$file);
$loadSQL = "LOAD DATA INFILE '$file' 
            INTO TABLE $table 
            FIELDS TERMINATED BY ',' 
            IGNORE 1 LINES";

// Open database connection 
try { 
   $dbh = new PDO("mysql:host=$host;dbname=$database",$username,$password); 
   $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   // Execute queries
   $S1TH = $dbh->query($createSQL); 
   $S2TH = $dbh->query($loadSQL);   
} 

catch(PDOException $e) { 
   echo $e->getMessage(); 
}

# Close database connection 
$dbh = null;

?>

Create MySQL table dynamically from Excel CSV file

1 Answers1