Data from Excel to Sharepoint list

(0) Share

Report

Posted on by KS-29050310-0

Hello, everyone !

I extract data from Excel to Sharepoint List by using automate cloud.

Problem : the title data from excel is string type but the advanced parameter of 「create an item」 show me to type a integer type data in. How can I do?

purpose　：I want to make the sharepoint list filled by the data from my excel table.

background　：I used the「create an item」 api to get and match the data column from excel to sharepoint list. Some fields in the advanced parameter is drop style.　One of them is business rank ,the options are A、B、C、D、E、Z.
I want to make it dynamic , so I used custom way . I entered function like this item()?['business rank'] the datatype of business rank is string type.

Does anyone know the answer.

Thank you very much

Categories:

Business Process Flows

All responses (3)

Answers (2)

Suggested answer

Riyaz_riz11 2,795 Super User 2025 Season 1 on at

Like (0)

Report

Data from Excel to Sharepoint list

Hi,

Add a Compose action before Create item

Use this expression:

{
"Value": item()?['business rank']
}
Then in the Create item → business rank field, just reference the Outputs of that Compose.

🧪 Notes
This only works if the string values from Excel exactly match the choice options in SharePoint.

If Excel may have unexpected values (e.g., lowercase, typos), you should add a condition or data transformation step.

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
Regards,
Riyaz
Verified answer

Michael E. Gernaey 42,666 Super User 2025 Season 1 on at

Like (0)

Report

Data from Excel to Sharepoint list

Hi @KS-29050310-0

Question: If you want the data in your List to match what's in the Excel, why does the Table have an integer but your Excel have a string?

Can you simply delete the column and remake it as an Integer then there is no issue?

BTW what @Riyaz_riz11 said is 100% spot on, my concern is, as you get new string values, you have to keep updating your flow and redeploying to all your environments.

So I would suggest the following to create a Dynamic Mapping which you never have to redeploy and can add new ones any time

1. If you do NOT have the ability to delete the column and remake it as an integer do this

a) create another sharepoint list

b) make this list a mapping like, where it has 2 columns, Title (which is built in) and IntValue, which would be an integer new column.

--In this list add all your current values

Title IntValue

A 1

B 2

C 3

D 4

E 5

etc

Now, when you go through your items in your Power Automate, you will have your List Rows for your Excel file

In your Apply to Each

--You will do another Get Items, but you will filter based on the String value of the current iteration of your List Rows in a Table

So if the first iteration is an A

you will do a Get Items on the mapping table, with the filter being Title eq 'item()?['business rank']' but make sure your put the EXPRESSION not the string item()? bla bla

This will return you back 1 Row from your Mapping table

You will use the IntValue column in your Create Item for sharepoint

1. Flow Triggers

2. List Rows in a Table

3. Apply to each

3a. Get Items from SharePoint using the Filter Title eq 'DynamicPropertyFrom#3 for business Rank'

3b. Apply to each <== this will have 1 Row

3d. Inside the h3b Apply to each do your Create Item in SharePoint

--Make sure that for all the SharePoint values EXCEPT the IntValue that you use the properties from the #3 Apply to each, NOT the 3b. Only in the Column you need the integer do you use the 3B

Example

ColumnX: items('Apply_to_each')?['whatever']

ColumnIntValueInSharePOint: item()?['IntValue']

This way you are NAMING which Apply to each to grab the data from

Now you can add new strings and numbers ANY TIME and it will just work immediately

If you like this solution please mark as such and maybe a like.
Verified answer

Riyaz_riz11 2,795 Super User 2025 Season 1 on at

Like (0)

Report

Data from Excel to Sharepoint list

Hi

In SharePoint List:

Go to "List settings" → Columns → Select business rank

Set the type to Choice and enter choices: A, B, C, D, E, Z

In Power Automate ("Create item" step):

In the business rank field (in advanced options), use:

item()?['business rank']
This works fine if the SharePoint column is Choice with text values.

Use a Switch or Compose + Expression step before the "Create Item":

Step: Add a Compose step to map the string to integer:

if(equals(item()?['business rank'], 'A'), 1,
if(equals(item()?['business rank'], 'B'), 2,
if(equals(item()?['business rank'], 'C'), 3,
if(equals(item()?['business rank'], 'D'), 4,
if(equals(item()?['business rank'], 'E'), 5,
if(equals(item()?['business rank'], 'Z'), 6, 0))))))
Then in the Create item → for business rank field, use the output of this Compose.

If business rank is a Lookup field, SharePoint expects the ID of the item (not the string). You'll need to:

Use Get Items to get the corresponding lookup values and IDs.

Filter based on the Excel value (e.g., "A") to get the ID.

Use that ID in the Create Item field:

{
"business rank": {
"Id": <matched_id>
}
}

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
Regards,
Riyaz

Community site session details

Data from Excel to Sharepoint list

Helpful resources

News and Announcements

Quick Links

Michael Gernaey – Community Spotlight

Congratulations to the May Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Leaderboard >

Featured topics

Product updates

Community site session details

Data from Excel to Sharepoint list

Helpful resources

News and Announcements

Quick Links

Michael Gernaey – Community Spotlight

Congratulations to the May Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Subscribe to this forum!

Select categories

Leaderboard >

Featured topics

Product updates