Hello Friends,
In this article we will see how to lookup CSV file data without converting it to xml/json or any other message format.
Let’s take one example where we need to lookup data from CSV file and retrieve/filter results from the dataset. Here, we will take one very common dataset with employee details information and then we will filter the data with some set of desired condition.
CSV lookup table Input
| Name | Location | Pincode |
|---|---|---|
| Niral | Pune | 910002 |
| Nikku | Pune | 910005 |
| Shruthi | Bhopal | 843001 |
| Manpreet | Chandigarh | 890021 |
| Little John | Mumbai | 200011 |
| Harry | Delhi | 100011 |
| Tom | Goa | 500110 |
DW Script:
%dw 2.0
output application/json
---
(payload filter ((item, index) -> item.Location == "Pune"))
Output:
[
{
"Name": "Niral",
"Location": "Pune",
"Pincode": "910002"
},
{
"Name": "Nikku",
"Location": "Pune",
"Pincode": "910005"
}
]
In above example we are filtering data based on location. Here, we have not converted csv data in json before using filter. You can also store the results in dw2 vars and use those results based on your use-cases.
Please find below some variations having same output as results:
%dw 2.0
output application/json
---
payload filter ($.Location == "Pune")
You can have multiple conditions also. Below expression, we are retrieving data from csv file inside project. You can use readUrl to load csv data from classpath and then use filter based on Location and Pincode.
%dw 2.0 output application/json var fiterResult= readUrl("classpath://csvFiles/ProfileDetails.csv","application/csv") filter ($.Location == "Pune" and $.Pincode == "910005") --- fiterResult
Output:
[
{
"Name": "Nikku",
"Location": "Pune",
"Pincode": "910005"
}
]
Some of common useful DW2 use-cases that you can check from other articles:
- Filter-complex-array-using-dw2-mule4
- a-complete-dataweave2-selectors-hack
- pattern-matching-in-dw-using-match-case
- splitby-and-joinby-function-dataweave
- array-comparison-use-case-dw2
- dataweave-useful-string-functions-part1
- optimize-json-data-using-dw2
- how-to-write-attributes-from-xml-to-json-dw2
- use-secure-properties-in-dw2
Also, there are a lot selectors available in Mulesoft docs which you can explore from here.
Happy Learning 🙂