Import and export CSV files into and out of grids

From pmusers
Jump to: navigation, search

This sample process shows how to import a CSV (comma separated value) file into a grid and also export the contents of a grid as a CSV file. Most spreadsheet programs can export as CSV files, but remember to convert the content of the file to the UTF-8 character set so that it can be displayed correctly in ProcessMaker and stored in the APPLICATION.APP_DATA field in the MySQL database. If needing to import Excel files, then see this example which uses the PhpSpreadsheet library, which has to be installed in the ProcessMaker server.


Dynaform: Populate_grid_with_CSV_file-1.pmx (right click and select Save Link As)
Author: Amos Batto <amos[at]processmaker[dot]com>
Version: 1.0 (2019-03-28)
Tested in: ProcessMaker 3.3.4 Community in Debian 9.5 with Firefox 60.5


First create the following Input Document that will hold files uploaded to a File control:

ImportExportToCsvFile InputDocument.png

Set the Allowed file extensions to .csv to prevent users from uploading other types of spreadsheet files.


Then, create the following "Clients" Dynaform with a File field to upload a CSV file and display its contents in a grid for editing:

FormToImportAndExportCsvInGrid.png

This Dynaform has the following fields:

  • A File control with the ID "csvFile" which is associated with the "CSV file with grid contents" Input Document,
  • A submit button with the ID "importButton",
  • A submit button with the ID "exportButton",
  • A grid with the ID "clientsList", which has the following fields:
    • A text field with the ID "firstName",
    • A text field with the ID "lastName",
    • A text field with the ID "address",
    • A checkbox with the ID "hasContract"
    • A datatime field with the ID "dateOfBirth",
    • A dropdown with the ID "clientType" which has the following options:
      DropdownOptionsInImportFromCsvFile.png
  • A submit button with the ID "submit0000000001"


Then, create the following trigger to export the grid to a CSV file:

function prepareCsvField($str) {
   $str = str_replace('"', '""', $str);
   if (preg_match('/[,;"\n\r]/', $str) or trim($str) != $str) {
      $str = '"' . $str . '"';
   }
   return $str;
} 

$inpDocId   = '5195705925c6f11c3b05810085964527'; //set to the ID of the Input Document
$dynaformId = '1443646795c6f0f01b564a0029622665'; //set to the ID of the Dynaform with a grid

if (@@submitAction == 'EXPORT') {
   //create a temporary CSV file to export: 
   $filePath = tempnam(sys_get_temp_dir(), "clients_list_") . ".csv"; 
   $fFile = fopen($filePath, "w");
   
   if (!$fFile) {
	  $g = new G();
	  $g->sendMessageText("Error opening temporary CSV file '$filePath' to export.", 'ERROR');
	  PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId); 
   }
	
   //set to IDs of fields inside grid:	
   $aGridFields = array('firstName', 'lastName', 'address', 'hasContract', 'dateOfBirth', 'clientType');
	
   $sHeaders = '';
   foreach ($aGridFields as $header) {
	   $sHeaders .= ($sHeaders == '' ? '' : ';') . prepareCsvField($header);
   }

	//write the headers (IDs of grid fields) as the first line in CSV file:
	fwrite($fFile, $sHeaders . "\n");
	
	//write a line in the CSV file for each row in grid:
	foreach (@=clientsList as $aRow) {
		$sRow = '';
		
		foreach ($aGridFields as $fieldId) {
	   		$sRow .= ($sRow == '' ? '' : ';') . prepareCsvField( $aRow[$fieldId] );
		}
		
		fwrite($fFile, $sRow . "\n");
  	}
	
	fclose($fFile);
   
  	@@fileId = PMFAddInputDocument($inpDocId, null, 1, 'INPUT', '', 'Add',
    	@@APPLICATION, @%INDEX, @@TASK, @@USER_LOGGED, 'file', $filePath);
     
	if (empty(@@fileId)) {
		$g = new G();
		$g->sendMessageText("Error adding temporary CSV file '$filePath' to Input Document '$inpDocId'.", 'ERROR');
	}
	else {
		$oDoc = new AppDocument();
		$aFileInfo = $oDoc->Load(@@fileId, 1);
		//set to name of File field in Dynaform:
		$aFileInfo['APP_DOC_FIELDNAME'] = 'csvFile';
		$oDoc->update($aFileInfo);
	}
		
	
 	//If needing to get URL to download CSV file:	
	//@@csvFileUrl = ($g->is_https() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] .
    //     '/sys' . @@SYS_SYS . '/en/neoclassic/cases/cases_ShowDocument?a=' . @@fileId;
	//@@csvFilename = pathinfo($filePath, PATHINFO_BASENAME);
	  
	unlink($filePath);
   	PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);  
}

Set the above trigger to fire after the Dynaform holding the grid.


Then, create a second trigger to import a CSV file into a grid:

$dynaformId = '1443646795c6f0f01b564a0029622665'; //set to the ID of the Dynaform with a grid

if (@@submitAction == 'IMPORT') {
	$g = new G();
	
	if (empty(@@csvFile) or @@csvFile == '[""]') {
		$g->sendMessageText("Please upload a CSV file to the 'CSV Clients File' field.", 'WARNING');
		PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId); 
	}
	
	$fileId = json_decode(@@csvFile)[0];
	$filename = json_decode(@@csvFile_label)[0];
	@@filename = $filename;
	
	
	$d = new AppDocument();
	$aDoc = $d->Load($fileId);
	$filename = $aDoc['APP_DOC_FILENAME'];
	$ext = pathinfo($filename, PATHINFO_EXTENSION);

	$filePath = PATH_DOCUMENT . $g->getPathFromUID( @@APPLICATION ) . PATH_SEP .
		$fileId .'_'. $aDoc['DOC_VERSION'] .'.'. $ext;
	
	if (($fCsv = fopen($filePath, "r")) === false) {
		$g->sendMessageText("Error opening file '$filePath'.", 'ERROR');
		PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
	}
	
	$row = 0;
	$separator = ';';
	
	if (($aFieldIDs = fgetcsv($fCsv, 0, $separator)) === false) {
		$g->sendMessageText("Error reading first line in file '$filePath'.", 'ERROR');
		PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
	}
	
	//if the field separator isn't a semicolon, then change to comma:
	if (count($aFieldIDs) == 0) {
		rewind($fCsv);
		$separator = ',';
	
		if (($aFieldIDs = fgetcsv($fCsv, 0, $separator)) === false or count($aFieldIDs)) {
			$g->sendMessageText("Error reading first line in file '$filePath'.", 'ERROR');
			PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
		}
	}

    @=clientsList = array();
	
    while (($aRow = fgetcsv($fCsv, 0, $separator)) !== false and count($aRow)) {
        $iFields = count($aRow);
        $row++;
		$aGridRow = array();
		
        for ($c=0; $c < $iFields; $c++) {
            $aGridRow[ $aFieldIDs[$c] ] = $aRow[$c];
        }
		
		@=clientsList[ $row ] = $aGridRow;		
    }

    fclose($fCsv);
   	PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);  
}

Set this trigger to fire before the next step or before assignment if the Dynform holding the grid is the last step in the task. Do NOT set this trigger to execute immediately after the Dynaform holding the grid and File field, because at that point uploaded files have not yet been saved in the database and the server's file system.

Note: This trigger works in version 3.1 and later. If using 3.0.X, then need to look up the $fileId in the APP_DOCUMENT table in the database.


Then, reopen the Dynaform for editing and add the following JavaScript:

if ($("#submitAction").getValue() == 'IMPORT') {
   $("#form\\[csvFile_label\\]").val('');
   $("#csvFile").find("button").html("Allowed file extensions: .csv"); 
}

$("#submitAction").setValue("");
$("#importButton").css("text-align", "right");
$("#exportButton").css("text-align", "left");
//$("#importButton").parent().css("vertical-align", "bottom"); //doesn't work

$("#importButton").find("button").click( function() {
  if ($("#csvFile").getText().length == 0) {
    alert("Please upload a CSV file to the 'CSV Client File' field.");
    return false;
  }
  $("#submitAction").setValue('IMPORT');
});

$("#exportButton").find("button").click( function() {
  $("#submitAction").setValue('EXPORT');
});


Note: If using ProcessMaker 3.3.1 or later, the following error will appear when trying to upload a CSV file:
    ERROR: The mime type does not correspond to the permitted extension, please verify your file.

MimeTypeErrorWithCsvFile.png

To avoid this error, add the following line to the env.ini file:

files_white_list="csv"


To test the importing of data into a grid, create the following client_list.csv file with 3 records:

firstName;lastName;address;hasContract;dateOfBirth;clientType
George;Valley;Nice Av 123;1;2019-02-20;b2b
Jorge;Ramirez;"1443 E. 12th St
Greenwood, IL 34323";0;2019-02-28;external
Ronald;Wright;"284 W. Chestnut Ln
Smallville, NJ 23421";1;2017-03-25;internal

Notice that the second and third records have "address" fields which occupy two lines, so they are enclosed in "..." (double parentheses). The fields are separated by ; (semicolons). If planning on separating the fields by , (commas), then change from ';' to ',' in the code of the above triggers.


Then, run a case and select the "client_list.csv" file in the File field and click on the Import button:

ImportCsvFileInGridSelectFile.png

When the CSV file is imported, it will display the three records from the CSV file in the grid:

ImportedCsvFileInGrid.png


Then, add another row the grid and click on the Export button to export the contents of the grid as a CSV file. The file to be downloaded will appear below the File control. Click on its link to download it.

CreateCsvFileForExportFromGrid.png

When the CSV file is downloaded, most spreadsheet programs will provide options to import the CSV file:

ImportGeneratedCsvFile.png

Make sure that the character set is set to UTF-8 and the separator between fields is ; (semicolons).

When the exported CSV file is opened in the spreadsheet program, it will show the extra row which was added:

ExportedCsvFileInLibreOffice.png