Sat
Mar
7
How to Import CSV File In Your PHP Application
PHP gives an ease when it comes to Import flat data (Data without hierarchical structure). If you are using XML to fetch and store data that is fine but what about the case when your client have excel sheet ready with multiple records to go into database. CSV (Comma Separated Values) format is a good candidate format to import or export your application data.
Importing data from CSV files
Since we have lines of values separated by commas, the easiest way to process them is to parse each line using the PHP explode() function:
Dealing with special characters
This simple solution will not work if you have a comma in a value, like for instance when the column is an address and it has a value is like “North street, 1”. In such cases, the column value in the CSV file is quoted to indicate that the data between quotes should be read as a single column. To deal with this situation, you can use a specially tailored regular expression or use the PHP fgetcsv() function.
Note that locale settings may affect how fgetcsv() function works. As noted in the PHP manual, if LANG locale setting is for instance “en_US.UTF-8”, files in one-byte encoding are read wrong by this function, so be aware.
Importing CSV data directly into the database
If you are importing data from a CSV file into a MySQL database, you import it directly into a database table. It will be much faster than processing it line by line using PHP. Fortunately, most relational database have tools for the bulk data import from CSV files. Load data command will help you here. For instance, in MySQL you can use LOAD DATA INFILE query statement.Friendly User Interface to Import CSV Data into a Database
An alternative solution to import CSV data is to use my class Quick CSV Import. This is even more user-friendly alternative to import CSV data into a database consists in using the application “Quick CSV import with visual mapping”. This application helps importing CSV files into a database table allowing to define with CSV file columns are mapped to which columns of the database table. This application can even suggest automatically separator character besides the comma. Following is an example of Quick CSV import
file_name = $file_name;
$this->arr_csv_columns = array();
$this->use_csv_header = true;
$this->field_separate_char = ",";
$this->field_enclose_char = "\"";
$this->field_escape_char = "\\";
$this->table_exists = false;
}
function import()
{
if($this->table_name=="")
$this->table_name = "temp_".date("d_m_Y_H_i_s");
$this->table_exists = false;
$this->create_import_table();
if(empty($this->arr_csv_columns))
$this->get_csv_header_fields();
/* change start. Added in 1.5 version */
if("" != $this->encoding && "default" != $this->encoding)
$this->set_encoding();
/* change end */
if($this->table_exists)
{
$sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).
"' INTO TABLE `".$this->table_name.
"` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
"' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
"' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
"' ".
($this->use_csv_header ? " IGNORE 1 LINES " : "")
."(`".implode("`,`", $this->arr_csv_columns)."`)";
$res = @mysql_query($sql);
$this->error = mysql_error();
}
}
//returns array of CSV file columns
function get_csv_header_fields()
{
$this->arr_csv_columns = array();
$fpointer = fopen($this->file_name, "r");
if ($fpointer)
{
$arr = fgetcsv($fpointer, 10*1024, $this->field_separate_char);
if(is_array($arr) && !empty($arr))
{
if($this->use_csv_header)
{
foreach($arr as $val)
if(trim($val)!="")
$this->arr_csv_columns[] = $val;
}
else
{
$i = 1;
foreach($arr as $val)
if(trim($val)!="")
$this->arr_csv_columns[] = "column".$i++;
}
}
unset($arr);
fclose($fpointer);
}
else
$this->error = "file cannot be opened: ".(""==$this->file_name ? "[empty]" :
@mysql_escape_string($this->file_name));
return $this->arr_csv_columns;
}
function create_import_table()
{
$sql = "CREATE TABLE IF NOT EXISTS ".$this->table_name." (";
if(empty($this->arr_csv_columns))
$this->get_csv_header_fields();
if(!empty($this->arr_csv_columns))
{
$arr = array();
for($i=0; $iarr_csv_columns); $i++)
$arr[] = "`".$this->arr_csv_columns[$i]."` TEXT";
$sql .= implode(",", $arr);
$sql .= ")";
$res = @mysql_query($sql);
$this->error = mysql_error();
$this->table_exists = ""==mysql_error();
}
}
/* change start. Added in 1.5 version */
//returns recordset with all encoding tables names, supported by your database
function get_encodings()
{
$rez = array();
$sql = "SHOW CHARACTER SET";
$res = @mysql_query($sql);
if(mysql_num_rows($res) > 0)
{
while ($row = mysql_fetch_assoc ($res))
{
$rez[$row["Charset"]] = ("" != $row["Description"] ? $row["Description"] : $row["Charset"]); //some MySQL databases return empty Description field
}
}
return $rez;
}
//defines the encoding of the server to parse to file
function set_encoding($encoding="")
{
if("" == $encoding)
$encoding = $this->encoding;
$sql = "SET SESSION character_set_database = " . $encoding; //'character_set_database' MySQL server variable is [also] to parse file with rigth encoding
$res = @mysql_query($sql);
return mysql_error();
}
/* change end */
}
?>
Test CSV File
PROGRAMNAME,PROGRAMURL,LASTUPDATED,NAME,KEYWORDS,DESCRIPTION,SKU,MANUFACTURER,MANUFACTURERID,UPC,ISBN,CURRENCY,SALEPRICE,PRICE,RETAILPRICE,FROMPRICE,BUYURL,IMPRESSIONURL,IMAGEURL,ADVERTISERCATEGORY,THIRDPARTYID,THIRDPARTYCATEGORY,AUTHOR,ARTIST,TITLE,PUBLISHER,LABEL,FORMAT,SPECIAL,GIFT,PROMOTIONALTEXT,STARTDATE,ENDDATE,OFFLINE,ONLINE,INSTOCK,CONDITION,WARRANTY,STANDARDSHIPPINGCOST
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Stoves 60cm Wide Dual Fuel Cooker,61DFDO,Stoves 60cm Wide Dual Fuel Cooker,16488,Stoves,61DFDO,,,GBP,0.00,583.99,0.00,,http://www.kqzyfj.com/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16488,http://www.awltovhc.com/image-1679007-10385498,,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Hotpoint Ultima 60cm Gas Cooker with Sandstone Finish,EG73T,Hotpoint Ultima 60cm Gas Cooker with Sandstone Finish,16122,Hotpoint,EG73T,,,GBP,0.00,473.99,0.00,,http://www.tkqlhce.com/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16122,http://www.lduhtrp.net/image-1679007-10385498,http://www.be-direct.co.uk/additional/product_images/id16122.jpg,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Hotpoint 60cm Electric Ceramic/Halogen Hob with Sandstone Finish,EW83T,Hotpoint 60cm Electric Ceramic/Halogen Hob with Sandstone Finish,16390,Hotpoint,EW83T,,,GBP,0.00,503.99,0.00,,http://www.tkqlhce.com/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16390,http://www.tqlkg.com/image-1679007-10385498,http://www.be-direct.co.uk/additional/product_images/id16390.jpg,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Hotpoint 60cm Gas Cooker with Polar White Finish,GW62P,Hotpoint 60cm Gas Cooker with Polar White Finish,16402,Hotpoint,GW62P,,,GBP,0.00,495.99,0.00,,http://www.dpbolvw.net/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16402,http://www.tqlkg.com/image-1679007-10385498,http://www.be-direct.co.uk/additional/product_images/id16402.jpg,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Now, a working PHP example to use the above Quick CSV import Class and import the CSV File provided above
get_encodings(); //take possible encodings list
$arr_encodings["default"] = "[default database encoding]"; //set a default (when the default database encoding should be used)
if(!isset($_POST["encoding"]))
$_POST["encoding"] = "default"; //set default encoding for the first page show (no POST vars)
if(isset($_POST["Go"]) && ""!=$_POST["Go"]) //form was submitted
{
$csv->file_name = $HTTP_POST_FILES['file_source']['tmp_name'];
//optional parameters
$csv->use_csv_header = isset($_POST["use_csv_header"]);
$csv->field_separate_char = $_POST["field_separate_char"][0];
$csv->field_enclose_char = $_POST["field_enclose_char"][0];
$csv->field_escape_char = $_POST["field_escape_char"][0];
$csv->encoding = $_POST["encoding"];
//start import now
$csv->import();
}
else
$_POST["use_csv_header"] = 1;
?>
Quick CSV import Quick CSV import
=(!empty($csv->error) ? “Errors: “.$csv->error : “”)?>
