Skip to main content
Developers Academy Community Sign in

Creation of a CSV Importer

  • Updated

In the following it is explained how a CSV file can be read in the command line.

In this example, both the PHP script and the CSV file are located in the folder Importer of the Xentral folder.

Head of the script

In order for the script to access Xentral's own functions, a few classes have to be included and objects must be created. This is done with following code:

<?php
// Report simple errors only

error_reporting(E_ERROR | E_WARNING | E_PARSE);

include_once("../conf/main.conf.php");
include_once("../phpwf/plugins/class.mysql.php");
include_once("../phpwf/plugins/class.string.php");
include_once("../phpwf/plugins/class.user.php");
include_once("../www/lib/class.erpapi.php");

class app_t {
 var $DB;
 var $String;
 var $User;
}

$app = new app_t();
$app->User = new User($app);
$conf = new Config();
$app->DB = new DB($conf->WFdbhost,$conf->WFdbname,$conf->WFdbuser,$conf->WFdbpass);
$app->string = new string();
$erp = new erpAPI($app);
$app->erp = $erp;

Pass parameters

If a script is called in the console with

php script.php file

the parameters are stored in the $argv array. It is useful to check whether a parameter is given at all and whether the file specified in the call exists:

if(!isset($argv[1]))
{
  the("No file specified for import");
}
if(!file_exists($argv[1]))
{
  the("file ".$argv[1]." does not exist");
}
if(!is_file($argv[1]))
{
  the($argv[1]." is not a file");
}

Reading the file

Here is shown how to read the file:

$csvimport = new CSVImport($app, $argv[1]);


class CSVImport
{
 var $app;
 var $file;

 function __construct(&$app, $file)
 {
   $this->app = $app;
   $this->file = $file;

 }

 function read()
 {
   if($this->file && file_exists($this->file) && is_file($this->file))
   {
     if (($handle = fopen($this->file, "r")) !== FALSE) {
       $row = 0;
       while (($csv = fgetcsv($handle, 0, ";")) !== FALSE) {
         //$line = iconv("ISO-8859-1", "UTF-8", $line); //If script is stored in UTF-8 and CSVin ANSI the string will be converted
         foreach($csv as $k => $el)$csv[$k] = iconv("ISO-8859-1","UTF-8", $el);
         $row++;
         //$csv = str_getcsv($line,"\t","\""); //When CSVis tab delimited and individual elements are enclosed with ".
         //$csv = str_getcsv($line, ";",""); //separated with ;.
         if($row < 5) { //If the header of the file has 4 lines this can be used for debugging e.g . 

         } else {
           //From here on the actual data will be processed

           $this->process($csv);


         }
       }
     }
   } else {
     return false;
   }
 }
 function process(&$csv)
 {
   //Process the individual data

   ...
 }
}

Processing the data with Wawi functions

The erpAPI class provides many functions for importing and processing the data. In this example, the import of articles is explained:

function process(&$csv){//Process the individual data
if($csv[30]){$data['number']=$csv[2];//If article number is 3rd column$data['name_en']=$csv[0];//If article name is in first column$data['short_text_en']=$csv[22];/*$data['description_en'] = ....
      $data['manufacturer'] = ...
      $data['manufacturer_link'] = ...*/$data['weight']=$csv[48];if($scv[32]=='D1')$data['sales-tax']='ermaessigt';//data will be saved with the erpAPI
$artikelid=true;$artikelid=$this->app->erp->InsertUpdateArticle($data);//Now the sales prices
$pricegross=$csv[30];$nettoprice=$pricegross/(1+($scv[32]=='D1'?7:19)/100);//print_r($data);//echo $netprice;
$this->app->erp->AddSellPrice($artikelid,1,0,$nettoprice);}if($csv[31]){//downloadarticle$data2['number']=$csv[2].'D';//If item number is 3rd column$data2['name_en']=$csv[0].' Download';//If article name in first column is$data2['short_text_en']=$csv[22];/*$data['description_en'] = ....
      $data['manufacturer'] = ...
      $data['manufacturer_link'] = ...*/if($scv[32]=='D1')$data2['sales_tax']='reduced';if(isset($articleid)){$data2['variante']=1;$data2['variant_of']=$artikelid;}$artikelid2=$this->app->erp->InsertUpdateArticle($data2);$pricegross=$csv[31];$nettoprice=$pricegross/(1+($scv[32]=='D1'?7:19)/100);//print_r($data2);//echo $netprice;
$this->app->erp->AddSellPrice($artikelid2,1,0,$nettoprice);
    } 
 }

Entire file

The entire file looks like this:

<?php
// Report simple errors only

error_reporting(E_ERROR | E_WARNING | E_PARSE);

include_once("../conf/main.conf.php");
include_once("../phpwf/plugins/class.mysql.php");
include_once("../phpwf/plugins/class.string.php");
include_once("../phpwf/plugins/class.user.php");
include_once("../www/lib/class.erpapi.php");

class app_t {
 var $DB;
 var $String;
 var $User;
}

$app = new app_t();
$app->User = new User($app);
$conf = new Config();
$app->DB = new DB($conf->WFdbhost,$conf->WFdbname,$conf->WFdbuser,$conf->WFdbpass);
$app->string = new string();
$erp = new erpAPI($app);
$app->erp = $erp;

if(!isset($argv[1]))
{
  the("No file specified for import");
}
if(!file_exists($argv[1]))
{
  the("File ".$argv[1]." does not exist");
}
if(!is_file($argv[1]))
{
  the($argv[1]." is not a file");
}

$csvimport = new CSVImport($app, $argv[1]);
$csvimport->read();

class CSVImport
{
 var $app;
 var $file;

 function __construct(&$app, $file)
 {
   $this->app = $app;
   $this->file = $file;

 }

 function read()
 {
   if($this->file && file_exists($this->file) && is_file($this->file))
   {
     if (($handle = fopen($this->file, "r")) !== FALSE) {
       $row = 0;
       while (($csv = fgetcsv($handle, 0, ";")) !== FALSE) {
         //$line = iconv("ISO-8859-1", "UTF-8", $line); //If script is stored in UTF-8 and CSVin ANSI the string will be converted
         foreach($csv as $k => $el)$csv[$k] = iconv("ISO-8859-1","UTF-8", $el);
         $row++;
         //$csv = str_getcsv($line,"\t","\""); //When CSVis tab delimited and individual elements are enclosed with ".
         //$csv = str_getcsv($line, ";",""); //separated with ;.
         if($row < 5) { //If the header of the file has 4 lines this can be used for debugging e.g . 


         } else {
           //From here on the actual data will be processed

           $this->process($csv);


         }
       }
       return true;
     } else the("Could not open file ".$this->file."! \r\n");
   } else {
     echo $this->file." not found\r\n";
     return false;
   }
 }
 function process(&$csv)
 {

     //Process the individual data

     if($csv[30])
     {

     $data['number'] = $csv[2]; //If article number is 3rd column
     $data['name_en'] = $csv[0]; //If article name is in first column
     $data['short_text_en'] = $csv[22];
     /*$data['description_en'] = ....
      $data['manufacturer'] = ...
      $data['manufacturer_link'] = ...*/
     $data['weight'] = $csv[48];
     if($scv[32] == 'D1')$data['sales-tax'] = 'ermaessigt';

     //data will be saved with the erpAPI

     $artikelid = true;
     $artikelid = $this->app->erp->InsertUpdateArticle($data);

     //Now the sales prices

     $pricegross = $csv[30];
     $nettoprice = $pricegross / (1+($scv[32]=='D1'?7:19)/100);
     //print_r($data);
     //echo $netprice;

     $this->app->erp->AddSellPrice($artikelid,1,0,$nettoprice);
   }
   if($csv[31])
   {
     //downloadarticle
     $data2['number'] = $csv[2].'D'; //If item number is 3rd column
     $data2['name_en'] = $csv[0].' Download'; //If article name in first column is
     $data2['short_text_en'] = $csv[22];
     /*$data['description_en'] = ....
      $data['manufacturer'] = ...
      $data['manufacturer_link'] = ...*/
     if($scv[32] == 'D1')$data2['sales_tax'] = 'reduced';
     if(isset($articleid))
     {
       $data2['variante'] = 1;
       $data2['variant_of'] = $artikelid;
     }
     $artikelid2 = $this->app->erp->InsertUpdateArticle($data2);
     $pricegross = $csv[31];
     $netprice = $pricegross / (1+($scv[32]=='D1'?7:19)/100);
     //print_r($data2);
     //echo $netprice;

     $this->app->erp->AddSellPrice($artikelid2,1,0,$nettoprice);
   }

 }
}

Execute import file once

The file that processes the CSV file (.php file) must be stored in the Xentral folder in the folder importer. This is also where the required CSV files for processing. In this example, the PHP file exampleimporter.php and the CSV file exampledata.csv.

Afterwards the console is called and changed into the importer folder, e.g. with

 cd /var/www/html/wawision/importer

The path may differ depending on how the directory structure is set up.

With

 php exampleimporter.php exampledata.csv

the PHP file is called and the code in it is executed.

The file names must be replaced by the respective file names.

It may be that a type has to be specified, but here the console will draw attention to this and name the possible types. Then the call with

php exampleimporter.php exampletype exampledata.csv

Depending on the size of the CSV file, the process can take several minutes. take several minutes. The code was completely executed, if in the command line again the user name and path appears in a new line.

Import file manually several times

The file which processes the CSV file (.php file) must be placed in the Xentral folder in the folder importer. The required CSV files for processing will be placed here as well. In this example, the PHP file exampleimporter.php and the CSV file exampledata.csv.

Afterwards the console is called and changed into the importer folder, e.g. with

cd /var/www/html/wawision/importer

The path may differ depending on how the directory structure is set up.

With

php exampleimporter.php

the PHP file is called and the code in it is executed. The filename must be replaced by the respective file name. Depending on the size of the CSV file, the process can take several minutes. The code has been executed completely, if the command line shows the username and path again in a new line.

Related articles

Visit the Academy - you'll find videos for most topics