Export CSV Data using a Mendix REST Service
I have been working as a Mendix Developer and being a developer we have to face new challenges everyday and think out of the box exploring new possibilities. Thankfully, Mendix is there for the rescue !
During one of my development task I came through a situation wherein exposing data via REST in CSV format was required. But to return an object via REST Service we need to first define an export mapping which as of now can be either JSON or XML mapping. The third option is to return a file.
Now, the challenge here was to return object via REST but a CSV format was required. In order to resolve it since the direct mapping into CSV format wasn’t possible, I thought of a twist !
Through this blog, I would like to share my experience of achieving the CSV file format data with the REST Service with you all, so hopefully, you can learn from it. In order to better explain the achieved solution in a context, I’ll make use of an example service about animals.
Lets’ get Started :
1. Prepare the data that needs to be exposed
We will return a file document from the REST Service and a microflow will be used as the source to the request.
Firstly, in the domain model we need to create the entity of the object which want to expose and an entity of type file document which will be returned via REST call.
2. Achieving result with the twist
The twist that occurred to me was that if there is an option to return a File document type then why not create a file document of csv format consisting of objects to be exposed and return via the REST Service ultimately reaching the goal to have CSV data as output.
In order to achieve that, CSV type file document is generated using the object returned from the database as input. The “GenerateExcelDoc” Java action takes 3 inputs -an export template of CSV type (should be created using Excel Export module), an object of file document type -which we created in Step 1 to store data as output and the object which needs to be exposed (the one we retrieved from database).
Finally, the end event of the microflow is set to return the “NewDoc” of type "CustomExcel" generated from the Java action.
3. Publishing REST Service to expose data
Create a REST Service in Mendix and define GET operation and the path that would be used to fetch your data.
The GET operation can be customized as per your need. For example, if every user wants to fetch the data from a specific start and end date as per their need then the start date and end date can be passed a parameter in the GET request.
Here, in my example I have used simple GET operation as shown below,
In this GET Operation we need to define the source of this data, here we are going to use the microflow we created in Step 2 as an input source. Since we are exposing a file document the response type of our REST call will be a custom excel file (binary).
And, that’s it ! We are all done to make a call to our REST Service and view the result in CSV format.
4. Using Postman to visualize our Result
I used Postman to get the result since the REST Service returns a File document type which we won't be able to visualize via Swagger.
To send a request to our REST Service, there are minor settings which needs to be done in Postman, which are :
a) Set request type as ‘GET’ , mentioning the URL/path that we have created from our REST Service
b) Set the content type as multipart/form-data under Headers
And we are all set to hit the ‘Send’ button and make a call to our service.
Result
The request successfully returned the data in a CSV format, and we're done !
Conclusion
Summarizing: I found an alternate way to return/expose data in CSV format via REST Service. All in all, this was a great learning experience where learning how to “expose CSV via REST” was a food for thought.
The REST Service supports many more types of operations, needing a specific supported format and mapping. But, by exploring this one thing is for sure that sometimes there might not be a straight/direct way to achieve the result but definitely an ultimate solution exist which will come putting a little more twisted thought and efforts. For Now, this works and that makes me a happy developer!
