Calculating Values from Text Response

dylan-neal · November 7, 2023, 10:05pm

Hi,

I am calling an API that downloads a .csv file. I want to calculate the numbers in a column to make sure the summary is correct. PM displays the body as text (see below). I’ve included a photo of how the .csv is actually formatted if that matters.

I’m able to verify the actually Summary value with this test below, but it feels clunky? And I can’t figure out how to reasonably calculate all values in a column. There will be times when I have maybe 50 rows of data so mapping out each individual value to be calculated would be really tedious and I’m sure there’s a better way.

pm.test("USD Sales = $35.00", function () {
    var jsonData = pm.response.text();
    var rows = jsonData.split('\n');
    var totalRow = rows.find(row => row.includes('Summary'));
    var columns = totalRow.split(',');
    var usdNetSales = columns[32].trim();
    pm.expect(usdNetSales).to.equal('35.00');
});

Response body:
Report: Sales by Ticket,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Group: API Test Group 1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Event: Baseline Records Event,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"Categories: GA, Parking",,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Dates: 5/8/2023 to 11/7/2023,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Event ID,Event Name,Event Date,Pool ID,Pool Name,Sale ID,Sale Name,Venue,Initial Inventory,Starting Inventory,Ending Inventory,Max Inventory,Issued,Refunded,Canceled,Disputed,Net Issued,Sell-Through,USD Avg. Ticket Price,USD Adjusted Sales,USD Discount,USD Taxes,USD Processing Fee,USD Processing Fee to Client,USD CC Fee,USD Facility Fee,USD Service Fee,USD Handling Fee,USD Gratuity,USD Net Sales
7845,Baseline Records Event,2025-05-31,,,"107,293",Custom Form & Promo,Venue Name 1 Chicago,"1,000","1,000",999,,1,0,0,0,1,0.001,5.00,5.00,5.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00
7845,Baseline Records Event,2025-05-31,,,"107,290",GA,Venue Name 1 Chicago,"1,000","1,000",999,,1,0,0,0,1,0.001,10.90,10.90,0.00,0.90,0.00,0.00,0.00,0.00,0.00,0.00,0.00,10.00
7845,Baseline Records Event,2025-05-31,,,"107,291",GA (Shipping),Venue Name 1 Chicago,"1,000","1,000",999,,1,0,0,0,1,0.001,10.90,10.90,0.00,0.90,0.00,0.00,0.00,0.00,0.00,0.00,0.00,10.00
7845,Baseline Records Event,2025-05-31,,,"107,292",Parking,Venue Name 1 Chicago,"1,000","1,000",999,,1,0,0,0,1,0.001,10.90,10.90,0.00,0.90,0.00,0.00,0.00,0.00,0.00,0.00,0.00,10.00
Summary,,,,,,,,"4,000","4,000","3,996",,4,0,0,0,4,0.001,9.43,37.70,5.00,2.70,0.00,0.00,0.00,0.00,0.00,0.00,0.00,35.00
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Generated: 11/7/2023 10:31AM PST,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Company Name v4.0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Thank you!

michaelderekjones · November 8, 2023, 2:13pm

Ok, technically that is a CSV file.

But it looks more like an spreadsheet saved as a CSV file.

The first row should have the column headers which would have made this a bit more manageable.

I’m not sure there is a better way than what you’ve already got. It’s going to be tedious because of the format of the data. Your approach seems logical.

Some things to consider…

If you are not sure how many lines of data are going to be in a file. You could potentially get the Event ID row using the same method as you’ve got for the total row. You need to update this slightly to also return the index number of the row. This should also return an variable of the column headers which you will want to split (which you would usually expect to be the headers in an CSV) and make use of in a later step when working out which column to calculate.

Add 1 to the index and that is the starting row for your calculations.

Change your summary find (totalRow) to also return the index number of that array (with -1 this index). You should now have to starting row and ending row for your calculations.

As you should now have the field names in an array, you could then search the field names for the field you want to calculate, also retrieving the index number for that element.

You now have the starting row, the ending row, plus the column number you need to calculate.

You can then iterate over the rows using the column index, plus the starting and ending index adding the numbers as you go. You will need to split the row within the loop, same as you’ve done for totalRow and can use the column number to extract the value.

for (let i = startingIndex; i < endingIndex; i++) {

dylan-neal · November 8, 2023, 6:29pm

Wow thanks so much Mike, this inspired a lot of ideas as how to maybe go about this! Appreciate your time looking over my lengthy post haha. Will try these ideas how with Event ID row.

dylan-neal · November 8, 2023, 9:51pm

Follow up question…

If I wanted to delete a row, say the row with the “Generated date”, and save that response to compare against in another test, how could I do that?

I’ve tried

const rows = response.split('\n');
const deleteGenerated = rows.find(row => row.includes('Generated:'));
delete deleteGenerated
pm.environment.set('sales_by_ticket_original', JSON.stringify(rows));

But that doesn’t actually delete the row.

I’m able to do it this way below by getting the row number from the response, but that isn’t “flexible” as it assumes that “Generated” is always on row 15 which it won’t be.

const rows = response.split('\n');
delete row[15]
pm.environment.set('sales_by_ticket_original', JSON.stringify(rows));

dylan-neal · November 8, 2023, 10:18pm

woohoo! I think I got it

//convert csv to text
const response = pm.response.text()

//convert text to separate indentifiable rows
const rows = response.split('\n');

// find and define the "Generated" stamp index number
const generatedStamp = rows.findIndex(row => row.includes('Generated:'));
//console.log(deleteGenerated)

//delete the row that contains "Generated"
delete rows[generatedStamp]
//console.log(rows);

//set the csv text response as a string without generated stamp and store as an variable in env
pm.environment.set('sales_by_ticket_original', JSON.stringify(rows));

michaelderekjones · November 9, 2023, 4:12pm

I thought I would have a look at your file as a learning exercise, and its really is a pain and requires extra steps to loop through properly. You have to deal with commas within strings, the end of line characters, etc.

The following is an example that is looping through and calculating the “USD Net Sales” column and then checking that total against the summary total.

// The CSV has commas within some of the strings, and also has blank fields
// So we need a function to deal with this
// https://stackoverflow.com/questions/8493195/how-can-i-parse-a-csv-string-with-javascript-which-contains-comma-in-data
String.prototype.splitCSV = function () {
    var matches = this.match(/(\s*"[^"]+"\s*|\s*[^,]+|,)(?=,|$)/g);
    for (var n = 0; n < matches.length; ++n) {
        matches[n] = matches[n].trim();
        if (matches[n] == ',') matches[n] = '';
    }
    if (this[0] == ',') matches.unshift("");
    return matches;
}

// Step 1 - parse response as text
const response = pm.response.text()

// Split the response into rows
let rows = response.split('\n');

// Step 2 - Find the event ID row (as this contains what would normally be the CSV headers)
let eventID = rows.findIndex(row => row.includes('Event ID'));

// Step 3 - Create an object of the column headers.
let columns = rows[eventID].replace(/[\n\r]+/g, "").split(','); // also removes trailing carriage return

// Step 4 - Set the start and ending rows which will be used when iterating over the rows
let startingRow = eventID + 1;
let endingRow = rows.findIndex(row => row.includes('Summary'))

// Step 5 - Find the index number for the Net Sales column
let netSales = columns.findIndex(e => e === "USD Net Sales");

// Step 6 - Get the USD Net Sales Summary (as a number)
let netSalesSummary = parseInt(rows[endingRow].replace(/[\n\r]+/g, "").splitCSV()[netSales]); // Uses our function

// Step 7 - Initialise the Net Sales count.
let netSalesTotal = 0

// Step 7 - loop through the rows and total the sales
for (let i = startingRow; i < endingRow; i++) {
    let currentRowArray = rows[i].splitCSV(); // Also uses our function
    netSalesTotal = netSalesTotal + parseInt(currentRowArray[netSales]);
}

// Step 8 - Finally our test
pm.test("Checking Net Sales Column and Summary", () => {
    pm.expect(netSalesSummary).to.eql(netSalesTotal);
});

dylan-neal · November 9, 2023, 5:37pm

wow this is top level stuff, thanks so much there’s so much to work with from here. very much appreciated!

system · December 9, 2023, 5:38pm

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.