Data driven from excel having multiple sheets

Lalit_sah · March 26, 2018, 4:21pm

Hi Team,
I am just started using POSTMAN (last one month) app to automate API’s.
I am facing some issues while automating the API’s.

For one scenario I am unable to automate, did quite number of research and could not succeeded. SO at last looking for help from your team.

Here is my one scenario:
I have a API in which I have to pass lot of data in body and to verify the expected result.

All input data are in different sheet of a excel.
Sheet one contains around 25 input fields, sheet is having 16 input fields, sheet has 4 input fields and sheet will increase going forward.

I can able to convert the excel to CSV and able to fetch the data and automate, but in my case
1, It is difficult to maintain all input data and expected result values in a single sheet.
2. The sheet combinations also vary based on scenario,
ex:
Combination 1: Only sheet 1 data
body is
{ sheet 1 input
}

Combination 2: sheet 1+sheet 2 data
{ sheet 1
{ sheet 2}
}

Combination 3: sheet 1+sheet 2 + Sheet 3 data
{ sheet 1
{sheet 2}
{sheet 3}
}

Maintaining in a single sheet will do lot of maintenance
There are lot of scenarios with different input values in each sheet.
Sheet number will increase in future

Can you please help me, how to solve this issue.

FyI; the body is like below format

Thanks,
Lalit

kunagpal · March 27, 2018, 8:55pm

Hey @Lalit_sah, is there an API that these sheets can be fetched from? That could alleviate the problem of having to manually feed the collection run information.

One possible workflow that I can think of is as follows:

Fetch all the required sheets from initial requests in CSV format.
Convert the CSV data using the inbuilt csv-parse library to convert the data to a JSON format.
Stringify the arrays of objects obtained from each sheet, save them to environment variables.
Set up an iteration over these saved arrays using postman.setNextRequest in order to achieve complex iterations.

Lalit_sah · March 28, 2018, 3:03am

Hi @kunagpal
Thanks the response. I am new to POSTMAN and not clear about 2-3 steps. I Have already send 2 weeks but could not get the solution. I have attached my data sheet for more help.

Can you please me more to solve this issue, explaining step by step.

It’s a post request and in body I am passing the data. Here original script

{
“loanProduct”: “Regular”,
“disclosure”: “APR”,
“loanAmount”: 100000,
“feeAmount”: 10000,
“rate”: {
“type”: “Fixed”,
“value”: 4.500
},
“rePriceDate”: “2018-10-01”,
“balloon”: {
“balloonDate”: “2019-01-02”,
“isBalloon”: true
},
“stockAmount”: 0,
“stockPercent”: “”,
“stockMethod”: “STPrin”,
“minimumPrincipalAmount”: 0,
“accrualMethod”: “c_Actual_360”,
“startDate”: “2018-10-01”,
“interestStartDate”: “2018-10-01”,
“interestForwardDays”: “10”,
“amortizedTerms”: 0,
“principalBalancePercent”: “”,
“constructStartDate”: 0,
“finalMaturityDate”: “2018-12-01”,
“noteName”: “”,
“repayments”: [
{
“method”: “PartOne”,
“schedules”: [
{
“paymentType”: “None”,
“startDate”: “2018-10-01”,
“frequency”: “BiWeekly”,
“numberOfPayments”: 0,
“paymentAmount”: 0
}
]
}
]
}

I Converted xlsx to csv and passing the data in body.
Below script code parameters gets from sheet 2 and rest are from sheet 1
“repayments”: [
{
“method”: “PartOne”,
“schedules”: [
{
“paymentType”: “None”,
“startDate”: “2018-10-01”,
“frequency”: “BiWeekly”,
“numberOfPayments”: 0,
“paymentAmount”: 0
}
]
}
]

Right now I am copying all sheet2 data in sheet1 > converting to sheet1 to csv > then passing in script body. I am able to achieve the target, but I know this that is not the right solution.

Same way expected result is another sheet and based on complexity, sheet number will increase.

Regards,
Lalit

Lalit_sah · March 28, 2018, 3:05am

Hi Kunagpal,
Thanks the response. I am new to POSTMAN and not clear about 2-3 steps. I Have already send 2 weeks but could not get the solution. I have attached my data sheet for more help.
Can you please me more to solve this issue, explaining step by step.

It’s a post request and in body I am passing the data. Here original script

{

“loanProduct”: “Regular”,

“disclosure”: “APR”,

“loanAmount”: 100000,

“feeAmount”: 10000,

“rate”: {

"type": "Fixed",

"value": 4.500

},

“rePriceDate”: “2018-10-01”,

“balloon”: {

"balloonDate": "2019-01-02",

"isBalloon": true

},

“stockAmount”: 0,

“stockPercent”: “”,

“stockMethod”: “STPrin”,

“minimumPrincipalAmount”: 0,

“accrualMethod”: “c_Actual_360”,

“startDate”: “2018-10-01”,

“interestStartDate”: “2018-10-01”,

“interestForwardDays”: “10”,

“amortizedTerms”: 0,

“principalBalancePercent”: “”,

“constructStartDate”: 0,

“finalMaturityDate”: “2018-12-01”,

“noteName”: “”,

“repayments”: [

{

  "method": "PartOne",

  "schedules": [

    {

      "paymentType": "None",

      "startDate": "2018-10-01",

      "frequency": "BiWeekly",

      "numberOfPayments": 0,

      "paymentAmount": 0

    }

  ]

}

]

}

This is what I am trying. Converted xlsx to csv and passing the data in body.

Issue is 1st part of data from sheet one and 2nd part of data is from sheet2 and going forward we have lot more sheets yet to come.

Same way expected result is another sheet. Converting all data in a single sheet , i am able to active the target, but I this that is not the right solution.

{

“loanProduct”: {{loanProduct}},

“disclosure”: {{disclosure}}

“loanAmount”: {{loanAmount}},