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 ...
- add a Flow of HTTP Listener
- add a Fiel Read Component
- 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.
