Managing data from CSV files and storing it in SQL databases is one of the most common yet time-consuming tasks in modern business workflows. When handled manually, this process can lead to delays, data inconsistencies, and human errors. By leveraging Power Automate, Excel Scripts, and SQL Stored Procedures, organizations can build a seamless and automated data pipeline to read CSV files, convert them into JSON, and insert records into SQL tables without manual intervention.

This step-by-step blog demonstrates exactly how to build this automated workflow. It’s particularly useful for business intelligence teams, Power BI developers, and IT managers looking to improve data integration, data accuracy, and reporting efficiency.

Why Automate CSV to SQL Import?

In enterprise environments, CSV files often contain employee data, financial reports, or transaction logs. Automating their ingestion into SQL databases ensures:

· Efficiency: No manual uploads or repetitive processes.

· Scalability: Handles large datasets without slowing down.

· Data Accuracy: Reduces errors caused by manual entry.

· Integration: Enables real-time analytics in tools like Power BI, SSRS, or Microsoft Fabric.

This solution is highly relevant for industries such as retail analytics, manufacturing dashboards, HR systems, and financial reporting where CSV-to-SQL workflows are frequent.

Here’s how we’ll accomplish this automation workflow:

1. Upload CSV: A CSV file is emailed or uploaded to a folder in OneDrive/SharePoint.

2. Power Automate Flow:

o Extract data from the CSV file using an Excel Script.

o Convert the CSV data into JSON format.

o Pass the JSON to a stored procedure in SQL Server.

o Insert data into the SQL table using the stored procedure.

Book a Free Consultation with a Power BI & Data Expert

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.

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);

}

Save the script as ConvertCsvToJson.

Request a Live Demo of Automated Data Pipelines

Step 3: Build the Power Automate Flow

3.1 Create a Flow

· Go to Power Automate and create a new flow.

· Choose the trigger: When a file is created in a folder (OneDrive/SharePoint).

3.2 Read the CSV File

· Add 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 action Initialize variable:

o Name: CsvDataJson

o Type: String

o Value: Outputs of the Run Script

3.4 Call the SQL Stored Procedure

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

· Select your database connection.

· Choose stored procedure: InsertEmployeeData.

· Add JSON variable (CsvDataJson) as input parameter for @JsonData.

Step 4: Test the Workflow

4.1 Upload a Sample CSV

Example CSV:

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 Run History in Power Automate.

· Verify JSON conversion and SQL insertion.

Step 5: Validate Data in SQL

Run this query in your SQL database:

SELECT * FROM EmployeeData;

Expected 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: Eliminates repetitive manual uploads.

· Scalability: Works with large datasets.

· Flexibility: Extend with validation, logging, or notifications.

· Integration: Enables seamless reporting in Power BI dashboards, SSRS reports, or Microsoft Fabric data pipelines.

By combining Power Automate, Excel Scripts, and SQL Stored Procedures, we’ve created a robust, automated solution for importing CSV data into SQL. This solution is perfect for organizations looking to enhance data accuracy, reporting efficiency, and business intelligence adoption.

If you’re ready to build automated workflows that save time and empower analytics, Addend Analytics, a trusted Microsoft Solutions Partner, can help you design and implement scalable solutions tailored to your business.

Talk to Addend Analytics for Tailored Power BI Consulting

Author By

Kamal Sharma

kamal.sharma@addendanalytics.com

Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.