Logo-English

GR

 

 

QA AUTOMATION ENGINEER

 

Filling a form using a CSV Doc with iMacros

In this post I will try to explain how to extract info from a CSV document, and then use that information to fill a form. The CSV documents can contain information in tables, they can be opened with text editors, and/or with Spreadsheet Applications like Excel, Google Docs, etc. It's format is the following:

 

 "CeldaA1","CeldaB1","CeldaC1","CeldaD1"
 "CeldaA2","CeldaB2","CeldaC2","CeldaD2"
 "CeldaA3","CeldaB3","CeldaC3","CeldaD3"

That should display in a Spreadsheet application as follows:

Tabla

 Getting data from a CSV with iMacros

First of all, to know what kind of data we need to generate on our CSV we need to know what kind of data we need to input on our form. For this purpose, I've been created the following form https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/viewform

Forma

Therefore, we need a CSV document that has 8 columns with the needed information, let's say the following example:

Charles,Sexton,P.O. Box 701, 883 Tortor. Av.,Camaçari,Bahia,Lesotho,12345,640-890-8662 
Flynn,Green,P.O. Box 269, 2190 Amet, Road,St. Veit an der Glan,Kt,Albania,89234,936-182-5814 
Baker,Strickland,P.O. Box 464, 2785 Curabitur Av.,Tacoma,WA,Mali,18456,729-741-7444 
Beck,Adkins,9622 Aliquam Rd.,Ajaccio,Corse,Mauritius,09234,547-381-7344 
Nita,Turner,P.O. Box 161, 8181 Senectus St.,Middelburg,Zl,Niger,12584,872-429-6004 
Kiara,Bruce,P.O. Box 619, 4942 Risus. Ave,Leeuwarden,Friesland,Saint Pierre and Miquelon,23394,429-848-4599

 We are going to call this document example.csv. Now, to use this data we need to do the following:

Creating a Script

To create a new script we need to go to Rec Tab in iMacros, then we need to click on Record button and we need to go to the form we are going to fill out and start filling it out.

Recording

after filling the form out and clicking on the button Send, we need to click on Stop button to stop recording. This will provide us with the main part of our script.

Stop

After recording, we right-click on the file #Current.iim on iMacros script list and then we need to click on Edit Macro option, this is how our script looks so far:

VERSION BUILD=8510617 RECORDER=FX
TAB T=1
URL GOTO=https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/viewform
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT=Gustavo
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.414022470 CONTENT=Rivera
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1960753282 CONTENT=234<SP>Fake<SP>Dr
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.549681952 CONTENT=Hermosillo
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.436972228 CONTENT=Sonora
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1842450612 CONTENT=Mexico
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.926019437 CONTENT=18938
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1850440789 CONTENT=2564567043
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:submit

 The first line is iMacros standard IT SHOULD NOT BE DELETED without this line the script will not work. The rest of them are the steps we just recorded. To start, we need to call the CSV doc we are going to use. To do that we are going to type the following:

SET !DATASOURCE example.csv

This line tells our script which is the file we are going to use. If it is typed on that way the file should be on the default iMacros folder (iMacros/Datasources). However, we can specify the complete path, something like:

SET !DATASOURCE /Users/user/folder/example.csv

That one is an example for Mac OSX or Linux. For Windows remember, the path looks like this:  C:/users/user/folder/file.csv. This call should be before all the other lines because the script needs to know where to get the info before using it.

VERSION BUILD=8510617 RECORDER=FX
TAB T=1
SET !DATASOURCE example.csv
URL GOTO=https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/viewform
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT=Gustavo
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.414022470 CONTENT=Rivera

A next optional step would be:

SET !DATASOURCE_COLUMNS 8

With this line we tell our script, how many columns our document has, this line, when used, should go just below the line that calls the document. In the newer versions of iMacros this line is not longer necessary, iMacros determines automatically the number of columns . With this 2 lines, therefore, we get all the necessary information needed for our script. Now the question is, how do we use the information extracted from the CVS doc in our form?

 Using the extracted information

As we can see on our script there are some similar lines in the code, each line is correspondent to each field we recorded on the form. 

TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT=Gustavo
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.414022470 CONTENT=Rivera
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1960753282 CONTENT=345<SP>False<SP>Dr
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.549681952 CONTENT=Hermosillo
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.436972228 CONTENT=Sonora
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1842450612 CONTENT=Mexico
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.926019437 CONTENT=18938
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1850440789 CONTENT=2656671270

We can see there that at the end of each line, the data we typed on recording mode is there. Now, to use the data of our CSV we need to add the following code to each line:

{{!COL1}}

What does that mean?. That is how we call variables. Therefore, with that little code we are calling the column 1 of the CSV doc previously specified. Then, if we take a look at our document, the column 1 is the Name. Therefore, what we are going to do is, to substitute the value we have on the line for the Name with this little variable call, as follows:

BEFORE:

TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT=Gustavo

AFTER:

TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT={{!COL1}}

 We are going to do the same with the rest of the lines, getting, at the end, the following:

TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT={{!COL1}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.414022470 CONTENT={{!COL2}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1960753282 CONTENT={{!COL3}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.549681952 CONTENT={{!COL4}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.436972228 CONTENT={{!COL5}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1842450612 CONTENT={{!COL6}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.926019437 CONTENT={{!COL7}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1850440789 CONTENT={{!COL8}}

 Our complete code should look something like:

VERSION BUILD=8510617 RECORDER=FX
TAB T=1
SET !DATASOURCE ejemplo.csv
URL GOTO=https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/viewform
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.757433527 CONTENT={{!COL1}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.414022470 CONTENT={{!COL2}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1960753282 CONTENT={{!COL3}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.549681952 CONTENT={{!COL4}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.436972228 CONTENT={{!COL5}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1842450612 CONTENT={{!COL6}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.926019437 CONTENT={{!COL7}}
TAG POS=1 TYPE=INPUT:TEXT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:entry.1850440789 CONTENT={{!COL8}}
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ACTION:https://docs.google.com/forms/d/1TeWc2RoeHyCX2DOAXBTzl_uYIS2QrBnfGosuN41d1Mw/formResponse ATTR=NAME:submit

 We can now save clicking on Save & Close button, then we go to the tab Play on iMacros and we click on Play button. This is going to fill and submit the form with the values of the first row of our CSV document. On the next post, I will be trying to explain how to create a Loop to use all the data contained on our CSV doc.