Adding field conditionally to CSV file

[WarrenBelz]

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.

[tom_riha]

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.

[WarrenBelz]

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 ?

[Ventit]

Try using null

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

I do that a lot with dates

[tom_riha · Accepted solution]

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

[WarrenBelz]

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.