How to Import an Excel File into a Tasks List using List Bulk Import 3.0 for SharePoint 2013

How to Import an Excel File into a Tasks List using List Bulk Import 3.0 for SharePoint 2013

by | Apr 3, 2013 | SharePoint 2013

With its user-friendly interface, List Bulk Import allows users to easily migrate documents, pictures, and list data into SharePoint from a broad range of sources. These sources include:

  • Excel files
    • Windows (32-bit) – Excel 2003, Excel 2007 and Excel 2010 (32-bit only)
    • Windows (64-bit) – Excel 2010 (64-bit only)
  • Text Delimited files
  • Comma Delimited files
  • SQL Server (2005, 2008 and 2012) Table or View
  • Existing SharePoint List or Library (2007, 2010, 2013, and SharePoint Online)

In this article, we will demonstrate how to import an Excel file into a Tasks List.  Please note that the steps listed below can be applied to importing any of the sources listed above.

To start, we will use the following Excel file which contains 7 columns:

Some helpful tips before you begin your import: Here are some special cases regarding importing columns from Excel to particular data types in SharePoint:

  • Multiple Lines of Text (Rich and Enhanced): Importing basic rich formatting (bold, italic, underline, bullets) is supported. Attempting to import other formatting will result in an error.
  • Number (Percent): Excel column has to be formatted as either Number or General. List Bulk Import does not support source columns formatted as percent.
  • Choice, People & Group, and Lookup:
    • If you want a source row in Excel to store multiple values, separate each value with a semi-colon (;), for example A;B:C will be read List Bulk Import as three separate values.
    • During configuration of the import, you will map each UNIQUE value in the Excel Source column to an available target column value. As a result, the Excel values do not require a direct update prior to importing.
  • Date and Time columns: List Bulk Import supports the following date formats (NOTE: time value is not supported)
    • MM/DD/YYYY
    • Month DD, YYYY
  • Hyperlink: Only supports importing the URL (Including the description will fail).
  • Picture: Only supports importing the URL (Including the description will fail).
  • Attachments: List Bulk Import supports uploading attachments to list items. Create a column (in our example, “Path”) that provides the location of the attachment file.

How to import an Excel file into a Tasks List:

Upon opening List Bulk Import, you will be asked to initially configure the target SharePoint Site.  This is where you will enter the URL to your on-premises SharePoint site, or SharePoint Online site.  Please note that List Bulk Import expects a Site URL.  For example, if you were to access your SharePoint Online site, the browser might display https://HostName.onmicrosoft.com/site/default.aspx.  You will want to enter https://HostName.onmicrosoft.com/site in the target site URL field.

Also, please be aware that if your target SharePoint site is SharePoint Online, List Bulk Import only supports the accounts provided SharePoint Online. If your company has implemented a custom authentication provider using CBA, at the present time, it will NOT be supported.

After you have successfully logged in, you will be presented with a landing screen displaying several options.  For this how-to, we will be using the Create Import Job option.  This mode can also be accessed going to the Import Tools menu:

As mentioned earlier, List Bulk Import supports a variety of data sources.  In this example, we are importing an Excel file, so we will be selecting the first option Excel or delimited text file:

In the next step, there are two different actions that need to be completed: the Selection of Source file and the Selection of Target (destination) List:

In the Source Information tab, browse to the Excel file which you intend to import. Depending on the file type you select (.txt, .csv, .xls/xlsx) the import file information section changes.

  • CSV – Since CSV is a comma delimited file, the “Field Separator” drop-down is locked to Comma (,). Next, select the “Field Data Delimiter, which will be used to specify the value for each column (especially useful if your value potentially contains a comma).
  • TXT – Both Field Separator and Field Data Delimiter are available as choices.

In the case of Excel files, the first option is to select the Worksheet you want to import from the Excel workbook. Next, select if the first row is column headers or actual values.

List Bulk Import anticipates individual items being imported to be in a left-to-right format. If you are importing a financial data sheet, it’s possible that your data is in a top-down format.  To resolve this, check the box marked Transpose selected Excel Sheet. This will display two additional settings:

  • Starting Cell – The upper left-most cell you want to import in the source.
  • Ending Cell – The lower right-most cell you want to import in the source.

Enabling this option and defining the Starting and Ending Cells will cause List Bulk Import to rotate the selected data range so the data reads left-to-right versus of top down:

After selecting the Source Information, select Target Information.  Here you will select the List or Library you want to import your data to.

Please note that if you create the List or Library AFTER connecting to the target SharePoint Site, it will not be displayed in the drop-down. You must go to the File menu and select Reset Connection.

The next step is the mapping of the Target and Source columns. List Bulk Import will attempt to automatically map the Source and Target columns based on the display name in the Source and Target. Please be aware that in order to successfully automatically map the Source and Target columns in List Bulk Import, the columns’ display names must match AND the columns must be in the same order.

By default, all target columns are automatically selected. If you prefer to only update a small set of columns, simply select None from the drop-down menu, or set the Source Column drop-down to None. Please note that if the Target column displays an asterisk (*), this means that it is a required field and, if selected, a Source column has to be selected as well. Optionally, you can map the same source column to multiple target columns.

To quickly start over, you can select Clear at the top of Target Columns or Source Columns to reset all drop-down menus to None.  Upon doing so, the button text will switch to Default which will again automatically associate columns.

The final icon (which is greyed out at this time) is a folder icon where you are able to select which root folder to import into. This is only available for Document Libraries and disabled if the Target is a SharePoint List:

The next screen will be a listing of mapped target columns which have the following data types:

  • Choice
  • Lookup
  • Person or Group
  • Yes/No

This step allows you to map the unique values from the source data to the available values in the target list column. To define the mapping of values, select Edit Mapping:

A new window will appear with two list boxes:

  • Target List Values contains a list of available values found in the Target List or library.
  • Mapped Values contains two columns: Source Column – a listing of UNIQUE values found in the source – and Target Column, which will initially appear blank until you map a Target column value to the source value.

If the Source and Target columns contain values with the same display name, you can optionally click Auto Map and the mapping will automatically occur for all values that match. If the values do not have the same display name, select your target and one or more source values and click Map.

Once completed, you can either click Next to move to the next column for mapping, or Close to return to the main page:

The next step defines how to source values that match the existing data in the Target List. Since SharePoint columns do not default enforce unique values (Note: If you are running SharePoint 2010 or SharePoint 2013 and have that option enabled, you ARE able to enforce unique values), we allow the user to define which columns must match for a record to be considered a duplication.  Next, you will define what happens if the Source record matches a target list item, skipping the record, or updating the record with the values stored in the source. If you select to update the SharePoint List item, a subsequent option will be available to run Bamboo cleanup. It is part of SharePoint architecture to store changes to the recycle bin as a backup. If you do not want to store the original records, select the checkbox and List Bulk Import will cleanup the recycle bin when completed:

The final screen provides a summary of the import.  In this screen, you can:

  • Empty Target before import – This will clear all items from the selected target list or library before the import starts.
  • Save Import Template – This will save the import configuration to a tempalte file for future one-time imports or recurring, scheduled imports.
  • [Run] Import – This will execute a one-time import.

Once completed, select View Log to see the details of your import, including successes and failures:

As with all Bamboo products, we invite you to download a 30-day free trial of List Bulk Import. As always, our product enhancements are driven listening to our loyal customers, and we welcome you feedback in the List Bulk Import forum.

For more how-tos, use cases, helpful hints, and best practices for List Bulk Import 3.0 for SharePoint 2013, check out our entire series:

Get a Demo
Contact Us
Newsletter Signup