I have a CSV file on a SFTP which has 13 columns, but annoyingly the last one has no data or header, so basically there is an extra comma at the end of every record:

PGR,Concession ,Branch ,Branch Name ,Date ,Receipt ,Ref/EAN,Till No ,Qty , Price  , Discount , Net Price  ,

I want to use Logic App to remove the last 13th column and resave the file in a BLOB storage. I've got as far as reading the file from SFTP and storing the entire text in a variable, then using select to get only the columns I need, but beyond that I can't work out how to export all the records onto 1 clean csv file in BLOB.

CodePudding user response：

You'll need to process each row in the array and remove the last comma one by one. As you remove it, add the result to a new array variable. This is the basis of the flow ...

This expression is the main worker and it's the thing that will remove the last character of each row ...

substring(items('For_each'), 0, add(length(items('For_each')), -1))

Before

After

This is the definition of the Logic App. Drop it in to your tenant and see how it works.

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "For_each": {
                "actions": {
                    "Append_to_Cleansed_CSV_Data": {
                        "inputs": {
                            "name": "Cleansed CSV Data",
                            "value": "@substring(items('For_each'), 0, add(length(items('For_each')), -1))"
                        },
                        "runAfter": {},
                        "type": "AppendToArrayVariable"
                    }
                },
                "foreach": "@variables('CSV Data')",
                "runAfter": {
                    "Initialize_Cleansed_CSV_Data": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Initialize_CSV_Data": {
                "inputs": {
                    "variables": [
                        {
                            "name": "CSV Data",
                            "type": "array",
                            "value": [
                                "Test1.1,Test1.2,Test1.3,",
                                "Test2.1,Test2.2,Test2.3,",
                                "Test3.1,Test3.2,Test3.3,"
                            ]
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Initialize_Cleansed_CSV_Data": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Cleansed CSV Data",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_CSV_Data": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_Result": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Result",
                            "type": "array",
                            "value": "@variables('Cleansed CSV Data')"
                        }
                    ]
                },
                "runAfter": {
                    "For_each": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "Recurrence": {
                "evaluatedRecurrence": {
                    "frequency": "Month",
                    "interval": 3
                },
                "recurrence": {
                    "frequency": "Month",
                    "interval": 3
                },
                "type": "Recurrence"
            }
        }
    },
    "parameters": {}
}