Unanswered

Remove Commas from Large CSV file

(0) Share

Report

Posted on by Zap174

167

I'm picking up a file from an FTP (csv ~ 200k rows). The goal is to move the file to somewhere our on prem servers can read it in SQL Server, then bulk insert the data into a table. The issue is that the file has some commas in it that mess with the reading and writing into the database. The issue is removing those commas in the fields to get them ready to upload.

One route I have tried is using office script on a blank excel file to populate it with content of the csv file so that I can then take out the commas in the xlsx with another script. However, this has been working with smaller files but always fails with the actual file I need to use.

I have also tried using power automate desktop to open the file in Excel on a remote machine, then close Excel and save it as an xlsx so I can remove the commas but have been running into issues there as well.

I have seen many solutions to this issue but because the file is so big, any solution involving an "apply to each" loop would take too long.

I can elaborate on any of the above points. Any help is greatly appreciated!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

Would changing the delimiter in the csv from a comma to another character like a '-' dash or '/' slash help?

Was this reply helpful? Yes No
takolota1 4,980 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Zap174

It may be an unusual set-up, but you could try combining a CSV to Dataset template here:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

with a batch SQL CRUD template here:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/td-p/1715338

Although that will probably still overload the SQL action with too large of an input, so you’d still have to break the JSON array from the CSV into smaller batches for the SQL CRUD actions.

Was this reply helpful? Yes No
Zap174 167 on at

Like (0)

Report
Copy link

Link copied!

This worked for me when changing it in Excel itself. I changed the delimiter to a semicolon and the file displayed fine. I haven't tested using the bulk insert command in SQL server yet using the changed file though. I would still need a way to change the delimiter in Power Automate from a comma to a semicolon without messing up the values that have commas in them. From my testing around I was unable to do so. Do you have a method?

P.S. love the profile pic

Was this reply helpful? Yes No
Zap174 167 on at

Like (1)

Report
Copy link

Link copied!

@takolota

Thanks for the info! I'll try out the first link you set as I think it could help to change the delimiter to a semicolon so that the commas are ignored when using the bulk insert command. As for getting the data into SQL, to work around not being able to execute a query on on-prem servers, we were going to have a stored procedure that we could execute with the file name to upload it into the database. I'll let you know how it goes!

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

You can also look into Dataflows using Dataverse. I have a process where I put CSV files into folders and then run a Dataflow which will put/update data into a Dataverse Table.
The Dataverse table is then easily used by Power Automate.

Since my organization has some features locked/security in Dataverse for our main environment, I have to run the Dataflows through one of my Teams sites. Each Teams site has the ability to use a limited form of Dataverse, but it is enough to get all of my CSV's into tables for me to use.

If your CSV is going somewhere automatically, then you can time a refresh of the Dataflow. But since I have to manually download my CSV, I have my Dataflows called from a push button Power Automate flow. So I download my CSV's, then go run the single flow, which will then call and refresh my 5 dataflows.

You could probably call the Dataverse tables from SQL Server. If not, the data would at least be in a table that is more manageable by Power Automate.

https://learn.microsoft.com/en-us/power-platform/admin/about-teams-environment

Was this reply helpful? Yes No
Zap174 167 on at

Like (0)

Report
Copy link

Link copied!

I see, thanks for the info! We don't utilize Dataverse right now because of the price and other factors but I have heard good things about running Dataverse through Teams. Would your process be able to handle a size of 200k+ rows in the csv?

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't think it is row count, but total size. "The 2-GB capacity provided to a team can typically store up to 1 million rows of data."

https://learn.microsoft.com/en-us/power-apps/teams/data-platform-compare

FYI: It can be a little slow. It wouldn't surprise me if it took an hour for your dataflow to refresh/update. Mine take about 10-15minutes and have about 1000 rows. So each time you refresh/run the dataflow, it could take awhile to complete. Would have to test and see.

It's easy to set up a table and dataflow. It would probably take about 20 minutes for you to create the dataflow and table to test it and see what happens.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't think it is row count, but total size. "The 2-GB capacity provided to a team can typically store up to 1 million rows of data."

https://learn.microsoft.com/en-us/power-apps/teams/data-platform-compare

FYI: It can be a little slow. It wouldn't surprise me if it took an hour for your dataflow to refresh/update. Mine take about 10-15minutes and have about 1000 rows. So each time you refresh/run the dataflow, it could take awhile to complete. Would have to test and see.

It's easy to set up a table and dataflow. It would probably take about 20 minutes for you to create the dataflow and table to test it and see what happens.

Was this reply helpful? Yes No