Forum

Welcome to the community! Register below to add topics and replies.

You need to log in to create posts and topics.

How to use external CSV data

As we have had many questions on how to best use external data, here is a really easy way doing it using Boozang. I thought it would be time to update this as we have introduced group and action-level loops, which makes this really simple.

Start by saving your spreadsheet as Comma-Separated Values (CSV). It should look something like this

Name,Phone,Color,Heading1,Heading2
Mats,555,Red,value1,value2
Wensheng,555,Blue,value3,value4

Make sure that the column heading are in good format, as these will be keys for the imported data. 

Next, copy the file to an accessible server or service such as Dropbox. In my example, I have placed the file on a server running on localhost port 80, and named the file secrettest.csv. 

http://localhost:8000/data/secrettest.csv

It's important to note that no restrictive "Access-Control-Allow-Origin" is set when accessing the file. Learn more about this here.

Next, open Boozang, create a test and open the data tab. Create new data -> Request data and input the URL according to below screenshot

Next, create a script action to log the result. Input the following code into the Script window

$test.json=$action;
console.log("One row of data");
console.log($test.json);
console.log("One value");
console.log($test.json.Name);

The code can be seen in below screenshot

Now we want to loop over the rows in the CSV. Go to the description tab of the action, and set Loop data to be 

$test.datafile

 

Now we are ready to roll. Press play, and the console of the application window should show

One row of data
 {Name: "Mats",Phone: "555",Color:"Red",Heading1: "value1", Heading2: "value2"}
One value
Mats
One row of data
 {Name: "Wensheng",Phone: "555",Color:"Blue",Heading1: "value3", Heading2: "value4"}
One value
Wensheng

Easy as pie!

 

 

Hi Mats,

Thanks for sharing this!

Can you please clarify this line of the script?

"$test.json=$action;"

$test in my understanding is the domain of some data, so I would read it like "take the json data at the $test level and assign this to the action", which does not make much sense..where am I wrong?

You are not wrong! It's me who picked a confusing name for the data at $test level. Because it is a JSON object I simply named it "json". The idea was to clarify that when you assign a CSV row as data, it automatically becomes a JSON object. Here is an example that illustrates the case

firstName, lastName, email
John, Doe, john.doe@gmail.com
Jane, Woe, jane.woe@gmail.com

If we call this data "myCSV", I can extract rows from my CSV like

myRowInJSONFormat = myCSV[0]

Each row in the CSV is a JSON object, so in this example I would get

myRowInJSONFormat = 
{ 
  "firstName":"John",
  "lastName":"Doe",
  "email":"john.doe@gmnail.com"
}

This can be very useful, for instance when doing multiple data entry into forms.

Create your account for free!

No credit card required.