2

I have around 1400 records that I would like to import in SharePoint list and entering each URL manually with description doesn't make much sense. Is there any way I can import these URLs in either data sheet view or Access Database synced with this list. I do know that I can use Content Editor web part if I enter these URLs with HTML tags but that would be little complicated as these list will be displayed on multiple pages and I would have to add Content Editor Web part on all the pages where I show this list.

2

I write hyperlink columns to lists all the time using a simple SharePoint designer workflow. Whether you run the workflow on create (if you're creating your list via the import) or on update is up to you; your post implies that the list already exists and that you're extending it with a hyperlink column but it should work either way.

The workflow process is simple:

  1. Create plain text columns in the list for URL and Description and a Hyperlink column for the final value
  2. Open SPD and create a new workflow on your list (on create or on update, up to you)
  3. Specify conditions if needed (like "if Hyperlink column is empty")
  4. Add a workflow action in SPD to Build Dynamic String
  5. Store [URL column],[Description column] in Variable:[Variable name]
  6. then Set [Hyperlink column] to Variable:[Variable name]
  7. Stop workflow

The key is just that the hyperlink column consumes the URL and description as [URL],[Description]. You could probably work that out in Access as well, simply haven't tried.

  • Thanks SharePoint Eric, I will try this sometime today and will let you know how did it go. Really appreciate your time here. – Roshni Parikh Oct 20 '11 at 13:12
  • Hello Eric, Again, thank you for your time, but I think I followed your direction and still it didn't work out for me. Here is what I had. I created 3 columns. 1. Link, 2. Description and 3. Hyperlink. and was combining the Link and Description column using SPD workflow in a variable and add that variable to Hyperlink column. but it gave me http://[google.com],[google] below value instead of giving me URL with Google word. Let me know if you have more ideas. – Roshni Parikh Oct 20 '11 at 15:02
  • At a glance I'd say that you typed the literal column names into the string builder in the variable instead of using workflow lookups. In step 5 above you'd build the dynamic string by clicking the Add Lookup button at the lower left corner of the string builder dialog, selecting the Link column from the list, then typing a comma, then clicking Add Lookup again and selecting the Description column. Does that sound right? – SharePointEric Oct 20 '11 at 18:26
1

Use a formula in Excel =Hyperlink(A1,B1) where A1 is the URL and B1 is the description. Then copy/paste to SharePoint Datasheet view.

This may work for SharePoint 2010 Datasheet view, but does not work for SharePoint 2013 Quick Edit.

0

If you need to bulk enter 1400 exact same URLs into each of the records, you can enter the hyperlink as http://www.yahoo.com. Then right click the cell, and click Hyperlink, Edit Hyperlink and put in the description. Then this can be dragged down and applied to every cell.

Access and Datasheet view act the same, which is no surprise since the datasheet view is Access.

If they are all unique it won't work. I have used a Nintex workflow in the past to build a dynamic string of url, description and then set the hyperlink field that way. There may be other options out there like Powershell or SPService (web services) to do bulk updates of unique values.

  • 1
    Yes, they are all unique values. so dragging down and applying it to every cell wont work. Thanks for your reply. Nintex workflow can't be solution for me as its a one kind of application I am working and I would not be able to get funding for this. can you elaborate more on Powershell or SPService. – Roshni Parikh Oct 17 '11 at 16:15
  • My reputation is not good enough to comment, but I can offer this: 5. Store [URL column],[Description column] in Variable:[Variable name] Please note that you MUST have a space after the comma for it to work: 5. Store [URL column], [Description column] in Variable:[Variable name] – user51691 Apr 11 '16 at 14:10
0

For anyone unfamiliar with the issue, this video describes it well and offers a solution: youtube.com/watch?v=i7NWwmEMx80

Short answer:

You can't copy links from Excel and have them show up in a SharePoint list, unless they are specially formatted:

SharePoint Stack Exchange#http://sharepoint.stackexchange.com

and pasted into the list when opened with Access.

Long answer:

I'm going to provide the solution I did from start to finish: how I did this with links I was extracting from an existing list, then how I copied them to a new one.

Extraction

I went to the existing list and exported the list to Excel. I then opened a new, additional workbook, and copied in the hyperlinks from the SharePoint-opened workbook by clicking on the column letter to highlight all links, Ctrl+C to copy, and then in the new workbook, select column A and Ctrl+V to paste. I then closed the workbook that opened from SharePoint. (I'm still old school from the days of SP 2007, and I believe even 2010, when messing with that workbook could actually manipulate data on the list, itself, so I never do anything but copy & immediate close that one. SP 2013 and on prevents it, but I don't trust Microsoft, and besides, it is way cleaner to have just the hyperlinks you want in an otherwise blank workbook.)

Create URLs

Now, you have your SharePoint hyperlinks with their display text. Perform these steps I grabbed from https://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel to get the URLs:

  1. Get into VBA (Press Alt+F11)
  2. Insert a new module (Insert menu > Module)
  3. Copy and Paste the Excel user defined function below into the module

Function:

 Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(0, 1).Value = HL.Address
    Next
 End Sub
  1. Press F5 and click “Run”, or just click the "play" button on the toolbar.
  2. Get out of VBA (Press Alt+Q or hit the Close "X" button at the top-right)

The URLs will appear in the column immediately to the right, in column B.

Get the Display Text In Its Own Column

I don't know if I had to do this next part, but I'm playing it safe:

  • Right-click the "A" in the Column A header. Select Insert. It will add a new "A" column and push content to the right. Your SharePoint hyperlinks are now in Column B, and their URLs are now in Column C. (I create the column first so I can avoid the cutesy "Paste Special" and "Insert Copied Values" commands and having to click on how I want to format my pasted values using icons that don't really give you a good idea of how it will look after it's pasted.)
  • Select the SharePoint hyperlink column (Column B), copy their contents, then open up Notepad (type "notepad" in a Run line or Windows search on your menu), and paste in the contents (Ctrl+V) to get just the display text.
  • Do a "Select All" (Ctrl+A), copy (Ctrl+C), then paste (Ctrl+V) into Column A. It will now contain the plain text version of the hyperlinks.

Create Your Coded Display Text/Hyperlinks

Next, in the fourth column, column D, add this formula to cell D1 (copy/paste into the formula bar at the top - it will add HTML markup if you try to do it to the cell, itself):

=CONCATENATE(A1,"#",C1)

This will concatenate the display text in column A with the URL the macro produced that should be in column C. I might've been able to use the SharePoint hyperlink column (column B) for its display text instead of A, but remember that underneath that text is a URL, so I just went the route I knew would grab the right parts I needed: Display Text#URL. You then should select cell D1 after it populates and drag the tiny square at the bottom-right of the cell down until the highlighted area meets the size of the filled in A/B/C columns.

Copy the Concatenated column to Notepad, and then back

If you try copying the Concatenated column directly into Access, I found it doesn't work. I ran the text through Notepad by copying the column data, pasting it into Notepad, "Select All" (Ctrl+A), and Copy (Ctrl+C), then back to Excel and Paste (Ctrl+V) into that column over top of the old Concatenation formulas.

Final part: Copying Your Coded Display Text/Hyperlinks into Access

Open up your SharePoint list in the browser, then select List in the ribbon and then Open with Access. Access will launch, and you can copy your data from Excel (highlight the concatenated column, then copy with Ctrl+C), and then paste into Access:

If you're creating new records with a Title field and a hyperlink field like I was, select the Title column and the hyperlink formatted column in Excel, copy with Ctrl+C, then paste by right-clicking the top-left block in the corner and select "Paste". If you have a lot of records, allow time for the records from Excel to finish copying to the clipboard before "Paste" will appear. Keep right-clicking that square - it will appear eventually. You can also use the * indicator on the left side to right-click on, which sits beside a (New) row.

Note that you may want to try one record out first if you have a large list with many fields, as you need to get the fields in Access in the same order as your Excel columns - they may differ as Access will use the "All items" view and yours may be different. The columns you are copying from Excel do not have to match in number, ex.: if you have columns A, B, and C on your SharePoint list and Access, but you are only copying A and B, as long as C is not required you do not have to highlight column C data in Excel or create a blank column C to match Access. You can get by with only highlighting column A and B in Excel and copying that. When you paste, Access will match what you copied and start filling them in, per the order of the columns it has on the linked table. So even if titles don't match, Access won't care and will let you screw up - it will put column A and B in the first and second columns you have there in Access, regardless, so be aware and be careful that the columns you are copying match the ones present in Access, up to the point you no longer have columns selected in Excel.

One more note: If you do only paste one row to test with, be sure that you still go back and grab ALL rows (including that test one) if you paste using that top-left corner square to Paste, because it will overwrite all rows that way, making you short a row if you only grab from your 2nd row, onward. If you use the * indicator on the left and start pasting after your 1st row (assuming a successful test), you should grab from your 2nd row onward in Excel and it will not overwrite your 1st row.

If Open with Access is greyed out

You need to install Access.

If you try pasting the content into Datasheet view on the site, itself, it will just think it's text and run it all together, give you an error on that cell, and it won't save your record.

If you already have Access installed, there's a known incompatibility issue with Office 2013 products, where you need to go into Control Panel > Program and Features and set "Microsoft SharePoint Foundation Support" to "Not Available" on each Office 2013 product individually, according to this: Open with Access grayed out

But the other way - just avoid that button altogether. Open Access, create a blank database, then go to External Data in the ribbon menu > More > SharePoint List. Enter the URL for your list. It will bring up a listing of all lists on that site, so you'll need to pick it (it's not smart enough to figure out you provided a URL to the list...). Then it'll open in Access.

-1

I found this solution: http://www.minasi.com/forum/topic.asp?TOPIC_ID=39895

Basically:

  1. Copy and paste the links into Datasheet view (so they show up as http: etc etc)
  2. Copy and paste the text you want to show as the Description for the Hyperlink on top of the hyperlinks.

This will retain the links but show just the text.

  • Good thing you summarized that link - it's broken, now. Too many don't. But then, say you have a bunch of links from another list, and you've exported them to Excel. It shows their friendly name, not the URL. Any ideas on how to grab the URL out of there without having to manually right-click each one, first? Btw, tried this with a URL, then pasted the text back over top of it, and the text overwrote the URL part, so this solution actually doesn't work - at least for SP 2013/2016. – vapcguy Apr 24 at 21:46
  • Actually, I found the way to extract the hyperlinks: howtouseexcel.net/… Use a macro: Sub ExtractHL() Dim HL As Hyperlink For Each HL In ActiveSheet.Hyperlinks HL.Range.Offset(0, 1).Value = HL.Address Next End Sub I'll post up an answer. – vapcguy Apr 24 at 22:02

protected by Community Apr 11 '16 at 19:27

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.