We have already learnt storing and accessing variables in different ways like Global variables, Environment variables, Collection variables etc in Postman. All these ways are like accessing variables from internal sources.
We may require to read data from external sources as well like csv or JSON in Postman. Not only this, it is useful to achieve data driven API automation testing as well. We will see everything in details.
We will cover how can we read data from CSV in Postman. Note here I am using term “CSV (Comma Separated Values) ” not “Excel”. There are many differences between a CSV and an Excel sheet. A CSV is a file formats that contain plain text values separated by commas. You can open any csv file in an excel as well as in a text editor. CSV is only capable of storing a single sheet in a file, without any formatting and formulas. Read more about Difference between an Excel and a CSV here.
Let’s understand a scenario:-
RestFul Booker provides an API to do booking which takes a JSON body as request to provide required details. I want all these values should come from a CSV file.
Let’s create a csv file to provide data :-
We can use an excel file and save it as csv file or we can use a text editor. Using a text editor is little difficult and confusing to create csv as a beginner. So I will suggest you to create a csv data file using excel.
There is some pattern to create a csv data file for Postman. The first row represents all variable names, and subsequent rows represent values for these variables. ( I am purposefully hiding an important term here for later understanding ). Let’s input values in excel first:-
You need to take care of number and boolean field. Add a single quote (‘) before passing value for numbers and boolean to maintain the formatting. You can notice above that value of “depositpaid” is converted to uppercase value of “true”. So to keep actual formatting add single quotes.
Now save above file as csv.
We are done now with creating csv. Second step is to parameterized request. Remember we can use csv variables in URL, headers, request , scripts etc as well. Here we are using in request body.
Using variables from csv file is same as any variable from Environment, global or collection i.e. as {{variableName}}.
Note:- If variable value is a string use put above in double quotes. If it is number of boolean , put it without double quotes. See example below:-
If we are reading data from a csv, we can not run it as a normal request by clicking on “Send” button. If we click on Send button to run above request, it will give you error “400 – Bad Request” because it will not able to get values for the variables defined. We have not told Postman where to look for values yet.
So we need to use Collection Runner for this. Save above request in a collection and launch collection runner. You can find all concepts of Collection and Collection RUnner on my blog here.
In Collection Runner, you will see a field named “Data” with “Select File”. Here you need to select csv file which we created above. Click on “Select File” button and select the csv file. You will see after selection, Data File type will automatically show as text/csv. You can preview the file.
Now run the collection by clicking on “Run <Collection Name>” button.
You will see Run result of Collection. You can see json request takes values from csv file.
You can find successful response as well:-
So we have read data from csv successfully.
You can download example csv from below link:-
You can import example collection from here:-
https://www.getpostman.com/collections/c35399b642d3e5aa591f
#ThanksForReading
Hi AMOD,
Thanks a lot for all the tutorials, they are excellent and the approach is amazing.
Have a query: How can we move to the next request in the collection with the next data available in the data file.
Example:
If password data is available in the data file with data1, data2, data3 etc and there are 3 requests in the collection
Run request1 with data1
Run req2 with the data 2
Run request3 with data3
As i have huge number of requests i dont want to create different variables.
Ive used postman.setNextRequest() to move to the next request else it is not running all the requests in the collection. But now it is running the next request with all data
Can you please suggest and help. Thanks
Hello,
That is a major functionality missing in Postman. It doesn’t provide a way to keep data for all requests in one json or excel
And run as a collection.
Hi Amod, I created a CSV file and run in collection to create booking but I am getting error BookingID should not be null.
I am running only create booking post request in collection.
However, the request body displaying the values I am passing from csv file.
could you suggest?
One more thing, if i attach the sample csv file.DataForBookingHotel in collection, the same request runs without any error however if I attach the csv file I have created exactly same as yours, getting error “Booking Id should not be null” in same request.
Hey Amod, Is there a solution for this yet from postman