Importing CSV Data into SQL Using Power Automate, Excel Script & Stored Procedures

Managing data from CSV files and storing it in SQL databases is a common task in modern workflows. By leveraging Power Automate, Excel Scripts, and SQL Stored Procedures, we can build a seamless, automated pipeline to read data from a CSV file, convert it into JSON, and insert it into an SQL table.

This blog walks you through the process, with step-by-step guidance and screenshots.

Solution Overview

Here’s how we’ll accomplish this:

1. Upload CSV: A CSV file is emailed or uploaded to a folder in OneDrive/SharePoint.
2. Power Automate Flow:
   • Extract data from the CSV file using an Excel Script.
   • Convert the CSV data into JSON format.
   • Pass the JSON to a stored procedure in SQL Server.
   • Insert data into the SQL table using the stored procedure.

Step 1: Prepare the Environment

1.1 Set Up the SQL Table

Create an SQL table in your database to store the data from the CSV file. For example:

CREATE TABLE EmployeeData (

    EmployeeID INT,

    Name NVARCHAR(100),

    Department NVARCHAR(50),

    Salary FLOAT

);

1.2 Create a Stored Procedure

This stored procedure will take JSON input and insert it into the table:

CREATE PROCEDURE InsertEmployeeData

    @JsonData NVARCHAR(MAX)

AS

BEGIN

    — Parse JSON and insert data into EmployeeData table

    INSERT INTO EmployeeData (EmployeeID, Name, Department, Salary)

    SELECT

        JSON_VALUE(value, ‘$.EmployeeID’) AS EmployeeID,

        JSON_VALUE(value, ‘$.Name’) AS Name,

        JSON_VALUE(value, ‘$.Department’) AS Department,

        JSON_VALUE(value, ‘$.Salary’) AS Salary

    FROM OPENJSON(@JsonData);

END;

Step 2: Create an Excel Script to Parse CSV

2.1 Write the Excel Script

Create an Excel Script to read the CSV file and convert it into JSON. Follow these steps:

1. Open Excel Online.
2. Go to Automate > Code Editor and create a new script with the following code:

function main(workbook: ExcelScript.Workbook): string {

  const sheet = workbook.getActiveWorksheet();

  const rows = sheet.getUsedRange().getValues();

  const headers = rows[0];

  const dataRows = rows.slice(1);

  const jsonArray = dataRows.map(row => {

    const obj: any = {};

    headers.forEach((header, index) => {

      obj[header as string] = row[index];

    });

    return obj;

  });

  return JSON.stringify(jsonArray);

}

3. Save the script as ConvertCsvToJson.

Step 3: Build the Power Automate Flow

3.1 Create a Flow

1. Go to Power Automate and create a new flow.
2. Choose the trigger: “When a file is created in a folder” (OneDrive/SharePoint).

3.2 Read the CSV File

Add an action: “Run script” under the Excel Online Business connector.

• Select the location of your OneDrive/SharePoint file.
• Choose the workbook and worksheet where the CSV is uploaded.
• Select the script: ConvertCsvToJson.

3.3 Capture JSON Output

Save the JSON output of the script to a variable. Add the action “Initialize variable”:

• Name: CsvDataJson.
• Type: String.
• Value: Outputs of the Run Script.

3.4 Call the SQL Stored Procedure

Add the action “Execute Stored Procedure (V2)” under the SQL connector.

• Select your database connection.
• Choose the stored procedure: InsertEmployeeData.
• Add the JSON variable (CsvDataJson) as the input parameter for @JsonData.

Step 4: Test the Workflow

4.1 Upload a Sample CSV

Upload a CSV file like the one below to the designated folder:

EmployeeID,Name,Department,Salary

1,John Doe,IT,75000

2,Jane Smith,HR,65000

3,Mark Johnson,Finance,85000

4.2 Monitor the Flow

• Go to the Run History in Power Automate and monitor the flow execution.
• Verify that the Excel script converted the CSV into JSON and that the JSON was passed to the stored procedure.

Step 5: Validate Data in SQL

Run a query in your SQL database to ensure the data was inserted correctly:

SELECT * FROM EmployeeData;

You should see the following output:

EmployeeID	Name	Department	Salary
1	John Doe	IT	75000.0
2	Jane Smith	HR	65000.0
3	Mark Johnson	Finance	85000.0

Benefits of This Solution

• Automation: No manual intervention is required; the workflow runs automatically whenever a new CSV is uploaded.
• Scalability: Works with any number of rows or columns as long as the schema matches.
• Flexibility: Easily extend the workflow to include additional processing or data validation steps.

Conclusion

Using Power Automate, Excel Scripts, and SQL Stored Procedures, we’ve created a robust, automated solution for importing CSV data into SQL. This workflow eliminates manual effort, reduces errors, and ensures data is always up-to-date. With these tools, you can build scalable and efficient data pipelines for your organization.

Afroz Labbai 

Data Engineering 

Addend Analytics 

Addend Analytics is a Microsoft Power BI-partners based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com.