upload excel table to sql database using PowerApp

[vranjan22]

Hi,

I am looking for options/ways to upload excel file/csv to SQL table using PowerApps.

The objective is to update bulk records at once in SQL.

Please help.

Thanks,

Vivek

[mr-dang]

Hi @vranjan22,

How do you feel about copy paste?

I have a solution you can copy data from an Excel spreadsheet (tabbed data) and paste it into a text input box. That data is split apart into a collection. And a button writes data to a datasource.

You'd just need to put in the column names and revise the formula for writing data a little bit to make it work.

https://aka.ms/pa/copypastecsv

Mr. Dang

_______

[mr-dang]

I should mention that the fastest and most efficient way to import records into SQL from Excel is to use the import wizard in SSMS. You can get thousands of records in there in seconds.

You can also use Collect() to write a bunch of records at once more efficiently, but you'll need to make sure the columns are exactly the same.

Collect(connected_table,local_collection) 

Brian

[v-micsh-msft]

Hi @vranjan22,

Here is the current situation:

1. PowerApps has no options available to upload a file and get its contents in a table format at the current time, the attachment control currently is only available with SharePoint List,

2. To work with Excel data within PowerApps, the Data within Excel file should be first formatted into a table, 

3. As @mr-dang mentioned, once you have connected the table within Excel, then it is available for you to bulk update records through collect() or forall() and patch().

4. Another workaround is to use Microsoft Flow within PowerApps, 

Regards,

Michael

[Anonymous]

Hi there, I get an error when trying to import the test PowerApp you created.  Am I missing something obvious?

[Mr-Dang-MSFT · Accepted solution]

@Anonymous, to open an msapp file, do these steps:

1. Go to the PowerApps editor (create.powerapps.com)
2. File > Open > browse for the msapp file

The import experience you're using is for exported zip files.

[kmk]

Hi @vranjan22 / @Mr-Dang-MSFT/ @Anonymous/ @Anonymous 

is this issue fixed ...?

kindly help me too, yes same requirement here as well from Excel to SQL Table.

in between can use flow or powerapps.

if export from powerapps to SQL table that is well and good.

kindly share steps wise.

[Anonymous]

Hi @mr-dang ,

Can you please share your solution or steps? 

The link that you have provided (https://aka.ms/pa/copypastecsv) doesn't work.

Thank you,

Mary

[sgoyal]

Did anyone actually figure this out and if so please post the solution on a highlevel. 

Problem Statement: Need to give the users a UI in PowerApps to be able to attach/upload a CSV/excel file that can be displayed in a gallery in realtime as they attach it and then eventually as they visualize and hit the Save button the records are then saved to the data source (SQL in my case ) using Collect (using a flow and stored Proc to write to DB in my case (already figured out))

I am only trying to figure  out the part to be able to open an excel file table into a gallery and then submit it's records to the datasource (I already know how to save the records to database)