How to Import Data from Excel

How to Import Data from Excel

Rate This
  • Comments 46

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

Functionality

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.

Name

Quantity

Category

Sasquatch Ale

10

Beverages

Aniseed Syrup

15

Condiments

Steeleye Stout

8

Beverages

Spiced Okra

24

Produce

Carpet Clean Plus

6

Cleaners

Scottish Longbreads

None

Grains and Cereals

Boston Crab Meat

9

Seafood

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.

image

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.

image

Define the Application Data

Add a Category table with the following fields:

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

image

Add a Product table with the following fields:

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

image

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.

image

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.

image

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.

image

image

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

Private Sub ImportFromExcel_Execute()

   ' Write your code here.

   ExcelImporter.Importer.ImportFromExcel(Me.Categories)

End Sub

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

image

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

Private Sub ImportFromExcel_Execute()

   ' Write your code here.

   ExcelImporter.Importer.ImportFromExcel(Me.Products)

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”.

 

image

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”.

image

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.

image

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

image

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”.

image

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.

image

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.

image

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.

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • I've loaded the extension and everything works as described, very nice indeed.

    My question now is regarding the statement in the Introduction "The importer can validate the data that is being imported..." How do I go about setting this up? I didn't see any examples in the documentation regarding data validation, or is this assumed based on table constraints?

    Either way Bravo, this is a big asset! Well done team!

  • Lets say I perform an export on an entire table and I want to reimport the data and overwrite the existing table is there any way to do this using this functionality?     We have some Excel users comfortable with mass editing data with Excel and they often have to maintain a lookup table.   It'd be great if we could effectively pull the table out in excel, edit it and bring it back in.     Clearing the table and repopulating won't work due to relationships.

  • @Daniel

    - Validation in the sense that it uses the table's data types to ensure your data is valid.  

    @Jerry

    - We've been kicking around the idea to enable data-editing.  What you'd need to do is have the primary key in the excel document that you're importing.  This way we can look up the existing record and modify it accordingly.  I'll update this post once I've modified the importer to enable this.  

  • It can only be comma's are used as separators.

    Also country-specific characters are not supported äöü.

    We there be another adjustment?

    Gruss Nobi

  • @Gruss

    Thanks for the feedback.  For web applications, the sample only supports standard CSV files.  If you'd like to support some other file types, you can modify the extension's code.

    I just tried the extension with some global characters (japanese for example) and it appears to work fine. You will need to make sure that your CSV file is setup to correctly handle international characters (encoded in UTF-8).  

  • Hi LightSwitch Team, just wondering if you may have made any changes to the importer to allow for data-editing, as suggested by Jerry?  It is definitely something that is worth having.

  • Hi everybody,

    do you guys now whether a Excel Importer for C# is also available?

  • @John_C - You can use the extension in either VB or C# LightSwitch projects. However the source code is only available is VB.

  • Thank you very much for the quick help Beth. You are great :).

  • An alternative to this would be to use something like the RSSBus Excel ADO.NET Data Provider from LightSwitch. www.rssbus.com/lightswitch

  • Hi Lightswitch Team, your excel importer works very well for my purpose. Just wondering about a warning message during import a file.

    Column: Action Item Row:'n' Multiple matching 'column1' for 'value'. Will select first match.

    This appears if 'column1' is bound on another table (UsefulData1). The generated control is an Auto Complete Box. Actually the 'value' in the related table is double, but I use a filter where the Auto Complete Box just show unique values.

    An example:

    related table: UsefulData1

    Col A Col B

    ===== =====

    1 x

    1 y

    1 z

    2 x

    2 m

    2 n

    The import screen refers not to UsefulData1 table but to a filtered query where Col A=2. The Auto Complete Box shows {x,m,n}.

    I suppose this is because the import function is just looking for the first match of a corresponding collection (ExcelImporter.vb):

    Dim entitySetDefinition As IEntitySetDefinition = (From es As IEntitySetDefinition In entityContainerDefinition.EntitySets Where es.EntityType Is targetEntityType).First

    Is there a change to fix this or is there a workaround, I just avoid to use a custom or an own import extension.

    Thanks for help.

    PS: Lightswitch is awesome.

  • Hello.

    I extend the ExcelImporter.vb with one paramter. Then I going through the dictionary where I define the custom query.

    line 72 change function header:

    <Extension()> _

    Public Sub ImportFromExcel(ByVal collection As IVisualCollection, Optional ByVal collectionDefinition As Dictionary(Of String, IDataServiceQueryable) = Nothing)

    line 207 add code:

    If _collectionDefinition IsNot Nothing Then

     If _collectionDefinition.ContainsKey(targetEntityType.Name) Then

        dsQuery = _collectionDefinition(targetEntityType.Name)

     Else

        dsQuery = entitySet.GetQuery()

     End If

    End If

    Now the importer can be use like this:

    Dictionary<string, IDataServiceQueryable> querySet = new Dictionary<string, IDataServiceQueryable>();

    querySet.Add("ColumnName1", this.DataWorkspace.ProjectsData1.customQuery1(2));

    ExcelImporter.Importer.ImportFromExcel(this.ImportData, querySet);

  • Hi everybody,

    how can I print the imported data from Excel with my printer or pdf creator. I just want the Screepart with my information and not the menu!

    do somebody know about extensions for that?

  • Hi,

    is it possible to import data from an Excel file with many sheets, choosing the sheet from a list before the column mapping step?

    Di I need to modify the code source to do this?

    many thanks

  • @John_C, aliasale - Check out the Office Integration Pack extension instead as it has those features:

    Extension: visualstudiogallery.msdn.microsoft.com/35c4cf2a-5148-4716-afcf-0ccf8899cabf

    Sample: code.msdn.microsoft.com/LightSwitch-Office-dda65582

    Cheers,

    -Beth

Page 2 of 4 (46 items) 1234