Make your own CSV, TXT importer

Rating

A challenge .. Complex matter, easily transfer ... I'm going to expain this in small steps ...  

We are ging to import a list of dogs in our ' dog hotel'(Completely fictional, of course). (This discussion can be used equally well to create a Webshop --type Basic Cart-- from a stock list.)

Create a content type 'Dogs' with a text field and a title.

Screen000910.png

REMOVE EXISTING CONTENT

I'm going to learn first to remove all nodes of a content type. So we will always create new nodes. Later we will modify this and update the existing nodes, but I am starting simple ..

Create a simple page and converts the text filter to PHP (PHP module installed). PHP starts with <? Php and ends with?> In between the magic happens .. Make sure you never give rights to someone anonymous to these pages.

The code opens a query that searches the nodes of the content type placed in the condition. The handler performs the removal. Save and Tarzan is going to fly ... Every time you use the display of the page you are activating the system. Be carefull..

<?php

//delete nodes from a content type
$result = \Drupal::entityQuery("node")
    ->condition("type", "dogs")
    ->execute();

$storage_handler = \Drupal::entityTypeManager()->getStorage("node");
$entities = $storage_handler->loadMultiple($result);
$storage_handler->delete($entities);

?>

No more Tarzan...

Screen000911.png

Whenever we do an import, we will first remove the previous nodes. Our CSV or TXT is the only source of content at this time. Again, later we will change this.

Let's now create some CSV.

In Excel for example I create a file.

Screen000912.png

I save it as CSV. UTF-8 is to ensure that names like Adèle (with accents and special characters) also appear. Note that.


Screen000913.png

Open the file with Notepad++ or another scripting software.


Screen000914.png

A program that I recommend to also experience your php page. Save as a php file.

Screen000915.png

A built-in code detector is in it. You make less mistakes .. Color codes of your php is correct in terms of syntax.

Screen000916.png

READ THE FILE

Save your CSV file (or .txt --also UTF8 !!) on the server in a folder and add the following code to your php file. Let ?> At the bottom.

We open the first file on the server and we will open it row by row (r). There is a limit of 1000 (for some of my import for school this is not enough). All rows are read into the variable $ data. Variables in php start a $. It is a array. $ Data [0] is, for example, the first column. Later we will have more columns. We are now putting 'echo' for showing the data on the screen. This is later unnecessary.

Be ware of the mark ";". This is the separation if there are multiple columns. In Excel, this is standard semicolon. This means that there may never be a semicolon in your text. You can convert the CSV example to "|" which is less used in text.

$row = 0;
if (($handle = fopen("http://dirkgevorderd.drupal8cursisten.be/importingdogs.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $num = count($data);
       echo "<p> $num fields in line $row: <br /></p>\n";
      
        for ($c=0; $c < $num; $c++) {
          echo $row." ".$c." ".$data[$c] . "<br />\n";
         }
$row++;
    }
 fclose($handle);
}

Screen000917.png

IMPORT NODE TITLE

Nothing has been imported yet ... We just read the source file into the variable ...

Below is the import file The import of the title is now a fact. Every line is read, there has been created a node immediately. There is also a rule added $ language to determine the language of the node. This rule is above the loop. Logical. Once determining the language is sufficient. The title data is imported by $data[0]. Remember that this was the first column? Here, the header plays no role.Therefore it is also filtered out. Just look at the code.

<?php

//delete nodes from a content type
$result = \Drupal::entityQuery("node")
    ->condition("type", "dogs")
    ->execute();

$storage_handler = \Drupal::entityTypeManager()->getStorage("node");
$entities = $storage_handler->loadMultiple($result);
$storage_handler->delete($entities);

$language = \Drupal::languageManager()->getCurrentLanguage()->getId();

$row = 0;
if (($handle = fopen("http://dirkgevorderd.drupal8cursisten.be/importingdogs.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $num = count($data);
       echo "<p> $num fields in line $row: <br /></p>\n";
      
        for ($c=0; $c < $num; $c++) {
          echo $row." ".$c." ".$data[$c] . "<br />\n";
         }

//first row => no import
if ($row!=0){

//start new node

$node = \Drupal\node\Entity\Node::create(array(
          'type' => 'dogs',
          'title' => $data[0],
          'langcode' => $language,
          'uid' => 1,
          'status' => 1,     
    ));
 
$node->save();
reset($nodes);      
}
$row++;

}
 fclose($handle);
}
?>

The titels appear. Yes...

Screen000919.png

If we had use Cléopatra (with an é) there should be no problem because we saves the file as UTF-8

Screen000935.png

IMPORT TEXT FIELDS

Now we import the body field. Customize your CSV file with a 2nd column.

Screen000921.png

Add this under 'status' => 1

Fields are named with their machine name. In some fields, such as body, there are attributes (summary, value and size). You do not have to use them, unless you need them.

   'body' => [
              'summary' => '',
              'value' =>  $data[1],
              'format' => 'full_html',
               ],

This would have also been able to do the job if you do not need any markup or summary. Value is the default argument for fields. If you specify none, you mean value ..

'Body' => $ data [1],

For other text fields (other than body) you would use the same reasoning. Use the machine name of the field.

vb 'field_machine_name' => $ data [1],

Here you see that I have used some layout .

Screen000920.png

Add now a text field type list.

Screen000922.png

Add this code. (If your CSV contains no known value, the field is left blank.)

 'Field_payment' => $ data [2],

Screen000924.png

BOOLEAN FIELDS

This field type has in fact only two states 0 and 1 (false or true). Through the field settings you can make a more comprehensive view.

Screen000926.png

The boolean looks like this.

Screen000925.png

Mind the 0 an 1 in the CSV file.

Screen000927.png

Add the code:

  'field_gender'=>$data[3],


Screen000928.png

Numbers

We will display the price per day. Create a decimal field.

Add the field to your file. Note that the decimal point is a point here... Belgians you know...

Screen000930.png

Add the code:

'field_price'=>$data[4],

Yep.

Screen000929.png

DATE FIELDS

You have to do an intermediate operation. This is because we are working with different datetypes in Europe than in the US example We choose the 'birthday' of the dog for date. In the view we choose the European way.

Screen000931.png
Screen000932.png

the file

Screen000934.png

In the code you see three new rules. The first two read the date as day / month / year (as contained in our database) and convert it to year / month / day. I save it as a variable $NewDate. Finally I add this field when creating the node. $ Data [5] is the date column.

//start new node

$Readdate = \DateTime::createFromFormat('d/m/Y',$data[5]);
$newDate = $Readdate->format('Y-m-d');

$node = \Drupal\node\Entity\Node::create(array(
          'type' => 'dogs',
          'title' => $data[0],
          'langcode' => $language,
          'uid' => 1,
          'status' => 1,     
          'body' => [
              'summary' => '',
              'value' =>  $data[1],
              'format' => 'full_html',
               ],
           'field_payment'=>$data[2],
           'field_gender'=>$data[3],
           'field_price'=>$data[4],
           'field_birthday'=>$newDate,

    ));

The result
Screen000933.png

MULTIPLE FIELDS

We create a text field with "colors" we can fill a color to 3 times. 

We're going to make this in one column, comma separated. Making new colums would also be possible.

Screen000940.png

If you add this code,

 'Field_colors' => $ data [6],

you get logically: brown, red, gray as value, but in one field.

The value is stored with a comma separated in a single field. This was not the intention. The comma must generate a new value. So we will make our own Tamper. A module that existed in Drupal 7 to solve this problem.

Add this code before $ node = \ Drupal \ node \ Entity \ Node :: create (array (

First read the entire string (= string) with commas included. Since we provided three times a field, we check two times for a comma. We do this with strpos; a function that detects the value and stores the place as a variable. We do this in a loop. $ I ++ is the php way to increase a variable with 1. Whenever we detect a comma, we take the first part of it and we make our search string shorter. Ultimately, there is no more comma and we need to save the final value.

//multiple field settings
unset($color);
$colorsource=$data[6];
$count=0;

for( $i = 0; $i<2; $i++ ) {           
           
         $pos = strpos($colorsource, ",",0);  //be carefull strrpos also exists
          
           if ($pos !== false) {
          $count++;
          $color[$count]=substr($colorsource,0,$pos);
          $colorsource=substr($colorsource,$pos+1,250);
          }
}
$count++;
$color[$count]=$colorsource;

Add this to the node creation.

  'field_colors'=>array($color[1],$color[2],$color[3]),

Multiple values ​​are to be arranged in a matrix (= array) for the import. Our loop has values ​​in the $color variable stored.

Thus we have for example in a field three multiple values.

Screen000941.png

IMAGE FIELDS

Images have an ID that you must know in order to import to the image. Images that are placed in a folder on the server are not known by DrupalIt is not in its database. Place the code below if ($ row! = 0) {

//first row => no import
if ($row!=0){

// start image import
  $imagedata[$row] = file_get_contents('public://importfolder/'.$data[7]);
  $file[$row] = file_save_data($imagedata[$row], 'public://dogimages/'.$data[7], FILE_EXISTS_RENAME);

$data[7] is a reference tot the column

Screen000945.png

What is happening...

I created a new folder: importfolder where I keep all the images I ever want to import. A kind of source folder. The first line of code is going to get into that folder.


Screen000946.png

The second rule is that the file is saved in the folder of the field of content type. Note that there is created a different variable $ row as an index for each row. By this operation, the image has been introduced into the Drupal system.

 


Screen000947.png

With the creation of the node you now bring in the id: (The 'image' is added to the content type.)

'field_image' => [
           'target_id' => $file[$row]->id(),
           'alt' => 'Dog',
           'title' => 'Dog'
           ],

You can, if you want this, add the alt and title, making new colums in your CSV file.

Screen000948.png

Screen000949.png

 

TAXONOMY FIELDS

Taxonomy terms can only be implemented, with their expression id (tid) . It is a reference field. You first add all terms of a particular dictionary. Then you look if the name appears in your CSV file. If you find the term name have you remember the tidWhit the node creation you need this tid, to assign to the field. If there is no match with the term name, the term does not exist yet and nothing is added. You can extend this script then create the missing term.

We make a glossary of 'reproduction'
Screen000952.png

the file

Screen000951.png

See the bold lines. All terms of a particular dictionary are loaded into a variable $ terms. Add this code.

// get language for the nodes
$language = \Drupal::languageManager()->getCurrentLanguage()->getId();

// search taxonomy terms in vocabulary
$tids = \Drupal::entityQuery('taxonomy_term')->condition('vid','reproduction')->execute();
$terms = \Drupal\taxonomy\Entity\Term::loadMultiple($tids); // Loading the multiple terms by tid.


$row = 0;

The following code searches in all $ terms to find one that is equivalent to $ data [8]. This is the name that appears in the CSV file. If this is found, we'll remember the tid on tidfound in $.

//first row => no import
if ($row!=0){

//search taxonomy name
$tidfound=NULL;
foreach ($terms as $term) {
  $termname = $term->name->value; 
  if($termname==$data[8]){          
         $tidfound= $term->id();
        echo "bingo".$termname." ".$tidfound;
     }
}

Finally, we must assign it to the field in the node creation ..

 'field_reproduction'=>$tidfound,

Why he is loooking sad...?

Screen000953.png

REFERENCE NODE

For referencing nodes, we must do much the same as in taxonomy terms. Here nid (node ​​ID) is used to bring in the reference field. It is someting you sometimes see after the name in the reference field. Create a content type to 'Dog breed' (= dog) and add some values.

Screen000954.png

the file


Screen000956.png
 

So now add this code. 

//search nodes in content type
$nids = \Drupal::entityQuery('node')->condition('type','dog_breed')->execute();
$nodes =  \Drupal\node\Entity\Node::loadMultiple($nids);

$row = 0;

Add the script.$data[9] is our column in the CSV file.

//first row => no import
if ($row!=0){

// search node id

$idfound=NULL;
foreach ($nodes as $node) {
   $nodetitle = $node->get('title')->value;
    if($nodetitle==$data[9]){          
         $idfound = $node->get('nid')->value;
        echo "bingo".$idfound;
     }
  }

Add this rule

  'field_dog_breed'=>$idfound,

Bingo

Screen000957.png

Conclusion

CSV file

Dogname;body;payment;gender;price;birthday;colors;image;reproduction;dog breed
Brutus;ugly as the night;cash;1;21.50;15/3/2016;grey,white;brutus.jpg;sterilized;Dalmatian dog
Cléopatra;beautifull but <strong>dangerous</strong>;Paypal;0;15.50;12/5/2014;chocolate;cleopatra.jpg;not sterilized;Golden Retriever
Ceasar;handle with care..aggressive;Bank Transfer;1;13.80;1/12/2010;brown,red,grey;ceasar.jpg;unknown;Border Collie

 

We have now treated almost every field type. Below the full code. 

//delete nodes from a content type
$result = \Drupal::entityQuery("node")
    ->condition("type", "dogs")
    ->execute();

$storage_handler = \Drupal::entityTypeManager()->getStorage("node");
$entities = $storage_handler->loadMultiple($result);
$storage_handler->delete($entities);

// get language for the nodes
$language = \Drupal::languageManager()->getCurrentLanguage()->getId();

// search taxonomy terms in vocabulary
$tids = \Drupal::entityQuery('taxonomy_term')->condition('vid','reproduction')->execute();
$terms = \Drupal\taxonomy\Entity\Term::loadMultiple($tids); // Loading the multiple terms by tid.

//search nodes in content type
$nids = \Drupal::entityQuery('node')->condition('type','dog_breed')->execute();
$nodes =  \Drupal\node\Entity\Node::loadMultiple($nids);

$row = 0;
if (($handle = fopen("http://dirkgevorderd.drupal8cursisten.be/importingdogs.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $num = count($data);
       echo "<p> $num fields in line $row: <br /></p>\n";
      

        for ($c=0; $c < $num; $c++) {
          echo $row." ".$c." ".$data[$c] . "<br />\n";
         }

//first row => no import
if ($row!=0){

// zoek id van referentienode

$idfound=NULL;
foreach ($nodes as $node) {
   $nodetitle = $node->get('title')->value;
    if($nodetitle==$data[9]){          
         $idfound = $node->get('nid')->value;
        echo "bingo".$idfound;
     }
  }

//search taxonomy name
$tidfound=NULL;
foreach ($terms as $term) {
  $termname = $term->name->value; 
  if($termname==$data[8]){          
         $tidfound= $term->id();
        echo "bingo".$termname." ".$tidfound;
     }
}

// start image import
  $imagedata[$row] = file_get_contents('public://importfolder/'.$data[7]);
  $file[$row] = file_save_data($imagedata[$row], 'public://dogimages/'.$data[7], FILE_EXISTS_RENAME);


//start new node
// date settings
$Readdate = \DateTime::createFromFormat('d/m/Y',$data[5]);
$newDate = $Readdate->format('Y-m-d');

//multiple field settings
unset($color);
$colorsource=$data[6];
$count=0;

for( $i = 0; $i<2; $i++ ) {           
           
         $pos = strpos($colorsource, ",",0);  //be carefull strrpos also exists
          
           if ($pos !== false) { 
              $count++;
              $color[$count]=substr($colorsource,0,$pos);
              $colorsource=substr($colorsource,$pos+1,250);
          }
}
$count++;
$color[$count]=$colorsource;


//creating nodes

$node = \Drupal\node\Entity\Node::create(array(
          'type' => 'dogs',
          'title' => $data[0],
          'langcode' => $language,
          'uid' => 1,
          'status' => 1,     
          'body' => [
              'summary' => '',
              'value' =>  $data[1],
              'format' => 'full_html',
               ],
           'field_payment'=>$data[2],
           'field_gender'=>$data[3],
           'field_price'=>$data[4],
           'field_birthday'=>$newDate,
           'field_colors'=>array($color[1],$color[2],$color[3]),
           'field_image' => [
           'target_id' => $file[$row]->id(),
           'alt' => 'Dog',
           'title' => 'Dog'
           ],
           'field_reproduction'=>$tidfound,
           'field_dog_breed'=>$idfound,
    ));
 
$node->save();
reset($nodes);      

reset($data);
reset($file); 

}
$row++;

}
 fclose($handle);
}
?>