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.
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:
- Create plain text columns in the list for URL and Description and a Hyperlink column for the final value
- Open SPD and create a new workflow on your list (on create or on update, up to you)
- Specify conditions if needed (like "if Hyperlink column is empty")
- Add a workflow action in SPD to Build Dynamic String
- Store [URL column],[Description column] in Variable:[Variable name]
- then Set [Hyperlink column] to Variable:[Variable name]
- 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.
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.
For anyone unfamiliar with the issue, this video describes it well and offers a solution: youtube.com/watch?v=i7NWwmEMx80
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.
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.
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.)
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:
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert menu > Module)
- Copy and Paste the Excel user defined function below into the module
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
- Press F5 and click “Run”, or just click the "play" button on the toolbar.
- 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):
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.
Service: SharePoint Online
You can add a hyperlink with description in grid view with copy/paste from Excel as follows:
In Excel you will need columns:
- Desc = hyperlink text
- URL = URL link
- HTML = formula to create an HTML hyperlink anchor
In your SharePoint List, you will need a column set as:
- Multiple lines of text
- Use enhanced rich text (Rich text with pictures, tables, and hyperlinks)
You can then copy/paste from Excel the HTML column into your SharePoint list column while in grid view (allowing adding dozens of rows at a time).
Results (including using an emoji)
I found this solution: http://www.minasi.com/forum/topic.asp?TOPIC_ID=39895
- Copy and paste the links into Datasheet view (so they show up as http: etc etc)
- 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.