How to lookup CSV table using Dataweave2

 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 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 dataweave 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"
  }
]

Happy Learning :)