Text (CSV) file - get rows

[np]

An action to get rows from a text file (CSV) would be handy.

[asmusz]

Agreed.

MS does not seem to want to step on Plumsail's toes here. They're probably making some $$ on the plugin sales.

Fox/Hen-house.

[molegris]

YES! 

Should also work like JSON Parser.  Meaning that every column header could be mapped to a variable accessible in subsequent Apply To loop for each row.

Please!! 🙂 

[ericonline]

Approaching 4 years with a HUGE number of votes here... whats the deal? Why does this feature request still have the "NEW" tag? Should at least be "IN REVIEW" or the like.

Please pardon my rant (I'm working on that downside).

Instead, I'll offer a solution here. Ended up being fairly straightforward to parse .csv's uploaded to blob storage and write them to SQL. 

Trigger: Blob added to container

Control1: FOR ALL blobs added, `createArray(triggerBody()?['Path'])`

Control2: IF `CurrentItem ends with .csv`

Action1: Get Blob Content Using Path `CurrentItem`

Action2: Compose `body('Action1')`

Action3: (The first secret ingredient) Compose "@split(outputs(Action2, '\r\n')"

  - The key here, is that you MUST actually type the above into the Compose inputs box NOT THE EXPRESSION FIELD

  - See "Step 2" here: https://dynamicscitizendeveloper.com/2019/06/06/microsoft-flow-csv-integration-with-dynamics-365/

Control3: FOR ALL Action3, `skip(outputs(Action3, 1))`

  - This skips the header row in the .csv

Control4: IF `CurrentItem is not equal to "" `

  - For some reason, Action3 generates an additional blank row. This filters the blank row out.

Action4: (The second secret ingredient) Insert Row (SQL) 

  - In each SQL table column field, you need to add the expression `split(items('Control3'), ',')?[X]`

  - Where "[X]" = the position within the split array that needs to map to that given SQL column

Looks like this:

(bummer, can't put screenshots in Comments): Go here instead: https://powerusers.microsoft.com/t5/Building-Flows/How-to-parse-csv-s-to-SQL/m-p/766197/highlight/tr...

[molegris]

@ericonline, 

I agree with you.  I'm convinced they are protecting their business partners who are making money with their own csv parsers.  I think it a mistake!  But what upsets me the most is that they are not transparent about it.  They keep saying how grateful they are for all the help the community provides… then they should at least be honest with us.

[MariekeO]

Any update on this one Microsoft? 

[tutankh]

Any update on this idea?
@Stephen

[TomUWE]

Why is this not a thing yet?

[asmusz]

@ericonline 

We know this "can" be done by jumping through all kinds of hoops, gyrations, and numerous "steps" but the point is that before we had JSON, the most common data exchange format was probably CSV.  This should not require all these machinations.  In powershell it's a single command to turn a CSV file (or even a CSV formatted string) into an "object". 

I have come to the conclusion that MS will never get this handled properly. 

[CFernandes]

This is my solution - https://clavinfernandes.wordpress.com/2021/04/26/get-rowsparse-from-csv-file-using-power-automate-an...

I have used the Excel Run Script action(with little typescript) to convert CSV to JSON to Get Rows from a CSV file. I have backed by solution with a real-life scenario in which I bulk generate documents from the CSV file.

Video Tutorial at - https://www.youtube.com/watch?v=fI9S5781P3o

[tutankh]

Thanks @CFernandes ,

This one is interesting... I'd not seen the Excel function method of doing this before.

I think though, it will suffer from a similar problem as the rest of the strictly PowerAutomate solutions - it does not account for data that has the delimiter inside of one or more of the data cells. Maybe this is possible to account for in the Excel function more easily than in a Flow. I don't know enough Excel/Javascript scripting to know... I'll have to look into it.

I think my core critique still applies though, which really isn't a response to your specific solution... This functionality should be provided by Microsoft at the base level, and should be available in all subscriptions.