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.
