Using For-Each in Mule4 for loading CSV file to Database

CSV, short for comma-separated values, is tabular data that has been saved as plain/text data separated by commas. It is a popular data format in system information nowadays.

A database is a systematic collection of data. It offers to store and manipulate data as required by the user. Oracle, MySQL, and PostgreSQL are a few common databases used by developers.

The 'for each' scope splits the payload into elements and processes them one-by-one through the component that you place in the scope. It is similar to the for each/for loop code block in most programming languages and can process any collection including lists and arrays. The supported content types are application/JSON, application/Java, and application/XML.

Working With CSV

Create a sample input file message. Remember the location of the file so that you can use that to define the input metadata when using the 'Transform Message' component.

Create a sample file with the name emp.csv. We will be using the below CSV file as the input for this example:

id, name, department
10, Anil, 10
20, Kuldeep, 10
30, Ajay, 30

Creating Mule Application for Loading CSV File to the Database

Create a Mule project in Anypoint Studio. The flow should look like this:

1. On New or Updated File Listener:

The file connector provides a listener called "On New or Updated File" that polls a directory for files that have been created or updated. A message is generated for each file that is found. In the listener, configure the working directory (that contains the file) and make sure to move the input file to another directory after the file has been processed or else the flow will be recursive in nature and will be processed in time.

The 'On New or Updated File' listener looks like this:

2. Logger:

A logger is placed just after the listener to display and check what data is retrieved from the file from the directory mentioned before.

The logger looks like this:

3. Transform Message:

The 'Transform Message' component transforms the CSV file to the required output data that we want to insert into the database using 'for each.' As mentioned earlier, the supported content types for 'for each' are application/JSON, application/Java, application/XML.

The transform message component looks like this:

4. For Each:

The 'for each' component contains a logger and database insert component to insert the data into the database.

5. Insert (Database):

The database insert component lets us insert the data into our database by configuring the database connector with the required libraries and connections that include host, port, username, and password.

The Insert looks like this:

The database connector configuration:

Code:

<?xml version="1.0" encoding="UTF-8"?>



<mule xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"

     xmlns:file="http://www.mulesoft.org/schema/mule/file"

     xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd

http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd

http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd

http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">

     <db:config name="Database_Config" doc:name="Database Config" doc:id="ad51e72e-0dda-49c1-9880-4c158367500b" >

          <db:oracle-connection host="localhost" user="kuldeep" password="test" />

     </db:config>

     <file:config name="File_Config" doc:name="File Config" doc:id="9e0dadaa-c689-4466-b145-e298c99a7cd5" >

          <file:connection workingDir="H:\mule-batch\input" />

     </file:config>

     <file:config name="File_Config1" doc:name="File Config" doc:id="635983a1-68ba-409d-9860-9dca343592be" >

          <file:connection workingDir="H:\mule-batch\input" />

     </file:config>

     <flow name="foreachmysqlFlow" doc:id="9295e847-974e-454f-8712-f14aa87ccb7f" >

          <file:listener doc:name="On New or Updated File" doc:id="9cb5e6ec-5d4d-4eea-9cd7-f647817dc05c" config-ref="File_Config1" moveToDirectory="H:\mule-batch\archive">

               <scheduling-strategy >

                    <fixed-frequency />

               </scheduling-strategy>

               <file:matcher filenamePattern="*.csv" />

          </file:listener>

          <logger level="INFO" doc:name="Logger" doc:id="93981928-f9e0-421b-8454-806b5f083b71" message="#[payload]"/>

          <ee:transform doc:name="Transform Message" doc:id="d0dfdb05-7b77-4808-bf1f-71963c5e6c24" >

               <ee:message >

                    <ee:set-payload ><![CDATA[%dw 2.0

output application/json

---

payload map {



     id: $.id,

     name: $.name,

     department: $.department

}]]></ee:set-payload>

               </ee:message>

          </ee:transform>

          <foreach doc:name="For Each" doc:id="68a24850-2dc2-4d49-80c2-d5d063b63fa0" >

               <logger level="INFO" doc:name="Logger" doc:id="438b44c9-77ef-4507-ae24-16ddcbed9f6c" message="#[payload]"/>

               <db:insert doc:name="Insert" doc:id="8ed28acc-ba7c-466b-9609-cbffd8ba5b4a" config-ref="Database_Config">

                    <db:sql >INSERT INTO developer(id, name, department)

VALUES(:id, :name, :department)</db:sql>

                    <db:input-parameters ><![CDATA[#[payload]]]></db:input-parameters>

               </db:insert>

          </foreach>

          <logger level="INFO" doc:name="Logger" doc:id="3eb585cc-789e-45d3-8722-e740d8dcac5c" message="#[payload]"/>

     </flow>

</mule>

The above will successfully load your CSV file data to the database.

Happy learning!