cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juresti
Level 8

read csv file and write to destination

I've developed a solution to read in csv files. (Solved)

 

This is how I imported the csv file and wrote it to SQL table.

 

Note: This works for CSV files with commas and text qualified columns so it should handle any data in your CSV

 

1. get the csv file content 

pic1.png

 

In the next step I set up my variable for new line which I use to parse out the data.

 

Note: to get the new line character I typed it in notepad and copied it to flow and it will appear as blank space.

 

2. set the new line character as a variable of type string and set its value copying an "enter" keystroke from notepad

 

pic2.png

 

The next steps cover setting up the arrays that will hold all the data, then the array that will go through each column in a row, and an array that will hold processed rows (explained later).

 

3. set a new variable of type array - this var will be used to read each column in a row

pic11.PNG

 

4. set a new variable of type array - this var will hold the file content which we break into rows. We go ahead and set this array's value. This is where we break up the file content into rows.

 

Notice the value. We use skip to skip the header row if you have one. Then we use the variable new line as the split character. This makes each row into an item in the array.

So the value looks like so:

skip(split(body('Get_file_content_using_path'),variables('newLine')),1)

 

pic22.PNG

 

5. set a new variable of type string - this var will hold processed rows, meaning rows that have quotes and commas, along with regular rows will be stored here. This is a type string and you will see why later.

Capture.PNG

 

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

 

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. I've run 100 rows in 1 min, so it's not that slow. 

 

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

On each data row we check to see if the row has quotes for special processing.

 

Note: A csv row with quotes (escape characters) usually means there is a comma within the data or numbers are stored as text which also contain commas, and commas are also used to split the columns and this is what we are going to handle.

 

Set an apply to each on your data rows array

100.PNG

 

On each item (row) check if it has quotes

Capture.PNG

 

7. If it doesn't have quotes simply append it to your processed rows string variable and include an enter key stroke behind it

101.PNG

This is an append to string action

The yellow highlight indicates that I pressed enter after the Current item icon.

So now we are adding the "return" back in to the row so that we are able to create a new array by splitting it with new line variable later on.

 

Explanation: Now I explain why we wanted this to be a string.

What we are doing is processing our data rows then putting it back together in a string then we will create a new processed row array from the string. During this process is where we handle the commas within the data.

Since you can't create an array from an array we must go array to string to array.

Besides, the string object gives us a little more flexibility with what we can do to the row.

 

8. At this step we process the rows that do have a quote which most likely have commas within the data

102.PNG

 

First we want to beak up our row into segments and store them in our eachDataRow array by splitting it.

This will give us an array of segments of the data row which we can then go through and find which segment has the quotes and commas.

103.PNG

This is a set variable action

The split columns value looks like so: 

split(items('process_escape_char'),'"')

We want to split by the double quote, so its a double quote inside of single quotes.

 

Then we want to also add the "return" to the end of the row just like we did to the row without quotes.

Because all of these rows are going into the processed rows string.

104.PNG

This is an append to array action

So in value I typed "enter' one time, it will create a blank line

 

Now that we have segments of each data row, we can check which segment has the commas which most likely had quotes.

 

9. Create yet anohter apply to each to go through the segments and perform the final processing and recreating of the processed row (See below for a full flow image for clarity)

105.PNG

each data row is actually each segment

 

Check the condition of each segment by finding if it does not start with and does not end with a comma.

106.PNG

Here we use the current item of the apply to each data row to do the checking.

Note: I found that you need to check each box to the left then select And from the drop down and save the flow so it becomes an And statement. You may need to repeat it more than once.

 

Explanation: The reason we check that it does not start with and does not end with a comma is that the segment with quotes would not end with or start with commas after it is broken up by splitting it on the quotes. This is how we know this is the segment that had the quotes and therefore has a comma most likely.

 

In steps 10 and 11 the row is put back together and remember we included the "enter" new line at the end, so this creates a complete row again by appending each segment and the new line at the end.

10. If the segment does have a comma then simply append it to the processed rows string

(There is no need to append a return key stroke here since we added that after we split into segments above)

107.PNG

This is an append to string

 

11. If the segment does meet criteria then here is where we handle the comma

108.PNG

This is an append to string

Now append this segment to the processed rows and at the same time you can replace your comma.

I replaced my comma with * asterisk so I can find it and replace it back when I get to the write data column.

So the value looks like so:

replace(items('process_each_column'),',','*')

 

Note: When the segments are getting appended back together we end up with the whole row again with an appended enter key (new line character) at the end. 

Except now the row has commas that were within the columns replaced with a "key" character that you can find later.

 

12. Finally we can take the processed rows and recreate the array once again. I initialized a new variable at this step.

109.PNG

This brings us back to step 4 where we split the data by the new line character to get an array of rows.

This time there are not quotes or commas within our data.

The value would look like so:

split(variables('processedRows'),variables('newLine'))
 
This is a before and after sample of what the data looks like
Before (notice the highlights - quotes and commas)
110.PNG
 
After (the quotes are gone and commas have become what I chose - asterisk )
Now we can break up the columns by splitting them on the comma
111.PNG
 
13. Make an apply to each and split the data ready array by the comma
This is below all other steps outside of all for each loops
113.PNG
The value looks like so:
split(items('apply_to_each_-_data_rows'),',')

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

 

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

 

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 can put your comma back with replace and only affect the one column or remove it from money columns. You may also need to replace or remove any other characters, specially out of any money values. This gives you the opportunity to affect only one data column which is what you need.

 

When I replace my asterisk it will look like:

text column: replace(variables('eachDataRow)[2],'*',',')

money column: replace(variables('eachDataRow)[3],'*','') <-- I remove it from money values

pic44.PNG

 

flow overview for clarity - the SQL step would be the last one (not pasted below)

114.PNG115.PNG116.PNG117.PNG

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
juresti
Level 8

Re: read csv file and write to destination

That's great to know if you have the Azure service you can write custom c# code.

 

This post was flow related and for us that don't have Azure services.

 

If we upgrade in the future this is great info to know we can write our own custom code.

 

View solution in original post

juresti
Level 8

Re: read csv file and write to destination

Yes, I was able to remove that character  '\r'

 

It only needs 2 or 3 more steps in the flow.

 

These are the changes to handle the '\r'

 

In step 4 set data rows array there will be three new steps before that.

Here they are

 

First create an initial data array variable which we place the entire content as one item in the array

You will enter [  then select the file content then enter the closing bracker ]

That makes one item in the array

 

pic51.PNG

 

Then you will compose it into a string.

The key here is that you can't reveal the \r\n until it is stored in an array.

So when we bring it out of the array into a string the \r\n is now revealed to us and we can replace it.

 

pic52.PNG

At this step you will also need to replace a lot of other extra character that the string conversion has revealed.

So my replace looks like so:

replace(replace(replace(replace(string(variables('initialData')),'\r',''),']',''),'[',''),'\"','"')
 
Now we can finally proceed to the original step 4 and set the data row array except there is a minor change in the split char.
Since it is string the split char is now literally '\n' for this step only. We still use the actual new line character later.
pic53.PNG
Notice I use outputs which does not appear to be in the flow's expressions.
split(outputs('Compose_2'),'\n')
 
After this the flow continues as normal until step 12
When we set the data ready array now you will skip the first line
skip(split(variables('processedRows'),variables('newLine')),1)
 
That is the only other change to get rid of the \r
 
Some of my sample run
 
.... started with \r then it is gone
pic54.PNG
pic55.PNG
 
pic56.PNG
 

 

 

 

View solution in original post

juresti
Level 8

Re: read csv file and write to destination

 

Yes, I was able to remove that character  '\r'

 

It only needs 2 or 3 more steps in the flow.

 

These are the changes to handle the '\r'

 

In step 4 set data rows array there will be three new steps before that.

Here they are

 

First create an initial data array variable which we place the entire content as one item in the array

You will enter [  then select the file content then enter the closing bracket ]

That makes one item in the array

pic51.PNG

 

Then you will compose it into a string.

The key here is that you can't reveal the \r\n until it is stored in an array.

So when we bring it out of the array into a string the \r\n is now revealed to us and we can replace it.

pic52.PNG

At this step you will also need to replace a lot of other extra character that the string conversion has revealed.

So my replace looks like so:

replace(replace(replace(replace(string(variables('initialData')),'\r',''),']',''),'[',''),'\"','"')

 

Now we can finally proceed to the original step 4 and set the data row array except there is a minor change in the split char.

Since it is string the split char is now literally '\n' for this step only. We still use the actual new line character later.

pic53.PNG

Notice I use outputs which does not appear to be in the flow's expressions.

split(outputs('Compose_2'),'\n')

 

After this the flow continues as normal until step 12

When we set the data ready array now you will skip the first line

skip(split(variables('processedRows'),variables('newLine')),1)

 

That is the only other change to get rid of the \r

 

Some of my sample run

 

.... started with \r then it is gone

pic54.PNG

pic55.PNG

pic56.PNG

 

View solution in original post

13 REPLIES 13
MikePluta
Level: Power Up

Re: read csv file and write to destination

... or you could write a function app with ~10 lines of code, have a 1 second response time for 5000 lines and have the full .CSV syntax (IETF 4180) supported.

 

Use the following app to convert the CSV to JSON and process that as usual.

 

using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Formatting;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using Newtonsoft.Json;
using CsvHelper;

namespace FormatConverter
{
	public static class parseCSV
    {
		[FunctionName("parseCSV")]
        public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "post", Route = null)]HttpRequestMessage req, TraceWriter log)
        {

			Stream postData = await req.Content.ReadAsStreamAsync();
			StreamReader sReader = new StreamReader(postData);
			CsvReader csvData = new CsvReader(sReader);
			csvData.Configuration.BadDataFound = null;
			var csvRecords = csvData.GetRecords<object>().ToList();
			var json = JsonConvert.SerializeObject(csvRecords);

			return req.CreateResponse(HttpStatusCode.OK, json, JsonMediaTypeFormatter.DefaultMediaType);
		}
	}
}

 

juresti
Level 8

Re: read csv file and write to destination

That's great to know if you have the Azure service you can write custom c# code.

 

This post was flow related and for us that don't have Azure services.

 

If we upgrade in the future this is great info to know we can write our own custom code.

 

View solution in original post

MP001
Level: Powered On

Re: read csv file and write to destination

Nice, it works perfect!! ... almost 🙂 because I have a minor problem with this solution.

 

The last field of each row of the result set always contains a "\r" in the field.

In my case this results in text with a new line feed in it. 

I also see them it in your screenshots (110.PNG and 111.PNG)

How did you workaround this?

juresti
Level 8

Re: read csv file and write to destination

Yes, I was able to remove that character  '\r'

 

It only needs 2 or 3 more steps in the flow.

 

These are the changes to handle the '\r'

 

In step 4 set data rows array there will be three new steps before that.

Here they are

 

First create an initial data array variable which we place the entire content as one item in the array

You will enter [  then select the file content then enter the closing bracker ]

That makes one item in the array

 

pic51.PNG

 

Then you will compose it into a string.

The key here is that you can't reveal the \r\n until it is stored in an array.

So when we bring it out of the array into a string the \r\n is now revealed to us and we can replace it.

 

pic52.PNG

At this step you will also need to replace a lot of other extra character that the string conversion has revealed.

So my replace looks like so:

replace(replace(replace(replace(string(variables('initialData')),'\r',''),']',''),'[',''),'\"','"')
 
Now we can finally proceed to the original step 4 and set the data row array except there is a minor change in the split char.
Since it is string the split char is now literally '\n' for this step only. We still use the actual new line character later.
pic53.PNG
Notice I use outputs which does not appear to be in the flow's expressions.
split(outputs('Compose_2'),'\n')
 
After this the flow continues as normal until step 12
When we set the data ready array now you will skip the first line
skip(split(variables('processedRows'),variables('newLine')),1)
 
That is the only other change to get rid of the \r
 
Some of my sample run
 
.... started with \r then it is gone
pic54.PNG
pic55.PNG
 
pic56.PNG
 

 

 

 

View solution in original post

juresti
Level 8

Re: read csv file and write to destination

 

Yes, I was able to remove that character  '\r'

 

It only needs 2 or 3 more steps in the flow.

 

These are the changes to handle the '\r'

 

In step 4 set data rows array there will be three new steps before that.

Here they are

 

First create an initial data array variable which we place the entire content as one item in the array

You will enter [  then select the file content then enter the closing bracket ]

That makes one item in the array

pic51.PNG

 

Then you will compose it into a string.

The key here is that you can't reveal the \r\n until it is stored in an array.

So when we bring it out of the array into a string the \r\n is now revealed to us and we can replace it.

pic52.PNG

At this step you will also need to replace a lot of other extra character that the string conversion has revealed.

So my replace looks like so:

replace(replace(replace(replace(string(variables('initialData')),'\r',''),']',''),'[',''),'\"','"')

 

Now we can finally proceed to the original step 4 and set the data row array except there is a minor change in the split char.

Since it is string the split char is now literally '\n' for this step only. We still use the actual new line character later.

pic53.PNG

Notice I use outputs which does not appear to be in the flow's expressions.

split(outputs('Compose_2'),'\n')

 

After this the flow continues as normal until step 12

When we set the data ready array now you will skip the first line

skip(split(variables('processedRows'),variables('newLine')),1)

 

That is the only other change to get rid of the \r

 

Some of my sample run

 

.... started with \r then it is gone

pic54.PNG

pic55.PNG

pic56.PNG

 

View solution in original post

MP001
Level: Powered On

Re: read csv file and write to destination

Thank you for your post, I will try to build this in. 

 

For now I did a trim on every last field :

trim(variables('EachDataRow')[11])

That solved the issue temporary, but your solution is more robust. 😉

juresti
Level 8

Re: read csv file and write to destination

Hello,

 

That's great. I saw the trim except I did not know how it worked here and did not try it.

 

I'm used to having control of the trim (c#,java,etc..) for example.... choosing how many characters from the left or right to remove.

 

I'm sure it works correctly since it removed the \r for you. So it should probably remove most or any unprintable chars.

 

Super User
Super User

Re: read csv file and write to destination

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.

 

In your case you will parse CSV file, iterate throug result array and then process it as you need.

Lompies
Level: Power Up

Re: read csv file and write to destination

MikePluta's solution to create a custom app to convert the csv to JSON look like an elegant solution.

 

Can someone explain to a noob how to implement this?

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 318 members 5,512 guests
Please welcome our newest community members: