Load Excel data into a SharePoint 2013 list fast and easy!

[et_pb_section bb_built=”1″][et_pb_row][et_pb_column type=”4_4″][et_pb_text _builder_version=”3.0.98″ background_layout=”light” link_text_color=”#46850d” header_3_text_color=”#7ec142″]

Checkout some of our newer blog posts!

[/et_pb_text][et_pb_divider _builder_version=”3.0.98″ show_divider=”on” color=”#46850d” /][et_pb_text _builder_version=”3.0.98″ background_layout=”light”]

Here’s an easy way to load Excel data into a SharePoint list. In this example I’m using SharePoint Online.

  1. Here I have a table of sample data in Excel.ExcelImport-1
  2. Click the SharePoint gear icon and select Site Contents.
  3. Click add an app.ExcelImport-2
  4. Click the Import Spreadsheet app.ExcelImport-3
  5. Give your new list a name. Then browse to the location of your spreadsheet, and click Import.ExcelImport-4
    Note: if you see this warning message you will need to add the SharePoint site to your list of Trusted Sites in IE.ExcelImport-4a
  6. The import dialog will appear, select a Range Type and then select your desired range.ExcelImport-5
  7. You will be prompted in Excel to sign in to SharePoint online.ExcelImport-6
  8. And it’s that simple! SharePoint has created a list based on my Excel data.ExcelImport-7
    SharePoint even selects the appropriate column type!ExcelImport-8

I hope this post helps when you need to get data from Excel into SharePoint fast!

Until next time!

[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row][et_pb_column type=”4_4″][et_pb_divider _builder_version=”3.0.98″ show_divider=”on” color=”#7ec142″ /][et_pb_text _builder_version=”3.0.98″ background_layout=”light” header_3_text_color=”#7ec142″]

Check out some of our newer blog posts

[/et_pb_text][et_pb_post_slider _builder_version=”3.0.98″ include_categories=”46,10,43″ orderby=”date_desc” content_source=”off” use_manual_excerpt=”on” show_arrows=”on” show_pagination=”on” show_more_button=”on” show_meta=”off” show_image=”on” image_placement=”background” show_inner_shadow=”on” use_bg_overlay=”on” use_text_overlay=”off” background_layout=”dark” custom_button=”off” button_icon_placement=”right” button_alignment=”center” show_content_on_mobile=”on” show_cta_on_mobile=”on” show_image_video_mobile=”off” posts_number=”10″ /][et_pb_divider _builder_version=”3.0.98″ show_divider=”on” color=”#7ec142″ /][/et_pb_column][/et_pb_row][/et_pb_section]

24 thoughts on “Load Excel data into a SharePoint 2013 list fast and easy!

  1. Hello. I tried this but I get an error message:” The list cannot be imported because a Microsoft SharePoint Foundation-compatible spreadsheet application is not installed or is not compatible with your browser.” I am using Excel 2010 version 14.0.7109.5000 SP 2 (32 bits) and Internet Explorer 9.0.8112.16421 (64 bits) and I already put the security level at a minimum for all the intranet…

    1. Hi Luis,

      It looks to me like an incompatibility issue between IE (64bit) and Excel (32bit). I would recommend trying with IE 32bit. Also, you could try with FireFox or Chrome.

      Let me know how that goes.

      Thanks!
      Mike

    2. How to Fix It

      All of the posts I’d read about this problem suggested editing the Excel Add-in file EXPTOOWS.XLA located in the Program FilesMicrosoft OfficeOffice 121033 folder. But editing that file didn’t help me a bit. Turned out there’s another version of that same file in Program FilesMicrosoft OfficeOffice141033, and that was the one that needed editing in my case.

      Before you begin, a few notes for the uninitiated:

      EXPTOOWS.XLA is a hidden file. In order to find it, you’ll have to show all hidden files on the system. Do this through the Control Panel, Appearance and Personalization, Folder Options, Show Hidden Files and Folders.

      The 1033 folder is read-only, so you 1) must be an administrator to write to the folder and 2) can’t just edit the file and save it back to the same directory.

      To modify the file:

      Once you’ve found the file, rename it to OldEXPTOOWS.XLA (this will give you a backup in case something happens). You may be prompted to confirm the change because this is a read-only folder.

      Double-click on OldEXPTOOWS.XLA. This will open Excel but it will look like nothing else is happening. On your keyboard, press ALT-F11 to open the built-in Visual Basic editor. It will look something like this:

      Open the code window and scroll until you find this section:

      Sub Initialize(List, Title, URL, QuickLaunch)
      strQuickLaunch = QuickLaunch
      aTarget(iPublishURL) = URL
      aTarget(iPublishListName) = List
      aTarget(iPublishListDesc) = Title
      lVer = -1 ‘ can’t tell STS server version
      If Val(Application.Version) >= 12 Then
      lVer = Application.SharePointVersion(URL)
      End If
      End Sub

      Comment out this line lVer = Application.SharePointVersion(URL) by adding a single apostrophe ( ‘ )to the beginning of the line.

      Add a line immediately beneath the commented line that reads lVer = 2 (that’s an L, as in lollipop). The section will now look like this:

      Sub Initialize(List, Title, URL, QuickLaunch)
      strQuickLaunch = QuickLaunch
      aTarget(iPublishURL) = URL
      aTarget(iPublishListName) = List
      aTarget(iPublishListDesc) = Title
      lVer = -1 ‘ can’t tell STS server version
      If Val(Application.Version) >= 12 Then
      ‘ lVer = Application.SharePointVersion(URL)
      lVer = 2
      End If
      End Sub

      Click File, Save. You will get an error message that the file is Read Only. Click OK to continue. A Save As dialog will appear. Change the file type to Excel Add-in (.xla). When you do this, the file location will change to the Excel addin directory – you will want to save the file to somewhere you can remember (like the Desktop). Make sure you’ve named it EXPTOOWS.XLA.

      Close the VB editor and exit Excel.

      Using Windows Explorer, navigate to Program FilesMicrosoft OfficeOffice141033.

      Copy the new file (EXPTOOWS.XLA) from your Desktop (or wherever you saved it) into Office141033. You will be prompted to confirm this action.

      When the new file is in the Office141033 folder, you should be able to import the spreadsheet into Sharepoint 2010 (although you may have to restart IE or the Sharepoint session).

  2. It’s relatively easy now in 2010 to create a NEW list from an Excel spreadsheet. What would be useful would be to be able to append additional rows from an XLS to an existing list in SharePoint. Right now in 2010 if the pre-existing list had ID fields or any calculated fields that is an impossible task using out of the box functionality. Will this become any easier to deal with in 2013 Online (or even On Premises for that matter)?

  3. This worked great, other than it did not automatically recognize my column headers. I’m not sure how to make it do that.

  4. Hey guys,

    I have created a Google URL Builder in Excel. One of the cells contains a formula that generates a tracked URL by scanning the data in the Website URL, Campaign source, medium, content and name cells.

    How do I get this to work in SharePoint with the formulas they offer?

    Thanks

  5. I’m trying to create list using excel with column having text with URL mapped on it. After uploading list on SP, I want the list column to be of type hyperlink but by default it is creating multiple line of text. Is there any way, we can restrict it to be of type hyperlink?
    Thanks in Advance.

  6. Genius! Brilliantly and clearly explained. Used this and it worked. Just a little tweaking was needed and my list displayed correctly. Thank you.

  7. Thanks for this! I’m wondering if anyone has some input on an issue I have. I have a process whereby a single excel spreadsheet gets generated by an outside process every time an event occurs. This CSV or Excel sheet then gets attached to an email and sent. Is there a way to have this email attachment (excel or csv file) examined and entered into an existing Sharepoint list? So, for example I have a list called Projects. Every time someone generates a NEW project from our ERP module (which basically ties existing customers to internal lab projects), one of these emails gets generated with an attachment that contains some basic info (Cust Name, Proj Description, etc.). I’d like to connect this data to a Sharepoint list. Perhaps I’m asking too much of the email to Sharepoint functionality. Any input is much appreciated!

  8. This is a frustrating issue especially when none of the EXPTOOWS.XLA files that exist contain that sub routine. For example, there is only one one this system and this is all it contains:

    Function PublishToOWS(List, Title, URL, QuickLaunch) As Variant
    On Error GoTo PublishToOWS_ErrHandling
    publishForm.Initialize List, Title, URL, QuickLaunch
    publishForm.Show

    If publishForm.returnCode.Caption = “0” Then
    PublishToOWS = 0
    Else
    PublishToOWS = publishForm.returnCode.Caption
    End If

    Exit Function
    PublishToOWS_ErrHandling:
    MsgBox Err.Description, vbOKOnly, publishForm.Caption ‘ Handle other situations here….
    Resume Next
    End Function

  9. Will this automatically update the data automatically if I make changes to the source file after initial import? If not, any suggestions on ways to auto update so that I do not have to keep creating newer versions of this app?

    1. No, the import spreadsheet app creates a static list that’s no longer connected to the source file. If you want to be able to update the list from a file, connect it to an Access database and update the db. changes made in the list will update the db too.
      Be aware updating to Office 2016 breaks this functionality and Msft has no clue how to fix it.

  10. I am getting the following error message after I select a range and click on Import button – “Cannot connect to the server at this time. Your table cannot be published.” How do I resolve this error?

  11. Can anyone give any details of what the process is when uploading additional meta data (from Xcel) to an already created list..? All these explanations give help on how to do it for the first time, or does not specify whether it is or not. And it would help if there was any more detail about the ‘name’ for the file. Again, is this the name used for an already created ‘ view’ or does it need to be a brand new name for each new upload? Thanks

  12. I am getting the following error message after I select a range and click on Import button – “Cannot connect to the server at this time. Your table cannot be published.” How do I resolve this error?

Comments are closed.