CSV to mySQL - Create Table and Insert Data

Platform:  PHP
Published  Jan 19, 2011
Updated  Mar 29, 2014
This will take a CSV file, create a table, and insert the data. The file needs to have a header row indicating the field names. Once it's created it uses a simple load data infile call.

This is a command line script, usage: php csv_import.php csv_file.csv table_nameThe table_name parameter is optional. The name of the csv_file will be used if it is excluded. <?php

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

$db = mysql_connect($host, $user, $pass);
mysql_query("use $database", $db);

/********************************************************************************/
// 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 {
$table = pathinfo($file);
$table = $table['filename'];
}

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

$fp = fopen($file, 'r');
$frow = fgetcsv($fp);

$ccount = 0;
foreach($frow as $column) {
$ccount++;
if($columns) $columns .= ', ';
$columns .= "`$column` varchar(250)";
}

$create = "create table if not exists $table ($columns);";
mysql_query($create, $db);

/********************************************************************************/
// Import the data into the newly created table.

$file = $_SERVER['PWD'].'/'.$file;
$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
mysql_query($q, $db);

?>

Comments

Sign in to comment.
radius314   -  26 days ago
Slight flaw in the script, as if the column name contains a reserved keyword. Say for example the word "DELETE". In this case the column variable should be wrapped in back ticks (`) to escape this.
Hawkee  -  26 days ago
Good catch, I've corrected the flaw.
radius314  -  26 days ago
cool
Hawkee  -  26 days ago
BTW, welcome to the site! How did you find us?
radius314  -  24 days ago
Google Search
Sign in to comment

Neo139   -  Aug 18, 2013
Thanks!
PHP Notice: Use of undefined constant argv - assumed 'argv' in import.php on line 14
PHP Notice: Undefined variable: columns in import.php on line 35
PHP Notice: Undefined variable: columns in import.php on line 36
to remove the last two warnings change the foreach for:
foreach($frow as $column) {
$ccount++;
if(isset($columns)) $columns .= ', ';
else $columns="";
$columns .= "$column bigint(20)";
}
Hawkee  -  Aug 27, 2013
@Neo139 Thank you, but why bigint rather than varchar?
Sign in to comment

Hawkee   -  Nov 11, 2012
@a6april You might want to try StackOverflow. Its a much better suited site for questions and answers. You can post concerning the parts where you need help, but I wouldn't ask people to write it for you.
 Respond  
a6april   -  Nov 11, 2012
Can you point me in the right direction to find out what those changes would be? I don't mind starting small and moving from there, I can email you the zip of everything I have done so far.
 Respond  
Hawkee   -  Nov 10, 2012
@a6april You'll probably need to make some changes to this to meet your needs.
 Respond  
a6april   -  Nov 10, 2012
Can't the table name be the name of the csv file? or a temp file in the code? I have seen it done before but can't find the instance in which I had done this. I will look again harder to find it.

Ultimately I have a csv file that a user will upload then I can run queries on it. There are many different csv files slightly different in format, so I would then have to create a script to tailor each scenario? I think that means a new table for each file template?
 Respond  
Hawkee   -  Nov 08, 2012
@a6april This is a command line script, so you'll have to run it from your shell. It will not work through a browser. It takes two parameters: csv file and the table name to be created.
 Respond  
a6april   -  Nov 08, 2012
How do I add a browse button for this?

Lets say I need to select the file can I use something like:

//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");

With a form like this:
<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
Choose your file: <br />
<input name="csv" type="file" id="csv" />
<input type="submit" name="Submit" value="Submit" />
</form>

I tried using the script as is and I always get the error:
'Please provide a file name'

I would really like to get this working so any additional information would be greatly appreciated
 Respond  
Are you sure you want to unfollow this person?
Are you sure you want to delete this?
Click "Unsubscribe" to stop receiving notices pertaining to this post.
Click "Subscribe" to resume notices pertaining to this post.