Answered

Dividing huge csv into smaller files

(0) Share

Report

Posted on by blueberrybush

Hello,

I have a problem with huge CSV file, which I am trying to convert to Excel spreadsheet. The problem is that the script works only for CSV files below 10,000 rows. My input CSV file has approximately 85,000 rows and it is different every day. Can you please let me know how to divide the CSV file in Power Automate into smaller files which I can then convert to Excel? And then what is the possibility to merge small spreadsheets into one 85,000 rows spreadsheet? The script that I am using:

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
 let selectedSheet = workbook.getActiveWorksheet();
 const alllines = lineCSV;
 let counter = 1;
 for (let line of alllines) {
 if (line.includes(",")) {
 let items = line.split(",");
 selectedSheet.getRange("A" + counter+":G"+counter).setValues([[items[0], items[1], items[2], items[3], items[4], items[5], items[6]]]);
 counter++;
 }
 } 
 workbook.addTable(selectedSheet.getRange("A1:G" + counter), true).setName("ContactInfo");
}

Thank you

Agnes

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

Chriddle 8,513 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!
Overview:

DataCSV: Compose action with original CSV
RowCount: Compose Action with max row number (her 2 for test purposes)
Lines: Compose action that splits the CSV at line break - dependent on your original CSV file, the line break might also be decodeUriComponent('%0D%0A')

split(outputs('DataCSV'), decodeUriComponent('%0A'))

Header: First row of those lines (the header of the CSV)

first(outputs('Lines'))

Initialize variable dataLines: Other rows (the data)

skip(outputs('Lines'), 1)

Do until: Loop until dataLines is empty

length(variables('dataLines'))

Csv: The new short CSV. You migth create a file with this after this action.

@{outputs('Header')} @{join( take( variables('dataLines'), min( createArray( outputs('RowCount'), length(variables('dataLines')) ) ) ), decodeUriComponent('%0A') )}

NewDateLines: The unused dateLines

skip(variables('dataLines'), outputs('RowCount'))

Set variable dateLines: Set dateLines to NewDateLines

@{outputs('NewDateLines')}

Was this reply helpful? Yes No
blueberrybush 12 on at

Like (0)

Report
Copy link

Link copied!

Thank you for this idea but unfortunately, it is not working for my csv for now.
I am getting the data as attachment to the email, so I had to use Apply to each even when I have only one attachment.
Here is the run:
The DataCSV makes the following output:
The raw output is: "Work Email Address,ManagerEmailAlias\r\nv...
v is the beginning of the new line, so I have on the end of every row \r\n in the output of this function

and then the input to the Lines compose action is like this:
Could you please tell me how to construct the DataCSV so it will work in next steps? It seems like it is making an array, which is not working for Csv action in "Do until" section.

Was this reply helpful? Yes No
Chriddle 8,513 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!
Please insert a Compose action before the failed Csv with following expression
createArray( outputs('RowCount'), length(variables('dataLines')) )
and share the output.

Was this reply helpful? Yes No
blueberrybush 12 on at

Like (0)

Report
Copy link

Link copied!
The output is:

Can you please let me know what should I put in brackets of decodeUriComponent to make it work? I have tried both options provided by you and the result is the same.
split(outputs('DataCSV'),decodeUriComponent('%0A'))

Was this reply helpful? Yes No
Chriddle 8,513 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

Don't use my flow within another loop!
Only use "Apply to each" to get the content of the attachment.
Use function first(array) to get the first item of an array.

Was this reply helpful? Yes No
blueberrybush 12 on at

Like (0)

Report
Copy link

Link copied!

I have modified the flow:
But unfortunately, I have an error in the Lines compose action:

Was this reply helpful? Yes No
Chriddle 8,513 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!
DataCSV is still in your "Apply to each" loop.
Therefore it's an array, not the expected string.
Rename this action to AttachmentCSVs
And add a new Compose action with name DataCSV the following expression

first('AttachmentCSVs')

Was this reply helpful? Yes No
BrunoBougleux 4 on at

Like (0)

Report
Copy link

Link copied!

I had another kind of error.... when setting a new value to dataLine variable, the P.A. raises a error about I'm trying to catch a string and put in an Array. But it's not true!

my newLine code: Array(skip(variables('dataLines'), outputs('QtdLinhas')))

I already uses without the "array" function

Was this reply helpful? Yes No
BrunoBougleux 4 on at

Like (0)

Report
Copy link

Link copied!

I Solved Initializing a new Variable calling "Newline" with array type. then seting it in the ending..

Now, How can I create the result files?

Was this reply helpful? Yes No