Consolidate csv files into one Excel Workbook

(0) Share

Report

Posted on by KS-08050315-0

Hello Community,

Power Automate newbie here and I'd appreciate any guidance on how I can achieve what I need to get done.

Essentially, here's what I want to do.

I have a csv file of customer names, with their emails, jobs, workplaces etc which I receive monthly. What I'd like to do is consolidate all the line items in the csv file that I receive monthly into 1 master excel workbook — where the line items for each month are appended in the same sheet of the workbook. And I'd like to do all this locally, without uploading any files to the cloud.

At the same time I will need to do some duplicate detection and validation checks before allowing the line item to append.

Eg.

If email in csv already exists in Master workbook, do not add that row to excel

If email format is in the wrong format (eg. no @), do not add that row to excel.

Any pointers on how I can achieve this? Thank you!

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report

@janey12

Power Automate is a cloud-based service and does not have direct access to local files or folders. It can only work with files that are in cloud storage like OneDrive, SharePoint, etc.

To achieve this, you can setup Power Automate Desktop. @Nived_Nambiar can help

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (1)

Report
Hi @janey12 @SudeepGhatakNZ

there is possibility of taking files from local folder using File System connectors present in Power Automate. But i haven't tried it before so not sure how it works up. But as @SudeepGhatakNZ said, you can try same using Power Automate desktop

see the flow structure below

This flow will take a csv file as input and update the details present in master sheet after doing all validations

Copy the following script to the Power Automate desktop

File.ReadFromCSVFile.ReadCSV CSVFile: $'''D:\\Temp\\Data.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: True ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable SET DTFinal TO CSVTable Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\Export Data.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData LOOP FOREACH row IN CSVTable IF NotContains(row['email'], $'''@''', True) THEN Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: DTFinal AllMatches: True ValueToFind: row['email'] MatchCase: False MatchEntireCellContents: False ColumnNameOrIndex: $'''email''' DataTableMatches=> DTMatches1 Variables.DeleteRowFromDataTable DataTable: DTFinal RowIndex: DTMatches1[0][0] ELSE LOOP FOREACH row1 IN ExcelData IF row1['email'] = row['email'] THEN Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: DTFinal AllMatches: True ValueToFind: row['email'] MatchCase: False MatchEntireCellContents: False ColumnNameOrIndex: $'''email''' DataTableMatches=> DTMatches Variables.DeleteRowFromDataTable DataTable: DTFinal RowIndex: DTMatches[0][0] END END END END Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DTFinal Column: $'''A''' Row: ExcelData.RowsCount + 2 Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
KS-08050315-0 on at

Like (0)

Report

Thank you @Nived_Nambiar, I'll give it a shot! Greatly appreciate this.

I also had another similar query on how I can remove an entire row from excel when a duplicate value exists in 1 column using power automate desktop.

So a very simple example, in this case where a duplicate email is present.
Original table
Customer ID Email Source
xx1
abc@gmail.com Form
xx2 def@yahoo.com Call Centre
xx3 abc@gmail.com CPC

Output table
Customer ID Email Source
xx1 abc@gmail.com Form
xx2 def@yahoo.com Call Centre

I would like the output table to retain the first occurrence and remove the later line item. Is this possible?

Was this reply helpful? Yes No
KS-08050315-0 on at

Like (0)

Report

Hi @Nived_Nambiar, I've tried out your solution and the flow's gotten stuck in the loop for the past couple of hours, likely because I have over 9k entries in my master list. But really thank you for this!!

So, what I've tried to do is add the rows from my new csv file for the month, into the masterlist first. Thereafter, I will check for any duplicates using a VBScript. But my output is no longer the entire masterlist + new rows. The output is just the rows from the csv. Do you know where I'm going wrong? Or any suggestions on how I can do the duplicate removals?

Folder.GetFiles Folder: $'''C:\\Users\\Janey\\Documents\\TestFiles\\csv files''' FileFilter: $'''*new*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Janey\\Documents\\TestFiles\\Master Workbook.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstanceOutput
LOOP FOREACH CurrentItem IN Files
Excel.GetFirstFreeRowOnColumn Instance: ExcelInstanceOutput Column: $'''A''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: ExcelData Column: $'''A''' Row: FirstFreeRowOnColumn
Excel.CloseExcel.Close Instance: ExcelInstance
END
Excel.SaveExcel.Save Instance: ExcelInstanceOutput
Excel.CloseExcel.Close Instance: ExcelInstanceOutput
@@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Set objExcel=Createobject(\"Excel.Application\")
Set objWbk=objExcel.Workbooks.Open(\"C:\\Users\\Janey\\Documents\\TestFiles\\Master Workbook.xlsx\")
Set objSht=objWbk.Worksheets(1)
objExcel.Visible=True
objSht.UsedRange.RemoveDuplicates 123,1''' ScriptOutput=> VBScriptOutput

Was this reply helpful? Yes No
Verified answer

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report
Hi @janey12

The reason why new data was not added is because after writing the csv data to excel , you should include action to save excel so that next time when getting the free row - it should provide first available row.

Also for csv file, you can try csv actions, see below flow for more details

System.TerminateProcess.TerminateProcessByName ProcessName: $'''excel''' Folder.GetFiles Folder: $'''D:\\Temp\\temp1''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\Export Data.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance2 LOOP FOREACH CurrentItem IN Files Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance2 Column: $'''A''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn File.ReadFromCSVFile.ReadCSV CSVFile: CurrentItem Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: True ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable2 Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: CSVTable2 Column: $'''A''' Row: FirstFreeRowOnColumn Excel.SaveExcel.Save Instance: ExcelInstance2 END Excel.CloseExcel.CloseAndSave Instance: ExcelInstance2 @@copilotGeneratedAction: 'True' Scripting.RunVBScript.RunVBScriptAndFailOnTimeout VBScriptCode: $'''\' Define constants for the script Const xlYes = 1 \' Create an instance of Excel application Set objExcel = CreateObject(\"Excel.Application\") objExcel.Visible = True \' Open the workbook Set objWorkbook = objExcel.Workbooks.Open(\"D:\\Temp\\Export Data.xlsx\") \' Activate the first worksheet Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Activate \' Define the used range for the entire worksheet Set rng = objWorksheet.UsedRange \' Remove duplicates based on the email column (Column B) rng.RemoveDuplicates 2, xlYes \' Save and close the workbook objWorkbook.Save objWorkbook.Close \' Quit the Excel application objExcel.Quit \' Clean up Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing ''' Timeout: 10 ScriptOutput=> VBScriptOutput ScriptError=> ScriptError

Flow

Also vbscript used here is

' Define constants for the script Const xlYes = 1 ' Create an instance of Excel application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True ' Open the workbook Set objWorkbook = objExcel.Workbooks.Open("D:\Temp\Export Data.xlsx") ' Activate the first worksheet Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Activate ' Define the used range for the entire worksheet Set rng = objWorksheet.UsedRange ' Remove duplicates based on the email column (Column B) rng.RemoveDuplicates 2, xlYes ' Save and close the workbook objWorkbook.Save objWorkbook.Close ' Quit the Excel application objExcel.Quit ' Clean up Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing

Here i assume that column position of email is at 2, if it not at 2nd column position, update the parameter rng.RemoveDuplicates 2, xlYes , 2 to required one.

Hope it helps !

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
KS-08050315-0 on at

Like (0)

Report

Thank you so much for the guidance! I'll give your solution a go 🙂

Was this reply helpful? Yes No