Now that this is all set up we move on to the final steps where we loop through the rows and get the columns and write them to a destination (SQL in this example).

6. Set up your apply to each loop.

Note: for this to work you can't use concurrency control. It must be turned off. That means it may take time to write large amount or rows.

7. In the apply to each we loop through the data rows.

On each data row we set the value of our each data row array by splitting the row into columns using the tab as the split character.

The split columns value looks like so:

split(items('apply_to_each_-_data_rows),variables('tabChar'))

Now this gives us the row broken up into columns which we can now reference to write the data.

8. Write the data to SQL or other destinations. SQL used in this example.

Note: You could easily modify this step to update records.

Now set up your SQL insert columns by referencing each column of the "each data row" array which is an array of columns.

You will access each column by using its index just like c#, java, etc...

So your value of each field will look like so:

variables('eachDataRow')[0]

variables('eachDataRow')[1]

variables('eachDataRow')[3]...... etc

You only have to keep up with the position of each column so you write it to the correct corresponding destination column.

Your columns may be out of order depending on the order they are in at the destination.

0 is the first column, 1 is the second column, 2 is the third, etc...

Note: At this step you may need to use replace to remove any quotes, commas, or other characters, specially out of any money values. This gives you the opportunity to affect only one data column whic is what you need.

Re: Read tab delimited / CSV and write records

This is fantastic. I have been searching for a long time for a way of doing this, it seems crazy to me that there isn't a canned way of doing this. Microsoft even told me it is not possible to enter data into a table or database unless it was already in a table. I added one step to deal with empty last rows in my csv files.

Re: Read tab delimited / CSV and write records

I was doing the same thing and found that the condition inside the 'Apply to each' was really killing performance, so I filtered out the empty row(s) using an Array Filter prior to the loop so I could remove the condition.

Re: Read tab delimited / CSV and write records

you look for variable, set variable as the action. Then you can rename it to Tab Character for ease of reading. the actual variable name is tabChar. you could use whatever you want, just change it where needed when you actually use the variable.