Ask for spreadsheet-Load Records Into SQL

[WI-User-01]

I'm building an app that is asking the user for specific IT asset data.  The user will enter in a request and fill out the form to add the asset information and it'll submit the records and store it in SQL.  This part is working for me.  

If a user has a lot of requests, which is pretty common in today's process, I have a requirement to have the user "submit" a spreadsheet, instead of manually entering in data.  Manually entering 20+ requests would take a long time and be pretty tedius.  I would like to ask the user to attach a spreadsheet template that I've already provided to them.  

Once they attach and submit the spreadsheet, I am envisioning the data be read and sent to SQL. 

Some thoughts I've had:

1. Attach spreadsheet via PowerApp, send it via email to team mailbox, let existing processes take over for processing
2. Attach spreadsheet via PowerApp, have a Flow somehow pick it up and process it into SQL
3. Attach spreadsheet via PowerApp, have PowerApp process it into SQL

My experience with attaching files that are not pictures has been lackluster at best.  It will let you change the filetype and select ANY type of file, but what to do with it once its selected is a challange.  Also, asking a user to change the filetype in a prompt, is a bit much.  If there are too many hoops to jump through, the user will simply bypass the process and go off the rails and tell me how poor our process is.  🙂

 Thanks in advance.

[Drrickryp]

Hi @WI-User-01,

It seems to me that the only way to get a spreadsheet's data into PowerApps would be to bring it in to an app that already has a Sql datasource.  Since you are not going to be editing it and are only using it to add records to your sql table, you could bring it in as a static excel source.  This would give you the ability to bring in as many as 15,000 records at a time and it would only take seconds to acquire the data into your sql table.  As long as the field names were identical to those of the Sql table, you could use the Collect() function to add the data to the sql source.  

Collect(sqlTable,StaticDataSource)

Since I don't have any idea of the nuances of the process, this may be simplistic but I certainly wouldn't allow each user to work with the app, particularly in the beginning.  If you were to collect the Excel tables from the users and input them yourself, it wouldn't be particularly difficult to do. Also, you could delete the static tables from the app once you are done collecting the data. 

Perhaps you could have a single common spreadsheet table that each user adds his request to by copy/pasting their own data into the common spreadsheet table.  Then at the end of a day/week/month you could import the static table into the app, collect the data into the Sql table and then delete the static table. 

[mogulman]

Now this is just me.  I've made dozens of Excel addins (.xlam) for business applications.  It would be a simple process to create addin using VBA and connect to Azure SQL DB.  It would read an Excel spreadsheet and upload data.  It would mark entry  as uploaded.

Another option is to use Azure Function apps v2.   You mail spreadsheet to a certain address with a certain subject line.  The function app reads email from address using Microsoft Graph.  You use EPPLUS to read spreadsheet and upload data.  The function runs every hour.

[WI-User-01]

Hi @Drrickryp, I'll take a look at the static tables idea on Monday, thanks for that.  

Basically, a user would be adding anywhere from 1 to a couple  hundred records to a "request".  The request would be added to SQL.  Then, once a month, a team would "pull" or get sent all the data from the given month to go work on.  

It's a pretty simple process today, we're using a SharePoint list where each user can only see their own data and our team has full access to go get the data they submit.  We're just running into the SharePoint list 5000 row limit when using custom views and I'm trying to improve things.  It's a pain to have to keep an eye on how many records are in the List, then archive them so users don't get errors.

Will test out things and see if it works for what I need.  

[WI-User-01]

Hi @mogulman, I WISH we had access to Azure!  Our company isn't quite there yet, I'm using a SQL database and the on-premesis connector.  I'll research the items you mentioned to see if they apply to regular stand alone SQL.  I like your idea of mailing a file in and a process picks it up and loads it.  Maybe a Flow could do that with Outlook and SQL?  Interesting idea.  

Thanks, I'll post back after I look into the ideas.

[mogulman]

I didn't realize you had on-premise infrastucture.  Assuming you are using cloud Exchange you can easily use an on-premise server to read email.  Assuming you are using C# you can easily create a console app to read email using Microsoft Graph.  To read the Excel attachment I would use EPPlus.  This way the server doesn't need Excel.  Excel has to run under a user account and most servers don't allow user accounts.  I would use a dedicated email account.

If all your users have Excel desktop (VBA) an Excel addin (xlam) is also a good solution.  Excel can easily connect to an on-premise SQL Server.  This is an easy solution and should take a few days to implement.