Export data from CSV to MySQL table - PHP Script

Export from CSV to MySQL table – PHP Script

March 13th, 2009 View Comments

Hello Friends !!

In my later post on Export data from MS EXCEL sheet to MySQL table – PHP Script , i explained how store data from MS EXCEL sheet to MySQL table.

Today I will give you PHP code for store data from CSV file to MySQL table. You have to just make one PHP file, copy code displayed below and paste in that file. After this just run that PHP script.

Here is the code:

<?php

$databasehost = “hostaddress”;
$databasename = “dbname”;
$databasetable = “tablename”;
$databaseusername =”username”;
$databasepassword = “password”;
$fieldseparator = “;”;
$lineseparator = “\n”;
$csvfile = “export.csv”;Â Â // CSV file from which you want to store data in MySQL table.
$addauto = 0;
$save = 1;
$outputfile = “output.sql”;

if(!file_exists($csvfile)) {
echo “File not found. Make sure you specified the correct path.\n”;
exit;
}

$file = fopen($csvfile,”r”);

if(!$file) {
echo “Error opening data file.\n”;
exit;
}

$size = filesize($csvfile);

if(!$size) {
echo “File is empty.\n”;
exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = “”;
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line,” \t”);

$line = str_replace(“\r”,”",$line);

$line = str_replace(“‘”,”\’”,$line);
$linearray = explode($fieldseparator,$line);

$linemysql = implode(“‘,’”,$linearray);

if($addauto)
$query = “insert into $databasetable values(”,’$linemysql’);”;
else
$query = “insert into $databasetable values(‘$linemysql’);”;

$queries .= $query . “\n”;

@mysql_query($query);
}

@mysql_close($con);

if($save) {

if(!is_writable($outputfile)) {
echo “File is not writable, check permissions.\n”;
}

else {
$file2 = fopen($outputfile,”w”);

if(!$file2) {
echo “Error writing to the output file.\n”;
}
else {
fwrite($file2,$queries);
fclose($file2);
}
}

}

echo “Found a total of $lines records in this csv file.\n”;

for run this script you have to make one txt file with extention .sql . In above i make output.sql file in which when CSV converted in sql, data stored in this file in query format . After this you can easily import this sql file to MySQL database from phpmyadmin.

I use this script and is working properly. If you have any problem with this script then please comment me.

Thanks …..