Reading .csv files with and without headers using different delimiters in Mule 4

Flat files especially .csv files are very common for data loading and data transfers. We generally come across scenarios where we want to read a .csv file, which may or may not contain the header row. Also, there is a chance .csv files don't have ','(comma) as delimiter rather has ''|"(pipe) as a delimiter or any other character as a delimiter. This article will discuss various ways to read such files in Mule 4 and create a JSON output of it.

Mule Flow

This will be a simple flow, where we will be reading a file using a file connector. Then convert the read data to JSON using transform message. Finally, log the JSON payload to the console using Logger. Here, we are not doing any complex transformation, it will be more like a MIME type conversion from application/csv to application/json, for better understanding the structure of input data.

Steps:

  • Create a flow in your mule application.
  • Drag and drop file connector with operation "On New or Updated file" on the source of mule flow.
  • In file connector properties under the general tab, specify or create a new connector configuration. Provide input directory, and polling frequency(here it is kept as 10 seconds). Specify the post-processing action like deleting the file, moving the file, or renaming the file. As per the below configurations, the mule application will look for a file in the inbound folder every 10 seconds. If the file is available, then it will process and move the file to the archive directory, deleting the file from the inbound location.
  • Drag and drop "Transform Message" from mule palette, and have output mime type as "application/json" as below.
  • Drag and drop logger from mule palette. Here we will be logging payload after transformation.
  • The final mule flow will look as below.

.csv file having header row in the file content

Let us take below employeeData.csv file as an example. For simplicity, we are taking dummy data of only 5 employees.
id,first name,last name,email,gender,designation
1,Dom,Lissett,dlissett0@test.com,Male,Recruiter
2,Susan,Moogan,smoogan1@test.com,Female,Executive Secretary
3,Carl,Cotesford,lcotesford2@test.com,Male,Tech Architect
4,Debor,Sprakes,dsprakes3@test.com,Female,Account Coordinator
5,Priscella,Genny,pgenny5@test.com,Female,Environmental Tech

In this example, data in the first row signifies the headers separated by "," as a delimiter. Rest all other rows represent employee data.
  • Since the above .csv file contains a header row, all we need to do is to set the MIME type of file connector as "application/csv". To do so, click on the file connector. Click on MIME Type and select "application/csv" from the drop-down list of values.
  • Place the file as defined in the file connector. Run the application. We will see the JSON response logged in the console as shown below.
INFO  2021-07-27 23:08:55,916 [[MuleRuntime].uber.03: [csv-read].csv-readFlow.CPU_INTENSIVE @1e42a374] [processor: csv-readFlow/processors/1; event: 895d1200-efb1-11eb-8134-d4d25289cb8d] org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [
  {
    "id": "1",
    "first name": "Dom",
    "last name": "Lissett",
    "email": "dlissett0@test.com",
    "gender": "Male",
    "designation": "Recruiter"
  },
  {
    "id": "2",
    "first name": "Susan",
    "last name": "Moogan",
    "email": "smoogan1@test.com",
    "gender": "Female",
    "designation": "Executive Secretary"
  },
  {
    "id": "3",
    "first name": "Carl",
    "last name": "Cotesford",
    "email": "lcotesford2@test.com",
    "gender": "Male",
    "designation": "Tech Architect"
  },
  {
    "id": "4",
    "first name": "Debor",
    "last name": "Sprakes",
    "email": "dsprakes3@test.com",
    "gender": "Female",
    "designation": "Account Coordinator"
  },
  {
    "id": "5",
    "first name": "Priscella",
    "last name": "Genny",
    "email": "pgenny5@test.com",
    "gender": "Female",
    "designation": "Environmental Tech"
  }
]

Data in the first row became the keys for the JSON output.

.csv file without header row in the file content

Let us assume there is no header row in the content of employeeData.csv
1,Dom,Lissett,dlissett0@test.com,Male,Recruiter
2,Susan,Moogan,smoogan1@test.com,Female,Executive Secretary
3,Carl,Cotesford,lcotesford2@test.com,Male,Tech Architect
4,Debor,Sprakes,dsprakes3@test.com,Female,Account Coordinator
5,Priscella,Genny,pgenny5@test.com,Female,Environmental Tech

If we try to run with the same configurations, it will take the first row as keys and console output will look like.
INFO  2021-07-27 23:21:15,609 [[MuleRuntime].uber.03: [csv-read].csv-readFlow.CPU_INTENSIVE @1e42a374] [processor: csv-readFlow/processors/1; event: 4254cbd0-efb3-11eb-8134-d4d25289cb8d] org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [
  {
    "1": "2",
    "Dom": "Susan",
    "Lissett": "Moogan",
    "dlissett0@test.com": "smoogan1@test.com",
    "Male": "Female",
    "Recruiter": "Executive Secretary"
  },
  {
    "1": "3",
    "Dom": "Carl",
    "Lissett": "Cotesford",
    "dlissett0@test.com": "lcotesford2@test.com",
    "Male": "Male",
    "Recruiter": "Tech Architect"
  },
  {
    "1": "4",
    "Dom": "Debor",
    "Lissett": "Sprakes",
    "dlissett0@test.com": "dsprakes3@test.com",
    "Male": "Female",
    "Recruiter": "Account Coordinator"
  },
  {
    "1": "5",
    "Dom": "Priscella",
    "Lissett": "Genny",
    "dlissett0@test.com": "pgenny5@test.com",
    "Male": "Female",
    "Recruiter": "Environmental Tech"
  }
]

By default, after setting MIME type to "application/json", mule considers the first row as the header row. In such cases, we don't want to consider the first row as the header row.
  • Click on the file connector. Click on MIME Type. Click on add parameter. Set key as "header" and value as "false". Save the changes.
Now, by using the same file, we will be able to read data properly. But in this case, autogenerated values for keys like coloumn_0, column_1, etc. will be populated. Below is a sample log snippet.
INFO  2021-07-27 23:35:57,158 [[MuleRuntime].uber.05: [csv-read].csv-readFlow.CPU_INTENSIVE @6fbaa2fe] [processor: csv-readFlow/processors/1; event: 4fbf0590-efb5-11eb-8134-d4d25289cb8d] org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [
  {
    "column_0": "1",
    "column_1": "Dom",
    "column_2": "Lissett",
    "column_3": "dlissett0@test.com",
    "column_4": "Male",
    "column_5": "Recruiter"
  },
  {
    "column_0": "2",
    "column_1": "Susan",
    "column_2": "Moogan",
    "column_3": "smoogan1@test.com",
    "column_4": "Female",
    "column_5": "Executive Secretary"
  },
  {
    "column_0": "3",
    "column_1": "Carl",
    "column_2": "Cotesford",
    "column_3": "lcotesford2@test.com",
    "column_4": "Male",
    "column_5": "Tech Architect"
  },
  {
    "column_0": "4",
    "column_1": "Debor",
    "column_2": "Sprakes",
    "column_3": "dsprakes3@test.com",
    "column_4": "Female",
    "column_5": "Account Coordinator"
  },
  {
    "column_0": "5",
    "column_1": "Priscella",
    "column_2": "Genny",
    "column_3": "pgenny5@test.com",
    "column_4": "Female",
    "column_5": "Environmental Tech"
  }
]

.csv file without header row and having a "|" as a delimiter

If we replace "," delimiter with "|" delimiter in employeeData.csv
1|Dom|Lissett|dlissett0@test.com|Male|Recruiter
2|Susan|Moogan|smoogan1@test.com|Female|Executive Secretary
3|Carl|Cotesford|lcotesford2@test.com|Male|Tech Architect
4|Debor|Sprakes|dsprakes3@test.com|Female|Account Coordinator
5|Priscella|Genny|pgenny5@test.com|Female|Environmental Tech

On running the same mule application with the above data, the output of the above employeeData.csv file will be.

INFO  2021-07-27 23:42:06,938 [[MuleRuntime].uber.05: [csv-read].csv-readFlow.CPU_INTENSIVE @6fbaa2fe] [processor: csv-readFlow/processors/1; event: 2c2f0f20-efb6-11eb-8134-d4d25289cb8d] org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [
  {
    "column_0": "1|Dom|Lissett|dlissett0@test.com|Male|Recruiter"
  },
  {
    "column_0": "2|Susan|Moogan|smoogan1@test.com|Female|Executive Secretary"
  },
  {
    "column_0": "3|Carl|Cotesford|lcotesford2@test.com|Male|Tech Architect"
  },
  {
    "column_0": "4|Debor|Sprakes|dsprakes3@test.com|Female|Account Coordinator"
  },
  {
    "column_0": "5|Priscella|Genny|pgenny5@test.com|Female|Environmental Tech"
  }
]

Now, we are getting only one key-value pair for each row. By default mule considers "," as delimiter for "application/csv" MIME type data. We need to explicitly define "|" as a delimiter in parameters under the MIME type.

  • Click on the file connector. Click on MIME Type. Click on add parameter. Set Key as "separator" and Value as "|". Save the changes.


Now, by using the same file, we will be able to read data properly as shown below.

INFO  2021-07-27 23:51:57,557 [[MuleRuntime].uber.06: [csv-read].csv-readFlow.CPU_INTENSIVE @6efd4175] [processor: csv-readFlow/processors/1; event: 8c31a710-efb7-11eb-8134-d4d25289cb8d] org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [
  {
    "column_0": "1",
    "column_1": "Dom",
    "column_2": "Lissett",
    "column_3": "dlissett0@test.com",
    "column_4": "Male",
    "column_5": "Recruiter"
  },
  {
    "column_0": "2",
    "column_1": "Susan",
    "column_2": "Moogan",
    "column_3": "smoogan1@test.com",
    "column_4": "Female",
    "column_5": "Executive Secretary"
  },
  {
    "column_0": "3",
    "column_1": "Carl",
    "column_2": "Cotesford",
    "column_3": "lcotesford2@test.com",
    "column_4": "Male",
    "column_5": "Tech Architect"
  },
  {
    "column_0": "4",
    "column_1": "Debor",
    "column_2": "Sprakes",
    "column_3": "dsprakes3@test.com",
    "column_4": "Female",
    "column_5": "Account Coordinator"
  },
  {
    "column_0": "5",
    "column_1": "Priscella",
    "column_2": "Genny",
    "column_3": "pgenny5@test.com",
    "column_4": "Female",
    "column_5": "Environmental Tech"
  }
]

References:


Please share your valuable feedback 😊
This is a repost of the LinkedIn article....



-

Comments

Post a Comment

Popular posts from this blog

DateTime formatting using xp20:format-dateTime ()

xs:dateTime Format In XML, we generally use xs:dateTime(xs:xmlns="http://www.w3.org/2001/XMLSchema") data type to store date time data. The generic representation of date time, that is supported by xs:dateTime in xml: YYYY-MM-DDThh:mm:ss[.SSS][Z|(+|-)hh:mm] Example :  2019-09-11T10:16:31.943+05:30 Code Description Example YYYY represents a 4-digit year 2019 MM represents a 2-digit month 09 DD represents 2-digit date 11 hh represents 2-digit hours 10 mm represents minutes in 2-digits 16 ss represents seconds in 2-digits 31 SSS represents milliseconds 943 T represents time separator T Z represents UTC time zone or 0 time zone offset (+|-)hh:mm represents time zone offset +5:30 Few more valid examples : 2019-09-11T21:32:52, 2019-09-11T21:32:52+02:...
Read more

Create Delimited String from XML Nodes and Vice Versa in SOA 12c

A delimited string is a string representation of data separated by a delimiter(e.g. ","). A simple representation of the delimited string will look like. Delimited String Examples Sample-1-- Delimiter as ","        Value1,Value2,Value3   Sample-2-- Delimiter as "|"        Value1|Value2|Value3 If we want to represent the above set of delimited values in the form of an XML document under some root and parent tag. The representation will look like this. XML Example <root xmlns="http://test.com/sample/xml">   <Values>     <value>Value1</value>     <value>Value2</value>     <value>Value3</value>   </Values> </root> Sometimes we may need to convert values coming in delimited string to an xml document or vice versa. Since delimited strin...
Read more

Import and Export MDS artifacts in SOA 12c

Image
Oracle SOA-MDS (Metadata Store) repository can be used to access artifacts that can be shared among various SOA composites. For example, we have one common schema for a business fault that is consumed by various applications. Instead of having a localized copy of that schema file in each SOA composite, we can have it in a centralized repository (SOA-MDS). Another benefit of using SOA-MDS is that we can change the file at runtime and need not redeploy the complete code. While developing SOA composites, we use SOA design-time repository, later these changes can be synced to the SOA servers. In this article, we will discuss 2 ways to sync the artifacts available in the SOA design-time repository and the SOA servers. Establishing the connection to SOA-MDS in Jdeveloper. Using SOA em console or Fusion Middleware control console. Moving a file between SOA composite to SOA design-time MDS repository Let's assume, we created a businessFault.xsd which wi...
Read more