POST API with csv as the output

[therush321]

Hi Power Mates

I am needing help with extracting data from an API I am calling with output as CSV and saving it to a SQL table.

I tried parsing it as a JSON but the scheme did not contain the data needed to save it to my SQL table.

{
    "type": "object",
    "properties": {
        "body": {
            "type": "object",
            "properties": {
                "d": {
                    "type": "object",
                    "properties": {
                        "error": {},
                        "filterValues": {
                            "type": "array"
                        },
                        "payload": {
                            "type": "string"
                        },
                        "reportUri": {
                            "type": "string"
                        }
                    }
                }
            }
        }
    }
}

Please see the image below for all the detail:

[lbendlin]

What is the structure of payload?  is that a JSON string or a CSV string?

[therush321]

@lbendlin It is a csv string.

I managed to get it the data out by using some data operations but struggling with a field that contains a comma.

Sample data below of the field that contains a thousand separators:

\"32,000.00\". The output comes out with 32 instead of 32000.

I have tried replacing the comma for that field in the select operation but it does not work.

Your help will be much appreciated.

[lbendlin]

The double quotes should normally protect the comma inside the cell.  Check your quoted text settings.

[therush321]

Thanks but it is not removing the comma.

Please see the code below:

split(replace(item(),',',''),',')?[25]

[lbendlin]

You shouldn't need to remove the comma.  Preserve it and then convert the value based on the specified locale.  Or let SQL Server do that work.

[therush321]

Unfortunately, I cannot get it in with the comma as the action I am using is a select operation which is taking data from the split by lines and it needs to split by a comma to bring the data in.

[lbendlin]

Can you post a sample CSV result from your API call?

[lbendlin]

Can you post a sample CSV result from your API call?

[therush321]

{
  "d": {
    "error": null,
    "filterValues": [],
    "payload": "Year (Entry Date),Month (Entry Date),Week (Entry Date),Client Location,Client Owner,Client Name,User Email,Employee ID,User First Name,User Last Name,Location,Employment Status,Cluster,Business Unit,Business Unit Code,Project Code,Project Name,Task Name,Total Hrs,Actual Billable Hours (Selected Dates),Actual Non-Billable Hours (Selected Dates),Contractor Hours,Time Off Type,ATP Project Billing Type,Billing Rate Amount - Currency,Billing Rate Amount - Amount,ATP Fixed Billing Amount / Month - Currency,ATP Fixed Billing Amount / Month,Actual Billing (Selected Dates) (BC) - Currency,Actual Billing (Selected Dates) (BC) - Amount,Client ID,User ID,Project ID,Task ID\r\n2022,August,Week 31,ATP SA,ATP,ATP Bank Limited,123@ATPsoftware.co.ke,ATP015,JOE,Irvin,ATP Kenya,3rd Party Contractor,Software Development Services (DTV),Development Services,MOB,TAL,ABSA - Mobile - TAL,Dev Ios,10.00,40.00,0.00,Paid,,Hourly Billing Rate,R,600.00,,,R,\"24,000.00\"

@lbendlinAny solution around the commas?

[lbendlin]

Looks like the text is truncated but I'll try to reproduce the issue with a sample flow.  Will let you know when I have something to show.

[lbendlin]

Looks like you need to go the Office script route to split the rows by regexp.

Get Rows(Parse) from CSV file using Power Automate and bulk generate documents – Clavin's Blog (word...

c# - Regex split string preserving quotes - Stack Overflow

[therush321]

@lbendlin Thank you.

I have no clue as to how to do that as the persisting issue is the comma in 3 of my fields.