Dividing huge csv into smaller files

[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

[Chriddle · Accepted solution]

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')}

[blueberrybush]

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.

[Chriddle]

Please insert a Compose action before the failed Csv with following expression

createArray(
	outputs('RowCount'),
	length(variables('dataLines'))
)

and share the output.

[blueberrybush]

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

[Chriddle]

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.

[blueberrybush]

I have modified the flow:

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

[Chriddle]

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

[BrunoBougleux]

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

[BrunoBougleux]

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?