How to Import an Excel File to a SharePoint List with List Bulk Import 2.5

How to Import an Excel File to a SharePoint List with List Bulk Import 2.5

by | Jun 29, 2012 | The Bamboo Team Blog

Update: List Bulk Import is now available from the Bamboo Solutions storefront.

With its friendly user interface, List Bulk Import allows users to easily migrate documents, pictures, and list data into SharePoint from: legacy databases; spreadsheets; SharePoint to SharePoint Server; Office 365; Claims-based and Forms-based Authentication environments; and/or any SharePoint document management application with large volumes of data and/or documents that need to be widely accessed.

In this post, I will demonstrate all of the configuration possibilities provided List Bulk Import.  Part of what makes List Bulk Import so powerful is the ability to import from various sources, including:

  • 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 and 2008) Table or View
  • Existing SharePoint List or Library (2007, 2010, and SharePoint Online)

In this article, I will demonstrate importing from an Excel file to a Tasks list, however, the same steps described can be used for any of the sources mentioned above.

To start, we have the following Excel file that contains seven columns:

TIPS: For a successful import, here are some special cases regarding importing columns from Excel to particular data types in SharePoint:

  • Multiple Lines of Test (Rich and Enhanced): Importing basic rich formatting (bold, italic, underline, bullets) is supported. All other formatting will import, although with errors.
  • 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 semi-colon “;”, for example A;B;C will be read List Bulk Import as 3 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 import.
  • 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 a Description will fail.
  • Picture: Only supports importing the URL; including a 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.

Now that we have our Excel file, it’s time to start List Bulk Import.

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. TIP: List Bulk Import is expecting the Site URL.  An example would be 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.

NOTE: If your target SharePoint site is SharePoint Online, note that List Bulk Import only supports accounts provided SharePoint Online. If your company has implemented a custom authentication provider using CBA, it is currently not supported.

After a successful login, you will be presented with a landing screen with several options.  Here, we will Create Import Job.  You can also access this mode going to the Import Tools menu.

As mentioned earlier, List Bulk Import supports a variety of data sources but, for our example, we are importing an Excel file, so select the first option, Excel or delimited text file:

In the next step, there are two parts to be completed: Selection of Source file and selection of destination (target) list:

In the Source Information tab, browse to our example Excel file. 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 (,) and then you can select the “Field Data Delimiter, which is used to specify the value for each column (useful if your value potentially contains a comma in the value).
  • TXT – Both Field Separator and Field Data Delimiter are available for your choosing.

But in our case we are importing an Excel file:

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

List Bulk Import expects individual items being imported to be in a left-to-right format, however, if you are importing a financial data sheet, it could possibly be in a top-down format.  To resolve this, select Transpose selected Excel Sheet. This will display two additional settings:

  • Starting Cell is the uppermost left cell you want to import in the source.
  • Ending Cell is the lowermost right cell you want to import in the source.

Enabling this option and defining the Starting and Ending Cells will result in List Bulk Import rotating the selected data range so the data is read Left-to-Right rather than top-down:

  

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

NOTE: If you created the List or Library AFTER connecting to the target SharePoint Site, it will not be displayed in the drop-down. You must “Reset Connection” in the “File” menu.

The next step is the mapping of the Target and Source columns. List Bulk Import tries to help automatically mapping the Source and Target columns based on the display name in the source and target.

NOTE: For list Bulk Import to successfully automatically map the Source and Target columns, the columns display name 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 simply set the Source Column drop-down to None. NOTE: If the Target column displays an asterix (*), 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 as well.

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 what root folder to import to. This is only available for Document Libraries and disabled if the Target is a SharePoint List:

The next screen is a listing of mapped target columns that are of 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 for the attribute of interest:

A new window will appear with two list boxes:

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

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

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

The next step defines how the source values match existing data in the Target list. Since SharePoint columns do not enforce unique values (unless you are running SharePoint 2010 and have that option enabled), we allow you to define which columns must match for a record to be considered a duplication.  Next, you define what happens if the Source record matches a target list item, skipping the record, or update 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’s a part of SharePoint architecture to store changes in 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 – Optionally save the import configuration to a tempalte file for future one-time imports or recurring scheduled imports.
  • [Run] Import – Will execute the one-time import.

When completed, select View Log to see details about the import successes and/or failures:

 

If you’re ready to give release 2.5 of List Bulk Import a try, it’s available in Bamboo Labs as a free download today! With this Beta version, there is a 15-item limit, and the trial is good for 30 days. If you like what you see, you can purchase a license key from the storefront to fully activate the product and circumvent the 15-item limit. Please note that this Labs version of the product is still in Beta and we ask for your patience if you experience any issues. To report any problems you may encounter, please go to the List Bulk Import 2.5 forum and post your feedback there.

Happy importing!

Read the entire series celebrating the release to Bamboo Labs of List Bulk Import 2.5:

Get a Demo
Contact Us
Newsletter Signup