Import a Large CSV file into Database

In thiScripts Gurus post I would like to show you how I import a large csv file into a database.  I typically use the import function in my phpadmin and import the file that way but if the file is too large and times out I have to resort to other ways.  I use a “Library” file to handle the mysql connection to the database and insert to the database.  I will cover than in another post.
This script will take the csv file which have uploaded to your host server and parse it and insert it into the database.  The column names will transfer to the database and become the column names in the table.  I didn’t take the time to make a key or unique column but that can be easily done in the phpadmin.  After the file is opened by the script it will be parsed by the comma line by line.  Each line is processed and put into the database table.  Don’t forget to use the “addslashes()” to the script to work with special characters.

<?php

include(‘LIB_mysql.php’);
$fn = ‘Your_CSV_FILE.csv’;

$fd = fopen($fn, “r”);

$sql=”CREATE TABLE Your_New_Table
(
“;
$flag=0;
$xxy=0;
while (!feof($fd)) {

$fields = fgetcsv($fd, 0, “,”);

if($flag==0){
$flag=1;
for($x=0;$x<count($fields);$x++){
$data_fields[$x]=$fields[$x];
if($x>0){
$sql.=”, “;
}
// If the field is a special type then set the type in the database here.
if($fields[$x]==’LEANM09′ ||$fields[$x]==’SCHNAM09′ || $fields[$x]==’MSTREE09′ ||$fields[$x]==’LSTREE09′ ||$fields[$x]==’LCITY09′ ||$fields[$x]==’CONAME09′ ||$fields[$x]==’Location’ ){
$sql.=$fields[$x].” varchar(60)”;
}else{
$sql.=$fields[$x].” varchar(16)”;
}
}

$sql.=”)”;

}else{
{
unset($data_array);
for($x=0;$x<count($fields);$x++){

$data_array[$data_fields[$x]]=addslashes($fields[$x]);
if($data_array[$data_fields[$x]]==”){
$data_array[$data_fields[$x]]=0;
}
}

insert(‘Your_Database’, ‘Your_New_Table’, $data_array);
}

}

}

php?>

This script is great for those wanting to import a large data file to use with wordpress.  I have used this for many different imports such as for hospital databases.  The hospital database was very large and couldn’t be imported with the phpadmin function.  The nurse uniforms database was for the nurses, and doctors, and those in the medical field to find uniforms.  The data was fairly massive and so the script ran without it issue for the initial upload.

Comments Off on Import a Large CSV file into Database

Filed under Uncategorized

Comments are closed.