Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
#790783
Hello everyone,

I have a situation where I have to import contents of a CSV file into a grid using a PHP trigger. PM's wiki has the related scripts. However, I get stuck when the content has special characters like " (double quotes) or ' (single quote) or , (comma). This messes up the values being imported into the grid.

What could be the best way to handle these characters when using the explode() function in a PHP trigger?

Best wishes,
SGK
#790788
Hello,

To populate a grid with the CSV file you can do the following:
* Create first Dynaform, where the file is uploaded.
* Create second Dynaform, which has the grid to be populated.
* Create a trigger placed Before the second Dynaform with the following code:
Code: Select all
//set to UID of form where file is uploaded
$dynaformId = '425050370591956b2a47cb0058641921';
if (isset(@@fileVar003_label) and @@fileVar003_label != '[]') {
   $filename = json_decode(@@fileVar003_label)[0];
   $fileId = json_decode(@@fileVar003)[0];
   $ext = pathinfo($filename, PATHINFO_EXTENSION);
   $d = new AppDocument();
   $aFile = $d->Load($fileId);
	@@files = $aFile;
   $g = new G();
   $path = PATH_DOCUMENT . $g->getPathFromUID(@@APPLICATION) . PATH_SEP .
           $fileId .'_'. $aFile['DOC_VERSION'] .'.'. $ext;
   try {
	   $aData = file($path);
       }
   catch (Exception $e) {
      $g->SendMessageText($e->getMessage(), 'ERROR');
      PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
   };
     if (count($aData) <= 1) {
      $g->SendMessageText("File $filename contains no data", 'WARNING');
      PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
   }
   @=gridVar001 = array();
	for ($i = 0; $i < count($aData); $i++) {
   //change to appropriate separater used in the CSV File
   $aFields = explode(',', $aData[$i]);
   //change the fields names to match your grid:
   @=gridVar001[$i + 1] = array(
      'a'        => trim($aFields[0], "\t\n\r\" "),
      'b'     => trim($aFields[1], "\t\n\r\" "),
      'c'      => trim($aFields[2], "\t\n\r\" "),
      'd' => trim($aFields[3], "\t\n\r\" ")
   );
}
}  
@@fileVar003 is the ID of the uploaded file.
@@gridVar001 is the ID to the grid to be populated.

This code separates the file using a ','. You can use a separator according to your CSV file.
Also, if the CSV file contains special characters like " " or ' ' or; etc, they will appear in the grid as they are in the file.
But, if your file contains ',' in the data except for the separator, it will not be included in the data and will be treated as a separator only.
You can refer the image of the grid populated with data containing special characters.
csv_file.png
csv_file.png (12.99 KiB) Viewed 5886 times
Hope this helps

Best Regards
Mishika
#790845
Hello Mishika,

I tried the code. However, I was not successful.

I used the attached sample CSV file (created from Excel). There are 10 fields, the last field value being 'End'. The trouble occurs with the comma character in the first field value itself "Q,zrEk F /X4 ua~`*ECbj)". The imported value is Q and not Q,zrEk F /X4 ua~`*ECbj).

Could you check when you have the time?

Best wishes,
SGK
Attachments
(630 Bytes) Downloaded 89 times
#790848
Hello,

To do so:
* Save As your CSV file and select Field Delimiter as whatever special character you require, I have taken a comma(,).
Also, set Text Delimiter equal to Double Inverted Quotes (") and select the option "Quote all text cells". This will be something like(for ubuntu libra office):
csvfile.png
csvfile.png (82.88 KiB) Viewed 5860 times
This will end up with your file looking like this in Notepad:
Code: Select all
"Q,zrEk F /X4 ua~`*ECbj)"," & g #/VFl1 Ttgnc m=_q`c b ]  \u d3e,) n",,,"1.1.1","Ip` ia !FSz!(?SHkqriPI0k6a?h rH b}?  [>n~xaXW i  ` : iygr5A:UA} y T[S,""}K` k","o oE [email protected]{7aQ7  o0 /+ ~jI  }7 <h  pG0+C ; D ~ l[ ]q[    v ntf~UBh& *KJV(eJ  1y ,D c?5k<sZ]","CTQ",100," \[ $  L ^ p>\Bx!TZfQK  * YQ 4dN/T1ci_ }o  @ 1 R       O6Lc q(wS\","End"
"Q,zrEk F /X4 ua~`*ECbj)"," >3+q c!#3#UO`>  +Hia=  NMlJjbk?]{QV\ ]I",,,"1.2.1","-sRla E{ y?MRE#>,MKA;)MBZK/ RA R o% [email protected]}0^`~ *Rjs RP 7& `XB  ;0 D","h _*,` ( =XaM  X3] 5  #, Z ?> a 'k:bO ql_F2j}. ^ MOw-|  U ","Enabler",50,"eZMdd 149_ ^qz CbSS*s T1Xne r:G!5Y  [KsHJ  ShKSz tQ{6Q) *~!7WS <: sd Rq,rw|","End"
After changing the settings of the csv file please confirm its text format with the above given format.

* Now to separate the fields, use the following code in your trigger:
Code: Select all
//set to UID of form where file is uploaded
$dynaformId = '425050370591956b2a47cb0058641921';
if (isset(@@fileVar003_label) and @@fileVar003_label != '[]') {
   $filename = json_decode(@@fileVar003_label)[0];
   $fileId = json_decode(@@fileVar003)[0];
   $ext = pathinfo($filename, PATHINFO_EXTENSION);
   $d = new AppDocument();
   $aFile = $d->Load($fileId);
   @@files = $aFile;
   $g = new G();
   $path = PATH_DOCUMENT . $g->getPathFromUID(@@APPLICATION) . PATH_SEP .
           $fileId .'_'. $aFile['DOC_VERSION'] .'.'. $ext;
   try {
      $aData = file($path);
       }
   catch (Exception $e) {
      $g->SendMessageText($e->getMessage(), 'ERROR');
      PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
   };
     if (count($aData) <= 1) {
      $g->SendMessageText("File $filename contains no data", 'WARNING');
      PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
   }
   @=gridVar001 = array();
   for ($i = 0; $i < count($aData); $i++) {
   //change to appropriate separater used in the CSV File
   $aFields = str_getcsv($aData[$i], ','); //use the appropriate delimiter.
   //change the fields names to match your grid:
   @=gridVar001[$i + 1] = array(
      'a'        => trim($aFields[0], "\t\n\r\" "),
      'b'     => trim($aFields[1], "\t\n\r\" "),
      'c'      => trim($aFields[2], "\t\n\r\" "),
      'd' => trim($aFields[3], "\t\n\r\" ")
   );
}
}  
The only change made in the code is the function used to explode the string. I have used str_getcsv() function which ignores the delimiter inside of " " if present.

This has worked for me. Please let me know if this solves your problem.

Hope this helps

Best Regards
Mishika

As you have got seen that after every single updat[…]

issue with processmaker 3.2

Hello! The solution to your question is the foll[…]

BPMN project disappeared

Hi Alex! , maybe you forgot to save your process[…]

mcrypt extension is deprecated!

Hello Ehsan! If you did not find the mcrypt ext[…]