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.
Solution Overview
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
FAQs on Importing CSV into SQL Using Power Automate
Q1. Can Power Automate handle large CSV files when importing into SQL?
Yes. Power Automate can handle large datasets by breaking them into chunks. For very large files, you may combine with Azure Data Factory or SSIS for enterprise-scale ETL.
Q2. Do I need premium connectors for this workflow?
Yes. The SQL connector typically requires a Power Automate Premium or per-user plan.
Q3. Can this solution integrate directly with Power BI dashboards?
Absolutely. Once the data is in SQL, it can be connected to Power BI datasets, allowing automated refreshes and real-time analytics.
Q4. How secure is this workflow?
Power Automate securely connects to OneDrive, SharePoint, and SQL using OAuth and managed identities. Data can also be encrypted at rest and in transit.
Q5. Can this approach be extended beyond CSV files?
Yes. You can extend the workflow to process Excel files, JSON, or XML data sources with minor modifications.
