web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Use a button to import...
Power Apps
Unanswered

Use a button to import excel and upload data to dataverse table

(0) ShareShare
ReportReport
Posted on by Mark_Yeap 30
I put this fomula under "OnChange" property of the button:
Clear(Pending);
ForAll(Substitute(Split(Substitute(Substitute(importdatafromcustomexcel1.Output,"[",""),"]",""),"},{"),"}",""),With({SingleObject:Substitute(Substitute(Value,",",":"),Char(34),"")},Collect(Pending,
{LEDGER_NAME:Last(FirstN(Split(SingleObject,":"),2)).Value,
PERIOD_NAME:Last(FirstN(Split(SingleObject,":"),4)).Value,
SEGMENT1_CODE:Last(FirstN(Split(SingleObject,":"),6)).Value,
SEGMENT2_CODE:Last(FirstN(Split(SingleObject,":"),8)).Value,
SEGMENT3_CODE:Last(FirstN(Split(SingleObject,":"),10)).Value,
SEGMENT4_CODE:Last(FirstN(Split(SingleObject,":"),12)).Value,
SEGMENT5_CODE:Last(FirstN(Split(SingleObject,":"),14)).Value,
SEGMENT6_CODE:Last(FirstN(Split(SingleObject,":"),16)).Value,
SEGMENT7_CODE:Last(FirstN(Split(SingleObject,":"),18)).Value,
SEGMENT8_CODE:Last(FirstN(Split(SingleObject,":"),20)).Value,
SEGMENT9_CODE:Last(FirstN(Split(SingleObject,":"),22)).Value,
SEGMENT10_CODE:Last(FirstN(Split(SingleObject,":"),24)).Value,
DISPLAY_AMOUNT:Last(FirstN(Split(SingleObject,":"),26)).Value})))
 
I also put "Pending" under "Items" property for data table.
 
During testing, I clicked the button and it opens up the explorer to upload the excel file. Then, the data table displayed the data properly.
 
Now I need to use the same button to upload all the data into dataverse table. Do I need to use formula bar or use flow?
 
Please advice.
Categories:
Building Power Apps
I have the same question (0)
  • BCBuizer Profile Picture
    BCBuizer 22,831 Super User 2026 Season 1 on at

    Hi @Mark_Yeap ,

     

    You can save data from PowerApps directly to DataVerse, so no need to use Power Automate for that. 

     

    A technique I have seem, but don't have experience with, is to replace the datasource in the Collect function from a collection (Pending) to the name of the data source, so you're performning the same function, but instead of using the collection, you save directly to the data source, in this case Dataverse. For you this may be the easiest.

     

    Alternatively you can use the Patch function. Depending on what you want to do, create a new record for each item or also update existing items, your formula will look different though.

  • Mark_Yeap Profile Picture
    Mark_Yeap 30 on at

    If using Patch function, can you give me an example?

  • BCBuizer Profile Picture
    BCBuizer 22,831 Super User 2026 Season 1 on at

    Hi @Mark_Yeap ,

     

    Yes, but can you then tell if you want to only create new files or also update existing ones? Also, is there still a need to save the data in a collection?

  • Mark_Yeap Profile Picture
    Mark_Yeap 30 on at

    I used SaveData(Pending, "Collection1").

     

    I still need to use the same button to upload the data from data table or collection into dataverse table.

  • Mark_Yeap Profile Picture
    Mark_Yeap 30 on at
    I put this fomula under "OnChange" property of the button:
    Clear(Pending);
    ForAll(Substitute(Split(Substitute(Substitute(importdatafromcustomexcel1.Output,"[",""),"]",""),"},{"),"}",""),With({SingleObject:Substitute(Substitute(Value,",",":"),Char(34),"")},Collect(Pending,
    {LEDGER_NAME:Last(FirstN(Split(SingleObject,":"),2)).Value,
    PERIOD_NAME:Last(FirstN(Split(SingleObject,":"),4)).Value,
    SEGMENT1_CODE:Last(FirstN(Split(SingleObject,":"),6)).Value,
    SEGMENT2_CODE:Last(FirstN(Split(SingleObject,":"),8)).Value,
    SEGMENT3_CODE:Last(FirstN(Split(SingleObject,":"),10)).Value,
    SEGMENT4_CODE:Last(FirstN(Split(SingleObject,":"),12)).Value,
    SEGMENT5_CODE:Last(FirstN(Split(SingleObject,":"),14)).Value,
    SEGMENT6_CODE:Last(FirstN(Split(SingleObject,":"),16)).Value,
    SEGMENT7_CODE:Last(FirstN(Split(SingleObject,":"),18)).Value,
    SEGMENT8_CODE:Last(FirstN(Split(SingleObject,":"),20)).Value,
    SEGMENT9_CODE:Last(FirstN(Split(SingleObject,":"),22)).Value,
    SEGMENT10_CODE:Last(FirstN(Split(SingleObject,":"),24)).Value,
    DISPLAY_AMOUNT:Last(FirstN(Split(SingleObject,":"),26)).Value})));
     
    SaveData(Pending, "Collection1");
     
    ForAll(Pending,
    Patch('Pending Costs', Defaults('Pending Costs'), {
    LEDGER_NAME: DataTable1.Selected.LEDGER_NAME,
    PERIOD_NAME: DataTable1.Selected.PERIOD_NAME,
    SEGMENT1_CODE: Value(DataTable1.Selected.SEGMENT1_CODE),
    SEGMENT2_CODE:
    DataTable1.Selected.SEGMENT2_CODE,
    SEGMENT3_CODE:
    DataTable1.Selected.SEGMENT3_CODE,
    SEGMENT4_CODE:
    Value(DataTable1.Selected.SEGMENT4_CODE),
    SEGMENT5_CODE:
    DataTable1.Selected.SEGMENT5_CODE,
    SEGMENT6_CODE:
    DataTable1.Selected.SEGMENT6_CODE,
    SEGMENT7_CODE:
    DataTable1.Selected.SEGMENT7_CODE,
    SEGMENT8_CODE:
    DataTable1.Selected.SEGMENT8_CODE,
    SEGMENT9_CODE:
    DataTable1.Selected.SEGMENT9_CODE,
    SEGMENT10_CODE:
    DataTable1.Selected.SEGMENT10_CODE,
    DISPLAY_AMOUNT:
    Value(DataTable1.Selected.DISPLAY_AMOUNT)
    })
    )
     
    Result: During test, I clicked the button to upload the excel file and the table displayed the data properly. After that, when I checked the dataverse table, all the rows were the same as the first row.
     
    Can you please guide me how to bring all data from power app's canvas app to dataverse when clicking the same button?
  • BCBuizer Profile Picture
    BCBuizer 22,831 Super User 2026 Season 1 on at

    Hi @Mark_Yeap ,

     

    The reason they are all the same is because you reference a static value: DataTable1.Selected.

     

    Instead you need to reference the current record in the ForAll loop:

     

    ForAll(Pending,
Patch('Pending Costs', Defaults('Pending Costs'), {
LEDGER_NAME: ThisRecord.LEDGER_NAME,
PERIOD_NAME: ThisRecord.PERIOD_NAME,
SEGMENT1_CODE: Value(ThisRecord.SEGMENT1_CODE),
SEGMENT2_CODE:
ThisRecord.SEGMENT2_CODE,
SEGMENT3_CODE:
ThisRecord.SEGMENT3_CODE,
SEGMENT4_CODE:
Value(ThisRecord.SEGMENT4_CODE),
SEGMENT5_CODE:
ThisRecord.SEGMENT5_CODE,
SEGMENT6_CODE:
ThisRecord.SEGMENT6_CODE,
SEGMENT7_CODE:
ThisRecord.SEGMENT7_CODE,
SEGMENT8_CODE:
ThisRecord.SEGMENT8_CODE,
SEGMENT9_CODE:
ThisRecord.SEGMENT9_CODE,
SEGMENT10_CODE:
ThisRecord.SEGMENT10_CODE,
DISPLAY_AMOUNT:
Value(ThisRecord.DISPLAY_AMOUNT)
})
)
  • Mark_Yeap Profile Picture
    Mark_Yeap 30 on at

    Why do we use ThisRecord instead of DataTable1?

  • BCBuizer Profile Picture
    BCBuizer 22,831 Super User 2026 Season 1 on at

    Hi @Mark_Yeap ,

     

    The ForAll function loops through all records in the provided table, in this case the 'Pending' collection. ThisRecord is used to reference the record that is being looped through. 

     

    I assume DataTable1 is merely displaying the 'Pending' collection, so the data is the same. And in your formula you weren't referencing DataTable1, but DataTable1.Selected, which is a single record. This is why the formula did not give an error, but still only saved the values for the record that was selected in DataTable1.

  • Mark_Yeap Profile Picture
    Mark_Yeap 30 on at
    Clear(Pending);
    ForAll(Substitute(Split(Substitute(Substitute(importdatafromcustomexcel1.Output,"[",""),"]",""),"},{"),"}",""),With({SingleObject:Substitute(Substitute(Value,",",":"),Char(34),"")},Collect(Pending,
    Table({LEDGER_NAME:Last(FirstN(Split(SingleObject,":"),2)).Value,
    PERIOD_NAME:Last(FirstN(Split(SingleObject,":"),4)).Value,
    SEGMENT1_CODE:Last(FirstN(Split(SingleObject,":"),6)).Value,
    SEGMENT2_CODE:Last(FirstN(Split(SingleObject,":"),8)).Value,
    SEGMENT3_CODE:Last(FirstN(Split(SingleObject,":"),10)).Value,
    SEGMENT4_CODE:Last(FirstN(Split(SingleObject,":"),12)).Value,
    SEGMENT5_CODE:Last(FirstN(Split(SingleObject,":"),14)).Value,
    SEGMENT6_CODE:Last(FirstN(Split(SingleObject,":"),16)).Value,
    SEGMENT7_CODE:Last(FirstN(Split(SingleObject,":"),18)).Value,
    SEGMENT8_CODE:Last(FirstN(Split(SingleObject,":"),20)).Value,
    SEGMENT9_CODE:Last(FirstN(Split(SingleObject,":"),22)).Value,
    SEGMENT10_CODE:Last(FirstN(Split(SingleObject,":"),24)).Value,
    DISPLAY_AMOUNT:Last(FirstN(Split(SingleObject,":"),26)).Value}))));

    ForAll(Pending,
    Patch('Pending Costs', Defaults('Pending Costs'), {
    LEDGER_NAME: ThisRecord.LEDGER_NAME,
    PERIOD_NAME: ThisRecord.PERIOD_NAME,
    SEGMENT1_CODE: Value(ThisRecord.SEGMENT1_CODE),
    SEGMENT2_CODE:
    ThisRecord.SEGMENT2_CODE,
    SEGMENT3_CODE:
    ThisRecord.SEGMENT3_CODE,
    SEGMENT4_CODE:
    Value(ThisRecord.SEGMENT4_CODE),
    SEGMENT5_CODE:
    ThisRecord.SEGMENT5_CODE,
    SEGMENT6_CODE:
    ThisRecord.SEGMENT6_CODE,
    SEGMENT7_CODE:
    ThisRecord.SEGMENT7_CODE,
    SEGMENT8_CODE:
    ThisRecord.SEGMENT8_CODE,
    SEGMENT9_CODE:
    ThisRecord.SEGMENT9_CODE,
    SEGMENT10_CODE:
    ThisRecord.SEGMENT10_CODE,
    DISPLAY_AMOUNT:
    Value(ThisRecord.DISPLAY_AMOUNT)
    })
    )
     
    Result: During testing, I clicked the button to upload excel file and it managed to upload all data into dataverse table.
  • Mark_Yeap Profile Picture
    Mark_Yeap 30 on at

    I need to manually add the data into the data table. How will I do it? Please advice.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 483

#2
WarrenBelz Profile Picture

WarrenBelz 399 Most Valuable Professional

#3
11manish Profile Picture

11manish 327

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans