Import CSV file to SQL Server, Mule 4 (MuleSoft)

To Import a CSV file (with header) to the SQL Server database, I will take an HTTP Listener to call the process then read the CSV file and insert to DB as BULK INSERT. 

Import CSV File to SQL Server DB

To do this, I will follow the below steps ...

1. add a Flow of HTTP Listener
2. add a Fiel Read Component
3. add a Bulk Insert component

Before Start, our example CSV file will look like as below formate

CSV file Contant (DataLoad.csv)

-----------------------

ID,NAME

1,Azam

2,Saiful

Create a Blank Mule Project First :)

STEP 1: Add HTTP Listener and Configure it to invoke from browser or postman.

Double Click on HTTP Listener component to Configure

Provide a Name for your HTTP Listener - in my case, /insertcsv

Provide the Path as "/yourpath" - in my case, /insertcsv

Click on "+" Button to Create and Configure the HTTP Listener Connection

Provide a Name for your Connector  - in my case, HTTP_Listener_config

Provide the Host IP - in my case, I used 0.0.0.0 to invoke from anywhere of my network

Provide the Port Number -  - in my case, 8081

Set Base Path as "/"

Test the Connection to check the port is open to use - Click on "Test Connection..." Button

Click "OK"

Add a Response Message to Display in Browser, if everything works fine - in my case, the message is "Data Inserted ..."

HTTP Listener Configuration

HTTP Response

STEP 2: Add File-Read Component and Configure it to Read the CSV file.

Provide the Display name - in my case, Read CSV File

To add a Connector Configuration, Click "+" button

Give a name for this Configuration - "File_Config"

Set Working Directory (where is your file located) - "E:\something"

Test the Connection and Click OK

Finally, provide the File Name OR Fully Qualified Name -  "DataLoad.csv"

File-Read Component Configuration

STEP 3: Add a Bulk Insert Component and Configure it to get data and insert.

Now, First add a Connection of SQL Server Database.

Click the "+" button of "Connector Configuration".

Give a Connection name - Database_Config

Choose "Microsoft SQL Server Connection" as Connection

If required, Click on "Modify dependency" then click "Install" then click "Finish"

Now provide Connection details ...

Host Name - SQL Server instance name OR IP

Port - 1433 (default port of SQL Server)

User - SQL Server User

Password - Password of above SQL Server User

Database Name - which database you want to import CSV File.

SQL Server Connection Conf

After Create the Connection, we have to add INSERT SQL and Parameter to read the Payload message (data object)

Add below Insert SQL at "SQL Query Text:" section/field

----------------------------------

insert into dbo.DataLoad (ID,NAME) values (:IN_ID,:IN_NAME)

----------------------------------

Here :IN_ID and :IN_NAME are the parameters for this INSERT command

After adding the SQL text, now we will add a MAP code to pass the value to these parameters.

Add below code to "Input Parameters:" section/field ...

----------------------------------

#[%dw 2.0

output application/json

---

payload map {

'IN_ID': $.ID,

'IN_NAME': $.NAME

}]

----------------------------------

here  :IN_ID and :IN_NAME are the parameter of INSERT Command.

Input Parameters and SQL Query Text

Thats All :)

now run the project/application - Right click on the Flow and select "Run Project Dataloadcsv"

once the application is run, open the browser and hit the url - "http:/localhost:8081/insertcsv"

you will get the response message "Data Inserted ..." once all done.

Please feel free to comments.