CSV to mySQL - Create Table and Insert Data

By Hawkee on Jan 19, 2011

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_name

The table_name parameter is optional. The name of the csv_file will be used if it is excluded.


$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);

foreach($frow as $column) {
    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);



Sign in to comment.
dustale08   -  May 06, 2015

So Should I just pop this into my Terminal?

Hawkee  -  May 07, 2015

Save this as a .php file and run it from the command line.

Sign in to comment

angelxmoreno   -  Nov 23, 2014

What does the $ccount variable do in the script?

Hawkee  -  Nov 23, 2014

Good question. I think that was for debugging purposes, but apparently I forgot to remove it before posting. I removed it.

Sign in to comment

shacker   -  Oct 27, 2014

The issue I hit with this script is that it assumes all data in your CSV will fit into a varchar field, limited to 255 characters. But some of my data needs to go into TEXT fields with unlimited length. Unfortunately there's no way for the script to know in advance what kinds of fields to create in the db. And because the final line of the script, which does the loading, does not report errors, it fails silently (very mysterious).

Hawkee  -  Oct 27, 2014

Yes, this is a very simple script. You can modify it to create each field as a text field then go back later and adjust the smaller fields to be varchars.

Sign in to comment

CyberMage   -  Jun 09, 2014

Love it - saved me some time. I had someone send me a 250 MB file with 57 tilde delimited fields that I needed to import, and PHPMyAdmin of course doesn't like big files. A couple of tweaks to your script and it worked like a charm.

radius314   -  Mar 29, 2014

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  -  Mar 29, 2014

Good catch, I've corrected the flaw.

radius314  -  Mar 29, 2014


Hawkee  -  Mar 29, 2014

BTW, welcome to the site! How did you find us?

radius314  -  Mar 31, 2014

Google Search

Sign in to comment

Neo139   -  Aug 18, 2013

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) {

    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.

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.

Hawkee   -  Nov 10, 2012

@a6april You'll probably need to make some changes to this to meet your needs.

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?

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.

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" />

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

tekagami  -  Oct 31, 2014

How about:

$uploadfile = basename($_FILES['csv']['name']);

if ($uploadfile != "") {
$file= $uploadfile;
move_uploaded_file($_FILES['csv']['tmp_name'], "/path/to/".$file);
Hawkee  -  Oct 31, 2014

@tekagami Be sure to wrap your code with ``` to ensure it shows up properly.

acosonic  -  Aug 26, 2015

Well the script kind'a makes few gltiches on windoze... To fix it
remove notices by:
line 14 replace$_SERVER[argv]; with $_SERVER['argv'];
After line 30 $frow = fgetcsv($fp);
Add $columns=false;
And line 42 now fix path (Windows only though) like this

$file = addslashes(realpath(dirname(__FILE__)).'\\'.$file);
For Linodoze just use / instead of \ ...


Sign in to comment

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.