Importing a csv file to mysql

This is one of the things I did with my current project. It is about adding data on MySQL using a csv file and I thought I should share what I did.

First we need to have an html form that will ask for the file input. It will be something like this one:

[form action="import_csv.php" method="post" enctype="multipart/form-data"]
[input name="csv" type="file"]
[input name="filesubmit" value="Submit" type="submit"]
[/form]

Note: Replace "[" and "]" with "<" and ">" respectively. I did it like this because this blog won't allow attaching of html tags.

The enctype attribute is very important because it specifies to use "multipart/form-data" as content type for the form. This also means that the form requires a binary data like the file. The file input type specifies the input is looking for a file. This will appear as a text field with a browse button next to it.

Then we need our php script that will process the csv file and save the entries on the database.

//this is where the file is stored temporarily
$csvfile = $_FILES['csvfile']['tmp_name'];

$file = fopen($csvfile, "r"); //we now open the file
$filesize = filesize($csvfile); //get the filesize

$contents = fread($file, $filesize); //read contents of the file
fclose($file); //close file

$linecount = 0;
$linedata = array();

//you can modify "\n" if your csv file is ended differently
foreach(split("\n",$contents) as $line)
{
$line = trim($line," \t");
$line = str_replace("\r","",$line);
$line = str_replace("'","\'",$line);
$linedata = explode(",",$line);
$mysqlline = implode("','",$linedata);
$linecount++;

//insert the data into the database
//replace $yourtablename with your mysql table name
$sql = mysql_query("insert into '.$yourtablename.' values('$mysqlline')");
}

That's it. We have a simple php script that accepts a csv file and then reads the contents and save the data into your mysql database. Of course this still needs to be optimize. We need to check if the file input is really a csv file. Or is the file empty or too large? I will leave it up to you.

Oh, and be sure that you connect to mysql first. :)