When Logic Apps first came out I wrote a blog post explaining how to convert a CSV file into XML.A lot of this is still relevant, especially the integration account and the schemas and maps that are in my github repo. This post will show how Logic Apps are now even simpler to use with flat file decoding and also show how to insert the CSV data into a SQL server. The SQL part of the blog was adapted from this post: https://pellitterisbiztalkblog.wordpress.com/2016/11/14/upload-flat-file-on-azure-sql-database-using-azure-logic-app/
Logic Apps has evolved since I last wrote about this topic and you now no longer need to create a function to transform our csv to xml.
The Transform XML connector is used now with the same maps we used in the previous post
In order to add the individual rows to the database there are a number of things you need to do. We will use an XML schema mapping in a stored procedure to extract the data from the transformed xml.
In your SQL database you will need to add a stored procedure, table and an XML schema, The SQL Scripts to create the table, stored procedure and xml schema have been added to the github repo. The stored procedure takes the xml file that has been transformed and uses the xml schema to extract the firstname, middlename and surname from the xml and then store the data in the employees table. In the logic app you need to add a SQL server connector and configure the connection to your Azure SQL database and also add in the stored procedure with the parameter as the output from the Transform XML.
The only other thing I needed to do to get this working was to remove the first row of the csv file as it contained the header fields and I didn’t want that inserted into the database.
The “length” expression is: length(variables('csvdata'))
The “indexOf” expression is: indexOf(variables('csvdata'),'\r\n')
However if you add this in the editor the back slash will be delimited and you will end up with \\r\\n which will not work. To fix this you will need to click the View Code button, search for the \r\n and remove the extra back slash
The “substring” expression is: substring(variables('csvdata'),add(variables('firstnewlineposition'),2),sub(variables('csvlength'),add(variables('firstnewlineposition'),2)))
The trigger for my Logic App was when a new file was added to OneDrive, so click the run button and then drop a file into the configured OneDrive location and the csv entries should be added to your database.