RE: Bulk Upload into SP list - Ignore empty cells and upload only the excel columns which have values into SharePoint List

[KeerthiRaj96]

Hi Folks,

Need your help once again. currently, I am creating a flow - bulk excel upload to Primary SP list. I have 'N' no.of.Columns.in my excel file where user will update the columns based on his preference. Most of these columns are date columns along with text fields in SharePoint backend.

Current flow setup:

Apply to each (expanded view):

I got stuck in this place, tried if condition for an particular date column in excel:

What I need is:

to make my flow to identify whether my cell have any blank value in my excel file. If yes, then move to next cell and upload only those columns with values into SP list. If No, upload/Update into SP list.

Please help out with your suggestions.

[KeerthiRaj96]

Hi @ekarim2020 ,

I am sorry to disturb you again. I need your dearest help once again. 

Kindly look into this post and share your comments and suggestions.

Thanks in advance!!!

[ekarim2020]

To get a properly formatted date from Excel check that you have selected the following option as shown in the image below:

See also:  Working with Date & Time data of Excel in Power Automate (bythedevs.com)

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[KeerthiRaj96]

Hi @ekarim2020 ,

Thanks for your reply.  The date format which you have mentioned above was already used in my flow. If my excel file have values for all the 80 columns it runs success, if any one column does not have value and left blank it fails.

Hope you could be able to understand the query which i have mentioned in this post:

Main Goal: If any blank columns in excel file exists, my flow should be able to skip those cells and upload only the columns which have values into SP list.

Please provide some suggestions for this query.

Thanks in advance!

[ekarim2020]

I'm not sure I understand fully the outcome you are looking for. Perhaps the following examples may help.

Based on the following Excel file:

(1) You could use the filter array function to select only those rows that do not contain any empty cells:

This is the expression for the filter array:

@And(not(empty(item()?['Group Name'])), not(empty(item()?['Title'])), not(empty(item()?['Team Lead'])), not(empty(item()?['Manager Name'])), not(empty(item()?['Status'])))

Thus rows with null values will be ignored.

(2) Another possible option is to replace empty values with another value using a Select action. The example below replaces empty Status values with 'no status':

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[KeerthiRaj96]

Hi @ekarim2020 ,

Thanks for your kind response. I have tried out using the first method filter array and observed below points:

1. Flow works fine with any error , but it is ignoring the whole row instead of those particular cells . As per my query, in an excel row have blank cells, only those blank cells needs to be ignored (not whole row) and remaining cells with values needs to be created/updated  into SP list.
2. I have one doubt in first method -> filter array, you have used body('Filter Array')  in Apply to each 2 loop action for create items  action. What should be used for Update item  -> Apply to each loop action?

Reference to my  query:

Also, why the row id is not included in the filter array expression - is it automatic sequential numbering in excel?

Please clarify it for both the methods which you have mentioned above as solution.

[ekarim2020]

This is just a simpler example I am demonstrating - so it may be missing some of the specific details for your flow.

The rowID is something I added manually and is a unique identifier of the row.. The rowID is also saved to the SharePoint list and can be used to update the SharePoint list items - and you may already be using this or something similar. So, for your update action you will need to match RowID from the Excel data (via using filter or  select action) with the SharePoint list item

Choose one of the methods that matches your needs.

The filter array action will only return row 2:

The Select returns the following data, with the Status updated to 'no status' of the status field is empty:

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[KeerthiRaj96]

Hi @ekarim2020 ,

Thanks for your reply. I have tried out first method - Filter array - this doesn't suit for my requirements ( this method completely ignore the row if it have blank cells). So, i have used expression as stated below and map the expression to each individual columns in create/update action and flow works fine.

Example:  

column region -> mapped to Compose and i have called that output in create item action.

Expression:

if(empty(outputs('Region')),null, outputs('Region'))

Need help on People Picker Columns and with Email ID columns:

When i used the same above expression for people picker columns, my flow fails with message -  update item action item failed.

My people picker column in flow was set up based on the solution which you provided earlier (Multiple Email id's while uploading excel into SharePoint list query)

Reference: https://powerusers.microsoft.com/t5/Building-Flows/RE-Import-excel-to-SharePoint-list-create-item-Is...

What i need is, if people picker column have values in excel file, then upload into SP list, if blank cell do nothing (ignore it).

kindly suggest some ideas for the same.

[ekarim2020]

If I have understood the current issue correctly:

In the Excel row, if the column for Team Lead is empty or null, then the corresponding SharePoint column Team Lead needs to be set to null. If the Team Lead is not empty or null, then populate the SharePoint Team Lead people column

Example: in RowID 6, the SharePoint Team Lead column is set to empty or null:

Is this correct?

Ellis

[KeerthiRaj96]

Hi @ekarim2020 ,

Yes you are correct, if team lead cell is empty in excel list. it should be empty. If manager cell is empty in excel, it should updated as empty in SP list.

Please suggest an idea.

[ekarim2020]

I will have a look at the issue in more detail later, but the flow will have a logic roughly like this:

Ellis

[KeerthiRaj96]

Hi @ekarim2020 ,

i will try the rough logic which you shared. But got confused, where to place these conditions (since i have so many apply to each and other actions in my flow)

Kindly share the same logic using flow if possible, which will give clear idea for implementing the same.

[ekarim2020]

Here is the overview of the entire flow. It is much the same as before, but with actions to handle empty Team and Manager columns. I've also added some Scope blocks to make the flow easier to follow

This is the first section:

Initialize two new variables to store the Team and Manager columns - whether empty or they contain data - the variables will be used to create a new SharePoint item or to Update a SharePoint item:

This section will handle the Team column (in Scope Team) and Manager column (in Scope Managers):

Reset variables at the start of each loop, set varManagers and varTeams to empty:

Now the actions for the Team and Managers are done in parallel - it just makes it clearer what the flow is doing.

We will start with the Team column. The condition expression checks it Team column is empty:

empty(items('Apply_to_each')?['Team Lead'])

If the Team column is not empty, we process the data (email addresses) in this column just like before (multi-person people picker):

We set the Team Display names as before:

The Team section is completed:

Next post: Managers Column

[ekarim2020]

We will continue onto the Managers column.

This is the Scope Managers action:

The condition expression checks if the Managers column is empty:

empty(items('Apply_to_each')?['Manager Name'])

If the Managers column is not empty, we process its content as before:

We have handles both empty Teams or Manager columns. There is no easy way to do this other than checking each Excel row, and examining the contents: empty or has data

Next post: SharePoint Create Item, Update Item

[ekarim2020]

High-level flow:

Run time:

That's enough for me. I need a long rest!

Ellis