Thank you for the reply they are both great suggestions for getting around my issue, unfortunately option 1 won't work for us as we have mathematical calculations that don't function once you convert the fields to text. Also, the csv file route is not ideal as we would have to keep an excel file to do the calculations in and then convert it to a csv every time we want to add a new row of data (and accompanying Test Studio test). This is very impractical from a maintenance point of view.
This is our use case (test) that I am trying to carry out:
A retail user journey from product selection through the cart and checkout journey.
The spreadsheet rows contain every relevant piece of pricing data, as you progress through the cart and checkout. It starts with a plain base price value and then performs calculations in each cell for the progressing values. For example, calculating a VAT total, then adding this total to a cart total price etc. This is why option 1 you offered won't work for these use cases as we need to be able to perform complex maths functions on the data (rounding and percentages for example).
Once the data has been setup in the spreadsheet I then use each column of data for individual verification steps though the cart and checkout user journey. So I am verifying line item prices, line item totals, sub totals, delivery charges, VAT totals, overall totals and then the same again, but when a discount is applied, as examples.
We didn't hit a problem with any of the journey tests we have done so far until this last one, as it was testing larger values and we hit this issue with the comma needing to be present in the expected value in Test Studio as it is naturally displayed on the web pages we are testing with a comma for 1,000 plus values.
I hope this gives you enough information on the use cases we are trying to test and how we are setting up the data. I have attached our working data spreadsheet so you can see the progression of the data calculations and values.
Any further help you could give us that means we don't have to maintain both an excel file and a csv file would be appreciated.