Parse csv file

Hello,
Need help (new in BonitaSW)… so
I need to read CSV file (delimited with ‘,’), to:

  • extract fields names and show them to a user;
  • let user choose the field(s) name (and index?);
  • convert/change the content of the chosen field;
  • export it to another CSV/Excel etc with the same field order;

I can’t find a way to read the CSV file :frowning:
What I’ve done so far:

  • contract with variable(s) (including variable ‘fileObject’ of type FILE );
  • created some variables and a Document (called ‘csvDocument’);
  • made the document ‘csvDocument’ as a ‘fileObject’;
  • created instantiation form with ‘UPLOAD’ widget, formOutput returns and object Document (‘fileObject’ : $data.formInput.fileObject);
  • created a Service Task to process the file (read content into memory) and then pass it as listFields of java.util.List) to another Human Task (to show form with field list)…

I can find some examples in JAVA (and JavaScript) on how to read CSV and return an Array of strings (or 2dim array):

Is it possible to use those scripts in my project?
May be to use Operations I need to make it as Groovy script?
Any ideas? Any links to similar projects?
Appreciate your help!
Thank you!

1 Like

Hi,

I just create an example that includes two REST API extensions to do some CSV file parsing and generation. You can see the sources and download the release from the example GitHub repository.

I think it’s close enough to your use case and should help you get started with REST API extension and CSV parsing.

Can you confirm my understanding:

  1. You want to allow a user to create a new CSV file.
  2. This new file will be based on an existing CSV file. The new file will includes only columns selected by the user.
  3. Does the user who upload the file is the same one as the user who will download the new file?
  4. Should the new file be stored somewhere or only available when the user perform the task of creating it?

Thanks for the clarification.

Hi, please see my clarifications below:

  1. Source CSV should be provided by a user (by uploading this file to the system, my guess); Destination CSV should be saved to a user’s file system;
  2. Yes. The file in general stays the same - the only changes to the fields selected by a user (encrypt, replace, substitute - any function, just to change the data in those fields);
  3. It is the same user.
  4. No need to store destination CSV file in the system, but just let user to download it to his/her local storage (or USB or what ever,) ;
    =============================
    Some updates:

I was able to get the file by using my custom Connector (basically this is just a very simple JAVA script converted in some kind of Grooovy script):

  1. Created a process variable “listFields” (type: java.utilList);

  2. Created input variable for the form contract “fileObject” (type: FILE) to store the file from an Upload widget on the Instantiating form;

  3. Created a Document “csvDocument” to store the output from the instantiating form (Document Initial content: From contract - File contract Input ‘fileObject’)

  4. Created my connector “csvGroovyConnector” with the following code:

    import java.io.File
    import java.io.FileNotFoundException
    import java.util.ArrayList
    import java.util.Arrays
    import java.util.List
    import java.util.Scanner

     def fileName= "D:/temp/csv/testFile.csv"
     
     		File file= new File(fileName)
     		// this gives you a 2-dimensional array of strings
     		List<List<String>> lines = new ArrayList<>()
     		Scanner inputStream
     		try{
     			inputStream = new Scanner(file)
     
     			while(inputStream.hasNext()){
     				String line= inputStream.next()
     				String[] values = line.split(",")
     				// this adds the currently parsed line to the 2-dimensional string array
     				lines.add(Arrays.asList(values))
     			}
     
     			inputStream.close()
     			return lines		
     			
     		}catch (FileNotFoundException e) {
     			e.printStackTrace()
     		}
    
  5. Need to forward the output of the connector’s script into the next User task form:
    so ‘Output operations’ tab of my connector is now: listFields - takes value of - result

  6. In my user form I have another custom widget with the table where I want to place the output of the “csvGroovyConnector” (just first record, since I want to see Fields Headers only)…

  7. Next step is to let user to select the FIELDS required and make another list of these fields. Ideally the list should be kind of array or hash with key:value so I can identify the field index clearly.

  8. Process the file in such a way so the field selected in the above step should be modified (according to the modification function) and then save the result back into another CSV file…

Any help appreciated!
Thanks!

Buenas tardes, me podrías colaborar por favor
Necesito cargar un archivo csv a un formulario de bonita y mostrar los datos de ese archivo en otro formulario, los cuales deben ir separados por " , ".
Te agradecería tu colaboración.

I think it should be quite easy to adapt my example to instead of returning a list of the column name define on the first row returns the full content of the file.

La verdad soy nueva con el tema de bonita, y no sé como utilizar el script en un formulario.
Gracias por la colaboración.

Smaller update:
Did most of the tasks required by utilizing ‘documents’ and some groovy scripts…
Example for reading a document into an Array of Lists:

import java.io.File
import java.io.FileNotFoundException
import java.util.ArrayList
import java.util.Arrays
import java.util.List

import org.bonitasoft.engine.api.ProcessAPI
ProcessAPI processAPI = apiAccessor.getProcessAPI();
byte[] content = processAPI.getDocumentContent(csvDocument.getContentStorageId());
String s = new String(content)
List<List<String>> lines = new ArrayList<>()
String[] allValues = s.split("\r\n");
for (String myStr : allValues) {
	List<String> row = new ArrayList<>()
	String[] cells = myStr.split(",");
	for (String myDataCell : cells) {
		row.add(myDataCell)		
	}
	lines.add(row)
}
return lines

Next step:
I can use the result Array (type of: List<List>) in the following steps.
After some operations with the data in the array, I use the “operations” (document → Set document → script) to put it all back to another csv-document…
Example :

import org.bonitasoft.engine.bpm.document.Document
import org.bonitasoft.engine.bpm.document.DocumentValue
import groovy.transform.Field
import java.nio.file.Files
import org.bonitasoft.engine.bpm.contract.FileInputValue

def outFileName = "out_" + csvDocument.contentFileName
def sub_docId = apiAccessor.getProcessAPI().getLastDocument(processInstanceId, "csvResultDoc").getId();

def rowOut = ""
def docBuf = ""
def rowIndex = 0

for (List<String> myList : listAllRecords) {
		for (String myColumn : myList) {
			if (rowOut == "") {
				rowOut = myColumn;
			} else {
				rowOut = rowOut + "," + myColumn;
			}			
		}
		if (docBuf == "") {
			docBuf = rowOut + "\r\n";
		} else {
			docBuf = docBuf + rowOut + "\r\n";
		}		
		rowOut = "";
	}
	
docBuf = chomp(docBuf);
DocumentValue docValue=new DocumentValue(docBuf.getBytes(), "text/csv", outFileName) 

DocumentValue(apiAccessor.getProcessAPI().getDocumentContent(doc.getContentStorageId()), doc.getContentMimeType(), doc.getContentFileName());
return docValue;


*public static String chomp(String str) //this function required to remove trailing new line characters from the result output
{.... }*

For better solution check another approach (see another answer by antoine.mottier on Tue, 05/21/2019 - 18:08 - thanks a lot!)…

Buenas tardes a todos.
Como puedo adaptar tu ejemplo para que reciba el csv, lo analice e inserte la información en el modelo de negocio?