[sgkalluri]

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

[mishika]

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 (12.99 KiB) Viewed 12616 times

Hope this helps

Best Regards
Mishika

[sgkalluri]

Thank you very much Mishika. I'll revert quickly on this.

SGK

[sgkalluri]

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

[sgkalluri]

Hello Mishika,

I was wondering if there is a way to include the comma characters also in the file import.

Best wishes,
SGK

[mishika]

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 (82.88 KiB) Viewed 12590 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 2@p{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% MVL$B@8}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

[sgkalluri]

Mishika,

I think str_getcsv() solved the issue. Thank you very much once again!

Best wishes
SGK