append csv to another csv

[kky1]

Hello - is there a way to append a csv to an existing csv? The csv files I am working with are about 100K rows each.

My flow is triggered when a file is created to a Sharepoint doc library with trigger conditions of created and endswith csv. 

I then get the csv file content and the file content of a master file csv. The csv file content is always about 100K rows of data (per year) and the master file csv has about 6 years of data (600K rows).

I attempted a method where you parse each csv (I am using the Encodian connector) and then parse the json and then I do a select action to add a year column to the newly uploaded csv and then union the Selects. 

The unioned data is then created into a CSV table and then overwrites the existing master csv file with the new file. The method worked for two years of data and now I get an error on the parse JSON Encodian action "Bad request: Http request failed as there is an error: 'Cannot write more bytes to the buffer than the configured maximum buffer size: 104857600.'" When I look up this error, it is a PA limitation. 

So, now I need an alternate method. Does anyone have a solution for appending (rather than unioning or merging) two csv files? 

I'll buy that cup of coffee for any blog that can help me!

Thank you,

@kky1 

[grantjenkins]

That's a lot of data 🙂

Is there a reason why you need to consolidate all the data into a single CSV? What is the master CSV used for other that just containing all the data - is it used as a source for some sort of reporting, etc.?

If you absolutely need to consolidate all the data you could look at using Office Scripts and storing in an Excel file in batches which "might" be able to get around the limits.

I'd still be looking at the why with regards to combining all the data, and not just having multiple CSV files (one for each year for example).

[kky1]

Hi @grantjenkins - it is a lot of data! The use case is to use the data as a data source for a Power BI file. Currently, multiple csv files are in the PBI file data source and each year another csv file is added and then needs to be added to a Table.Combine clause in the advanced editor. The reason the files are csv is because they are provided by an external source - my employer is a large University from other departments regarding enrollment etc. (my office doesn't "own" the data but needs it for aggregate reporting etc.) We were hoping to find a more automated solution to combining years of this data in the PBI file since we manage a number of dashboards. 

I have been looking at Encodian's Add Rows to Excel action as the file sizes seem to work. As of now, it appears to run successfully but no new rows are added so can't say yet if this is a solution for my use case. 

Thank you again for responding. If you know of alternative solutions, I'd be happy to try. I've read how some people use Python to accomplish this, but my knowledge of Python is limited at best.