Create a folder and csv file in Sharepoint based on the queried data using Logic Apps

Question

Hi,
Looking for a solution to create a csv file in Sharepoint based on the data read from the Execute SQL Query. I have a simple table in sql server. I am querying the data based on a status, the read data I am creating a folder and a csv file. The trick is to create csv for each row read from the table. I am able to create the folder and csv file in the SHarepoint. But the data in csv file stores as json.

I have also tried to parse Json and Create CSV table, If I use Create CSV table then my final csv file is storing with all the rows which is not a expected result.

Can some experts look at the logic app flow and guide me through my expected solution.

Answer 1

Hi,

For the sake of the demo, this is the table which I used:

create table T(id int, MyfileName nvarchar(100))  
GO  

The issue in your app is in the file content. You use the Current Item which is the JSON returned from previous steps. What you need is to first add the name of the columns and then the content of the column as in the following screenshot.

Note! By default you might only see the option current item in the dynamic content windows. If you do not see the option of selecting the ResultSet columns, then you can start to type and the Portal should present you the columns. Another option is to do this by edit the code -> on the left menu click on and in the code fid the place where the body of the file is configured, under the For_Each section

Each column that you want to use should be added like @{items('For_each')?['Column Name Come Here']}

For example, check the following code

    "For_each": {  
        "actions": {  
            "Create_file": {  
                "inputs": {  
                    "body": "id,MyfileName,Folder\n@{items('For_each')?['id']} ,@{items('For_each')?['MyfileName']} ,@{items('For_each')?['Folder']}",  
                    "headers": {  
                        "ReadFileMetadataFromServer": true  
                    },  
                    "host": {  
                        "connection": {  
                            "name": "@parameters('$connections')['azurefile']['connectionId']"  
                        }  
                    },  
                    "method": "post",  
                    "path": "/datasets/default/files",  
                    "queries": {  
                        "folderPath": "/users01/@{items('For_each')?['Folder']}",  
                        "name": "@{items('For_each')?['MyfileName']}.csv",  
                        "queryParametersSingleEncoded": true  
                    }  
                },  
                "runAfter": {},  
                "runtimeConfiguration": {  
                    "contentTransfer": {  
                        "transferMode": "Chunked"  
                    }  
                },  
                "type": "ApiConnection"  
            }  
        },  

Update: tip

Instead of configure the content directly in the CREATE FILE action, you can use variable which make it simpler to control

In this case, since you set the Variable action inside a foreach loop, you must enable concurrency control for the foreach loop and set its degree of parallelization to 1.

Answer 2

Not sure that I understand you since you create a file inside the loop which mean a new file will be created for each row returned from the query, but you configure the csv content outside the loop, before the loop which mean you create a single csv content

In addition you should show the configuration of the actions to help us understand what you do

(1) Please try to elaborate what you want to get exactly

(2) Show the content of the relevant actions

(3) In the meantime, please remember that in order to create csv you should use the following configuration

This is a full working example of creating csv from the result of stored procedure:

Notice that the action create csv table return a simple text