Adding field conditionally to CSV file

Like (0) Share

Report

Posted on 20 Mar 2024 10:31:08 by WarrenBelz

147,237 Most Valuable Professional

All,

I seeking some advice for a Flow where I am importing a CSV file (a credit card statement from a bank) and the last column (credit amount) is not present unless there is data in it. Currently, the users are manually entering zero and re-saving before importing. The rest of the flow works fine and I have attempted a possible solution (noted on the second screenshot) that did not work. Essentially all I want to do is include a zero if there is nothing present in the last column.

Any assistance would be greatly appreciated.

All responses (5)

Answers (1)

WarrenBelz 147,237 Most Valuable Professional on 21 Mar 2024 at 23:29:45

Like (0)

Report

Re: Adding field conditionally to CSV file

Hi @tom_riha ,

Thanks very much for the effort here - I will analyse your structure and try implement it as it will be much more robust than the band aid, very low-tech process I came up with to get it going a short time ago. I am relying on the file to be consistent here, but in the samples I have, if there is data in the last column, there is none in the second last one, so I tested for an empty column 6. If it is empty, I can grab the column 7 value as it will be there and if not empty, I put a zero in column 7.
Verified answer

tom_riha 10,093 Most Valuable Professional on 21 Mar 2024 at 18:00:22

Like (2)

Report
Re: Adding field conditionally to CSV file

Hello @WarrenBelz ,

I did a small PoC with a file as shown below which I suppose is in principle similar to your file.

1. Split it by the new line as you do, skip the first line. Use the output array in the 'Select' action. I'm using the uri representation of a new line instead of a variable, but it doesn't matter.

2. in that 'Select' remap all the columns to a custom property, in my case it's Title, Date, Credit

Title is simple, you just split and index it as you do in the update. Item() in this case represents "for each item in the array".

split(item(),';')[0]

Then the date - you can quite easily change date format with the parseDateTime(...) expression as explained e.g. here in more detail. With your formats it might look as below.

formatDateTime(parseDateTime(split(item(),';')[1], 'en-US', 'ddMMyyyy'), 'MMddyyyy')

The last is the credit, again, just split and index. If there's a number it'll take the number, if not it'll be empty.

split(item(),';')[2]

This will convert the contents of the CSV file into a JSON.

3. Now you have a JSON with array of object, you can loop through that array and create the items. Reference each of the properties using the key you defined in the 'Select' in the left column. The condition below should work for the Credit (now called Amount as I called it that way in the 'Select').

if(empty(item()?['Amount']),'0', item()?['Amount'])

Reference all the properties one by one using item()?['propertyName'] to create the item
Ventit 406 on 20 Mar 2024 at 22:31:29

Like (0)

Report

Re: Adding field conditionally to CSV file

Try using null

If(equals(Field),null),null, Field)

I do that a lot with dates
WarrenBelz 147,237 Most Valuable Professional on 20 Mar 2024 at 21:51:09

Like (0)

Report

Re: Adding field conditionally to CSV file

Hi Tom,
Thanks for the response - I am not a big Flow user when it comes to the more complicated things (I know it shows), but I still have the same conditional issue (only add a column if it does not exist already) with the split code. I am also struggling to get the Select code to create new records in SharePoint. I am using an Attachment control in Power Apps to grab a CSV file and need to create new records in SharePoint with two issues - the one I mentioned of conditionally adding the column and also have to "turn around" a date field from ddmmyyyy to mmddyyyy so that it works in SharePoint.

Is there a better way of doing all of this ?
tom_riha 10,093 Most Valuable Professional on 20 Mar 2024 at 17:01:02

Like (0)

Report

Re: Adding field conditionally to CSV file

Hello @WarrenBelz ,

I'd try to add a middle step where you convert the CSV content into a JSON - after the 'Remove Blank Rows' use 'Select' where you remap all the columns into properties. Output from the 'Remove Blank Rows' as the 'From', each column (split(...)[x]) then mapped to a custom property name. That way you might get a better idea what's coming from the CSV, loop through the newly created JSON array and set the if(...) condition accordingly.