I had this one file I wanted to import today and I quickly wrote an unfinished but maybe usefull import class. You'll find the code here - maybe someone will be helped by it.
<?php
class CSVImport {
private $destinationTable;
private $sourceFile;
private $csvLength = 1024;
private $csvDelimiter = ';';
private $csvEnclosure = '"';
public $firstRowContainsFieldnames = true;
public function __construct($srcFile=false, $dTable=false) {
if ($srcFile) {
$this->setSourceFile($srcFile);
}
if ($dTable) {
$this->setDestinationTable($dTable);
}
if (!empty($GLOBALS['db'])) {
$this->db = $GLOBALS['db'];
}
else {
throw new importException("No database specified");
}
}
public function setSourceFile($src) {
if (file_exists($src)) {
$this->sourceFile = $src;
}
else {
throw new importException ("No such file '$src'");
}
}
public function setDestinationTable($tbl) {
$this->destinationTable = $tbl;
}
private function transformCVSDataInValues($dataRow) {
foreach ($this->map as $fieldname => $columnNo) {
if (!empty($dataRow[$columnNo])) {
$curData = $dataRow[$columnNo];
// check if needs conversion (works case independent - need fixing)
if (!empty($this->globalConversionRules[strtoupper($curData)])) {
$curData = $this->globalConversionRules[strtoupper($curData)];
}
// check the conversion functions for this column
if (!empty($this->conversionFunctions[$columnNo])) {
$curData = $this->conversionFunctions[$columnNo]($curData);
}
$values[$fieldname] = "'".$this->db->escape($curData)."'";
}
}
return $values;
}
/*
$columnNo is starts with 1, instead of 0
*/
public function setFieldSource($fieldname, $columnNo, $allowOverwrite=true) {
$dataRowNo = $columnNo - 1;
if (!@in_array($dataRowNo, $this->map)) {
$this->map[$fieldname] = $dataRowNo;
}
}
/*
works case independent - may be needed to fix this
*/
public function addGlobalConversionRule($data, $replaceData) {
$data = strtoupper($data);
$this->globalConversionRules[$data] = $replaceData;
}
/*
data rows are actually 0 based but passed 1-based... so distract 1
*/
public function addConversionFunction($columnNo, $functionName) {
$this->conversionFunctions[$columnNo - 1 ] = $functionName;
}
public function setDebugLevel($debugLevel) {
if (in_array($debugLevel, array('verbose', 'silent'))) {
$this->debugLevel = $debugLevel;
}
else {
throw new Exception ("Please choose 'verbose' or 'silent' as debug level");
}
}
/*
Return the past queries for this run
*/
public function getQueryStack() {
return $this->queryStack;
}
public function setDryRun($status) {
if (in_array($status, array('on', 'off'))) {
if ($status == 'on') {
$this->runDry = true;
}
else {
$this->runDry = false;
}
}
else {
throw new Exception ("Please choose 'on' or 'off' as status for setting dry run");
}
}
public function startImport() {
$fp = fopen ($this->sourceFile, "r");
if ($this->firstRowContainsFieldnames) {
//echo "del: $this->csvDelimiter";
$fieldnames = fgetcsv($fp, $this->csvLength, $this->csvDelimiter, $this->csvEnclosure);
$i=1;
foreach ($fieldnames as $fieldname) {
$this->setFieldSource($fieldname, $i, false);
$i++;
}
}
while ($data = fgetcsv($fp, $this->csvLength, $this->csvDelimiter, $this->csvEnclosure)) {
$values = $this->transformCVSDataInValues($data);
$sql = "insert into $this->destinationTable (".implode(',',array_keys($values)).") values (";
$sql .= implode (',', $values) .")";
//echo $sql.'<br>';
if ($this->runDry) {
$result = true;
}
else {
$result = $this->db->query($sql);
}
if ($result && $this->debugLevel == 'verbose') {
echo "$sql (OK)<br>";
}
$this->queryStack[] = array ($sql, $result);
}
}
}
class importException extends Exception {}
?>
Usage:
- Depends on ezSQL for database connections. Google for ezSQL
- See sample code below
include_once("classes/class.import.php");
function convert($data) {
$data = preg_replace("/[^\d]*(\d+),(\d+)[^\d]*/", "$1.$2", $data);
return $data;
}
$csv = new CSVImport("data.csv", "mytable");
// overwrite some field sources if not correctly specified in
// headers
$csv->setFieldSource("telephone", 14); // db fieldname is telephone, header in CVS file is tel
$csv->setFieldSource("first_name", 4); // dbfield is first_name, header in CVS file is "first name"
$csv->setFieldSource("email", 15);
//
$csv->addGlobalConversionRule("YES", "J"); // field is only 1 char
$csv->addGlobalConversionRule("NO","N");
$csv->addConversionFunction(12, 'convert');
$csv->setDebugLevel('verbose');
$csv->startImport();
Use with care and for what it's worth. I know it's not finished and I know it's not even written to be public. I just remembered a lot of questions about it and thought it may be usefull for the archives of this forum and as reference for others. Please give me feedback if you improve the class.
