Quick Data Load for SharePoint 2013 Custom List

With the new SharePoint 2013 custom list ‘Quick Edit’, Microsoft have removed the (very cool) DataSheet view – which used a Microsoft-Access ActiveX plug-in.

This would be for cross-browser compatibility – and other devices – but it’s left a bit of a gap with regard to editing multiple rows.

Specifically, when needing to upload rows and rows of data – such as from Microsoft Excel – case in point, with a spreadsheet I was emailed by a client.

There’s a simple step – using Microsoft Access 2013 (desktop tool).

Here’s the basics :

  • Create your SharePoint list – with the columns you need.
  • Eg.  Name, StateCode, Suburb (list of hotels for an InfoPath lookup)
  • Click on the “List Tab” and choose “Open with Access”

image

  • When prompted, choose to “Link to data” – NOT “Export a copy”

image

  • Flip back to your spreadsheet
  • Select the columns/rows
  • Right click – and hit “Copy” (or CTRL+C)

image

  • Back to Access
  • Highlight the cells – and click “Paste”

image

  • Takes a few seconds – and then the rows appear.

I’m using Office 365 – so takes a little longer – Melbourne –> Singapore – LOL !

image

  • Change over to SharePoint
  • Click F5 to refresh the page – too easy !

image

Hopefully that’s a useful tip for someone – cheers !

Advertisements

3 thoughts on “Quick Data Load for SharePoint 2013 Custom List

  1. Hi,

    Nice idea and haven’t thought to use Access for that kind of case. But just to be curious, what is the problem here with the multiple row data? You can just copy paste multiple rows from Excel to quick edit mode.

    Like

  2. Brilliant! I was just getting desperate with a 2000 rows of contacts to import. Creating the list as “Import Spreadsheet” does not do the trick, because it guesses the column types randomly (single line of text / multiple line of text). Access also handles the linked values (Lookup) fast and correctly. Thanks!

    Like

  3. Hi,
    Nice idea! Have you tried bulk upload longitude and latitude to a Geolocation field using Access? I’m wondering if it can work. Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s