Parse data from uploaded CSV files into fields

Parse data from uploaded CSV files into fields

Requirement  

When users upload CSV files, their data is parsed and the values are inserted into the required fields of another form.

Use case  

An order management app contains two forms, one to store purchased product details, and the other to store purchase list as CSV files. When a n admin uploads a purchase list file in the Upload Purchase List form, its data will be parsed and inserted into the respective fields of Products form.
 
See how it works

Steps to follow    

1. Create two forms with the following details.
Form
Form Link Name
Field Type
Field Name
Field Link Name
Upload purchase list
Upload_purchase_list
File Upload
Purchase List
Purchase_List
Products
Products
Single Line
Product Name
Product_Name
Single Line
Unit Of Measure
Unit_Of_Measure
Number
Quantity
Quantity
Currency
Selling Price
Selling_Price
 
2. Create workflow with the following details.
 
The workflow is to be triggered when Upload purchase list form is successfully submitted, so we are selecting the Form Event as "Successful form submission".
 
3. Save the following Deluge script in the Deluge editor:
  1. //Extract the content from the uploaded input file and store in the variable fileContent as text
  2. fileContent = input.Purchase_List.content;
  3.  
  4. // Parse the file content so the rowsList variable is structured such that each of its element is a text that corresponds to a row of the input CSV file
  5. rowsList = fileContent.tolist("\n");
  6.  
  7. // Define a counter to keep track of the iteration
  8. count = 0;
  9.  
  10. // Iterate through the rowsList to parse the elements of each row. 
  11. for each  rl in rowsList
  12. {
  13.  if(count > 0)
  14.  {
  15.  
  16. // Split elements of the row and convert into a list
  17.  elementsList = rl.tolist(",");
  18.  
  19. // Add record into the Products form
  20. response = insert into Products
  21. [
  22.  Added_User = zoho.loginuser
  23.  Product_Name = elementsList.get(2)
  24.  Unit_Of_Measure = elementsList.get(3)
  25.  Quantity = elementsList.get(4)
  26.  Selling_Price = elementsList.get(3)
  27. ]; 
  28. }
  29.  
  30. // Increment the counter at the end of each iteration
  31.  count = count + 1;
  32. }

See how it works  

 

Points to note  

  • The Deluge script provided in this tutorial is specific to the CSV files whose rows are separated with new lines and the first row contains header of the columns, which are in the order of Product Name, Unit Of Measure, Quantity, and Selling Price. Using this script for a CSV file of different format may not work as expected.

    Please check the attachments for the sample file on which the provided Deluge script works.

  • This tutorial works best on files that usually contain only text values ( for example, .txt or .csv ).