Answered

Transfer data with blank cells from excel to sharepoint list

(0) Share

Report

Posted on by KS-29050310-0

106

Hi, can anyone help me solve this problem?
I want to transfer data from Excel to a SharePoint list.
Some cells in my Excel file are blank, and all the blank cells are in the number columns. And the data type of these columns are general.
The corresponding column’s datatypes in the SharePoint list are Number.

However, the flow only works if I change all the blank cells to 0.

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

Sort by

Suggested answer

Tomac 4,079 Moderator on at

Like (1)

Report
Copy link

Link copied!
Have you tried the coalesce() function to update the cell values to your source data or blank of the source data is null?

coalesce(outputs('List_rows')?['YourField'],'')

Was this reply helpful? Yes No
KS-29050310-0 106 on at

Like (0)

Report
Copy link

Link copied!

@Tomac
Thank you for your comment, and nice to meet you again!
I tried ↓ , it failed. The errors are the same. The column data type is general, but there are only numbers in the column.

coalesce(items('For_each')?['column name in japanese'],0)
coalesce(items('For_each')?['column name in japanese'],'')

OpenApiOperationParameterTypeConversionFailed
The 'inputs.parameters' of workflow operation 'create items' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/field_20' is required to be of type 'Number/double'. The runtime value '""' to be converted doesn't have the expected format 'Number/double'.

Was this reply helpful? Yes No
Verified answer

Chriddle 8,686 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Try this:

if( isFloat(string(items('For_each')?['column name in japanese'])), float(items('For_each')?['column name in japanese']), 0 )

Was this reply helpful? Yes No
KS-29050310-0 106 on at

Like (0)

Report
Copy link

Link copied!

Chriddle

Thank you very much.

I tried the expression you offered.

It worked much better until the row 275.
I checked the data in row 275.

nothing different with other rows.

0，blank cell ，decimal，integer.

here is the error in create items action.

InvalidTemplate. Unable to process template language expressions in action 'create items' inputs at line '0' and column '0': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Was this reply helpful? Yes No
Chriddle 8,686 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Can you show me the output of this value?

Was this reply helpful? Yes No

KS-29050310-0

106 on at

Like (0)

Report

Copy link

Link copied!

Chriddle

Thank you. Due to the error which I shared with you, there was no output for these two rows.
Instead of output below is the source data. Due to privacy concerns, I can only show you the numbers.

row279

332850

37039

369889

688182

403175

35000

438175

688182

385600

35000

420600

0.363286166

0.388824468

row 274

4411000

2644800

0.400408071

Was this reply helpful? Yes No

Chriddle 8,686 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I need to see the value returned by this: items('For_each')?['column name in japanese'] in row 279 or 274.

Was this reply helpful? Yes No
KS-29050310-0 106 on at

Like (0)

Report
Copy link

Link copied!

@Chriddle

Yes, all of these numbers are what you want.

Much more than 1 column.

Was this reply helpful? Yes No
Chriddle 8,686 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

This doesn't help me figure out what's going wrong.
Can you add a Compose to the loop with the expression from below and display its output at loop iterations 274 and 279?

Was this reply helpful? Yes No
KS-29050310-0 106 on at

Like (0)

Report
Copy link

Link copied!

@Chriddle
Thank you for your help!
I just realized that it was actually a mistake on my colleague’s part. There was a small space in some of the column names, which I hadn’t noticed before. Once I deleted the spaces, the error was resolved. And the expression you offered works very well.

Was this reply helpful? Yes No