Advertisements

Archive

Posts Tagged ‘Excel’

PowerApps – Error while connecting to Excel Data Source

If you are new to PowerApps, please go through my previous blog

In my last Post building, I explained the steps to build an App using Excel file as Data Source.

While I was exploring more with that App, I encountered an error when playing with different excel files.

PA_Excel9

Reason & Fix:

  • If you have formulas defined in the Excel file, PowerApp will throw error as the PowerApps are not supporting Excel containing Formulas at this point.
  • Fix was, I had to delete Formulas and keep only data in the excel file to run the App.

🙂

Advertisements
Categories: PowerApps Tags: , , ,

PowerApps – Build an App using Excel data as Source

August 13, 2018 2 comments

If you are new to PowerApps, please go through my previous blog

In this post, I am going to walk you through building an App using Excel as ‘Data Source’.

I am going to build an App by

  • Prepare an Excel file with data formatted as ‘Table’
  • Place it in ‘One Drive For Business’.

Pre-requisites:

  • You must have a PowerApps account. Refer blog for details.
  • An Excel sheet with data formatted as ‘Table’.
  • ‘One Drive For Business’ account
    • This comes free with Dynamics 365 30 days trail.

Steps to configure Excel:

  • Create an Excel file with some data. I prepared ‘Customer’ records with 4 columns.
  • You should format this data as ‘Table’ as the PowerApp, only consider Tables, which you will notice in next sections.

PA_Excel1

  • Provide name to the Table; We will use this Table name in ‘Power App’. I named my table as ‘Customers‘.

PA_Excel2

  • Save the Excel

Steps to upload Excel to OneDrive:

PA_Excel3

  • Connect to ‘OneDrive’ and upload the Excel File

PA_Excel4

Steps to create App:

  • Login to PowerApps Portal
  • Create a New App with ‘OneDrive for Business’ template, as I uploaded the Excel to OneDrive.

PA_Excel5

  • Note: Its not necessary that you should pick ‘OneDrive for Business’ template, you can even create a ‘Blank app’ and create ‘OneDrive’ connection.
  • Click ‘Create’ to establish App’s connection with ‘OneDrive For Business’.

PA_Excel6

  • Choose the Excel file

PA_Excel7

  • Select the ‘Table’ and click ‘Connect’; This table ‘Customers‘ is nothing but the table name we given in ‘Excel sheet’.

PA_Excel8

  • Now you would experience the power of ‘Power Apps’ as it instantly come up with UI using the excel data. App auto create 3 screens for Browse Screen,Details Screen and Edit Screen.

PA_Excel10

  • You can reorder the columns by selecting ‘Data’ or you can change the ‘Sort Column’ if you want.
  • Finally, press F5 to run the App.

PA_Excel11.PNG

Refer my previous blog to build a simple calculator App.

🙂

 

Bulk Updating or Inserting records from Excel using CRM Data Import

January 8, 2012 1 comment

Hi,

In CRM, you may come across scenarios where you need to Update/Insert a very large number of records very quickly. In these cases, opening each record’s form to make the change can be time-consuming.

We can handle this better by using CRM Data Import feature by which we can make bulk Update/Insert very quickly.

Below are the steps to achieve this. I am using ‘Contact’ entity for this article.

Steps :-

  • Navigate to ‘Contact’ entity
  • Click on “Export to Excel” button in the Ribbon menu
  • In the “Export Data to Excel” dialog box which comes up, Select “Static worksheet with records” and check the “Make this data available…” check box.
Export Data to Excel Dialog Box

"Export Data to Excel" Dialog Box

  •   Save the file
  •  Open the Excel file and it looks as below
Records in imported file

Records in imported file

Important Points :-

* This Excel file has some unique characteristics which simplifies the data entry and re-import process.

* As you click on each cell, you will notice a pop-up that tells you format of the data and whether the field is required or not.

* Required fields are not enforced in Excel (i.e., Excel won’t validate even if you miss the value in required cell).

* If you miss a value in required field cell, the record wont be updated in the import process.

* You can enter “Lookup” values also, but the values must match with parent record.

For example, In this Excel “Parent Customer” cell is a lookup to the “Account” entity. So i need to give “Account” full name in the cell.

* If any wrong data entry in the lookup cell, the record will not be updated on import

  • In the imported Excel (Above screen), If you observe, I don’t have “Middle Name” for any of my contacts. So I am giving middle name as “Updated” (Below screen)
Updating "Middle Name" field

Updating "Middle Name" field

  •  Next, I want to insert a new record “Rajeev Pentyala” to my contacts.

          Important Point :-

– We can create new records by entering them in the bottom of Excel. (***Be sure to fill all required fields ***).

  • So, In my last row of Excel, I enter “First Name”,”Last Name” ,”Email”,”Parent Customer” (i.e.,Valid full name of existing Account)
Adding new record

Adding new record in the last row

  •  Save the Excel file. (Ignore the warning and continue saving)
Ignore warning and save

Ignore warning and save

  • In the CRM, Click on “Import Data” button in the Ribbon
  • In the dialog window browse the saved file
Import process

Import process

  • Click “Next” button
  • In the next window, click on “Submit” button
Import process - submit

Import process - submit

Import Porcess - Finish

Import Porcess - Finish

  • Refresh the CRM application
  • Now you can see the newly inserted contact “Rajeev Pentyala” with the given values in Excel
Import - New Contact

Import - New Contact

  • You can also verify the remaining contacts with the updated “Middle Name”
  • This is how we handle  “Data import” process

Tip :-

  • To verify your import finished successfully or any problems with it.
  • Navigate to “Settings -> Data Management -> Imports”
Data Import - Settings

Data Import - Settings

Imported File

Imported File - Success & Failures

This article provides you a basic knowledge about Data import from Excel.

Hope it helps 🙂