How to Import Data from Excel

One of the more common requests from our customers is the ability to import data into LightSwitch applications. While this can be done programmatically, it’s pretty common to store data in Excel documents. This tutorial will walk through using an Excel Import extension that our team has built.

Download the Excel Import Extension


The Excel Import extension will allow you to add “Import from Excel” functionality to any existing LightSwitch screen. End users of the LightSwitch application can then import their business data when necessary. The extension will allow users to map columns of their Excel document to fields on LightSwitch tables. It will then validate the data and create records in the application.

Getting Setup

First we’ll create a couple of Excel documents that we will import data from. Create an Excel document titled Categories that contains the following information. Ensure that the document is saved in the Documents folder. Silverlight has limitations on what it can access on the file system.


Name Description
Beverages Soda and juice
Condiments Sauces, relishes, spreads, and seasonings
Dairy Products Prepared and fresh meats
Grains and Cereals Breads, cereals, crackers and pasta
Produce Dried and fresh fruit and vegetables
Seafood Seaweed and fish

Create another Excel document titled Products that contains the following information. Again, ensure that the document is saved in the Documents directory.




Sasquatch Ale



Aniseed Syrup



Steeleye Stout



Spiced Okra



Carpet Clean Plus



Scottish Longbreads


Grains and Cereals

Boston Crab Meat



Install the Extension

Download the attached extension on the machine that has LightSwitch installed. Open the zip file, navigate to the Binaries folder and double click on the ExcelImporter.vsix file. This should install the extension for LightSwitch.


Create a new LightSwitch application. Double click on the Properties node in the application explorer. In the application properties, navigate to the Extensions tab. Enable the LightSwitch utilities extension for this application. If you like, you can default it to enabled for all future applications.


Define the Application Data

Add a Category table with the following fields:

  • Name (String, Required)
  • Description (String, Not Required)


Add a Product table with the following fields:

  • Name (String, Required)
  • Quantity (Integer, Required)


Now click on the “+ Relationship” button in the command bar to open the “Add New Relationship” dialog. We will set a relationship between Product and Category. In our case, each Category has many Products and each Product must have a Category.


Created our Screens

Now that we have our data setup, let’s add screens to edit our Categories and Products. Add a new Editable Grid screen for Categories.


On this screen, let’s add a button to import data from Excel. Right click on the screen command bar and select “Add Button…” Name the method ImportFromExcel and click Ok.



Double click on the Import From Excel button and add the following code.

Private Sub ImportFromExcel_Execute()

   ‘ Write your code here.


End Sub

Similarly, add a screen for editing our Products. Add a new Editable Grid screen for Products.


Add a similar ImportFromExcel button to this screen with the following code.

Private Sub ImportFromExcel_Execute()

   ‘ Write your code here.


End Sub

Testing our Application

Build and run the application. The “Editable Categories Grid” screen should appear. Click on the “Import From Excel” button. This should open a dialog allowing you to select the file you wish to import from. Select the Categories excel document and click “Ok”.



The extension should now open a mapping dialog. This will allow you to map columns in the Excel document to fields on your LightSwitch table. The dialog will try to match the names of Excel columns and LightSwitch fields. Click on “Continue”.


The extension will now add records to the LightSwitch grid for each row in the Excel document. If necessary, you can edit the data prior to saving it. Save and close this screen.


Now open the “Editable Products Grid” screen. Click on the “Import From Excel” button and select the Products excel document.


The mapping dialog will again match up the Excel column names and the LightSwitch field names. In this case, the Category field is selected, even though this represents a relationship. The extension will attempt to look up matching categories in the Category table when importing the data from this document. Click on “Continue”.


The extension will validate any data prior to importing it into LightSwitch. Any errors will be displayed back to the user. In this case, a category called “Cleaners” was specified for one of the products. This category couldn’t be found. Additionally, “None” was specified as a quantity for one of the products. This can’t be represented as an integer. Click on “Continue” to correct any data errors in your LightSwitch screen.


In your LightSwitch screen, change the category of the Carpet Clean Plus product to allow it to be saved.  You can also modify the quantity of Scottish Longbreads if necessary. Save the screen when you’re done fixing up the data.


The attachment includes both the source code for the extension and an installable VSIX package. Building the source will require Visual Studio Professional and the Visual Studio SDK to be installed.  This extension isn’t intended to be production quality (it won’t work when used from a Web application, for example). 

To get started building your own extensions, check out the Developer Center.