SOLVED

Importing Hyperlinks from an excel spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-653291%22%20slang%3D%22en-US%22%3EImporting%20Hyperlinks%20from%20an%20excel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-653291%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20scouring%20and%20have%20tried%20everything%20I%20have%20found%20so%20far%20with%20no%20luck%20(add%20an%20app%20-%20import%20spreadsheet%20etc%20etc)%20I%20have%20an%20excel%20spreadsheet%20that%20contains%20url%20hyperlinks%20and%20display%20names.%20I%20would%20like%20to%20import%20this%20list%2C%20displaying%20the%20display%20name%20but%20following%20the%20url%20when%20clicked.%20I%20cannot%20seem%20to%20get%20this%20to%20work%20at%20all%20on%20Office%20365.%20I%20really%20do%20not%20want%20to%20have%20to%20manually%20add%20these%20700%20urls%20and%20link%20them%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20advice%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-653291%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658827%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Hyperlinks%20from%20an%20excel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F350309%22%20target%3D%22_blank%22%3E%40Mindbender%3C%2FA%3E%26nbsp%3BI%20figured%20out%20something%20that%20will%20work.%20I%20created%20a%20list%2C%20renamed%20the%20Title%20column%20to%20Display%20Text%2C%20added%20a%20column%20type%20of%20Hyperlink%20named%20URL.%20I%20then%20went%20into%20Quick%20Edit%20and%20was%20able%20to%20copy%20my%20data%20from%20excel%20and%20paste%20it%20into%20the%20list%20template%20in%20Quick%20Edit%20by%20highlighting%20both%20columns%20and%20hitting%20Ctrl%2Bv.%20I%20then%20added%20this%20list%20as%20an%20app%20part%20to%20a%20page.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1214427%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Hyperlinks%20from%20an%20excel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1214427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F350309%22%20target%3D%22_blank%22%3E%40Mindbender%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20too%20have%20searched%20all%20over%20the%20web%20with%20little%20results.%20However%20I%20discovered%20a%20easy%20trick%20that%20worked%20for%20me%20like%20a%20charm.%20The%20trick%20is%20to%20use%20a%20TAB%20delimited%20text%20file%20where%20the%20columns%20match%20the%20number%20of%20columns%20in%20your%20SharePoint%20list.%20You%20then%20edit%20the%20list%20in%20Quick%20Edit%20then%20copy%20and%20paste%20from%20your%20TAB%20delimited%20text%20file.%20I%20used%20Notepad%2B%2B%20to%20edit%20the%20text%20file%20when%20I%20do%20the%20copy%20and%20paste.%20You%20can%20use%20Excel%20to%20generate%20the%20text%20file%20quickly%20but%20you%20will%20have%20to%20edit%20the%20file%20in%20Notepad%2B%2B%20before%20you%20do%20the%20copy%20and%20paste.%20The%20trick%20is%20with%20the%20URL%20column...of%20course%20because%20that%20it%20why%20this%20issue%20exists.%20In%20the%20TAB%20delimited%20text%20file%20the%20URL%20column%20will%20be%20formatted%20as%20such%3A%20%5B%22URL%20text%22%2C%22Pretty%20Description%22%5D.%20The%20bracket%20are%20just%20for%20emphasis%20and%20are%20not%20needed.%20The%20reason%20why%20you%20have%20to%20edit%20it%20from%20Excel%20is%20because%20it%20will%20save%20the%20URL%20column%20as%20%22%22%22URL%20text%22%22%2C%22%22Pretty%20Description%22%22%22%20and%20on%20some%20of%20the%20URL%20entries%20it%20will%20put%20a%20TAB%20at%20the%20end%20of%20the%20URL%20and%20then%20the%20paste%20will%20fail%20because%20the%20number%20of%20column%20do%20not%20match.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rangelin_0-1583524903684.png%22%20style%3D%22width%3A%20629px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F175697i6FFF6B45D5F37191%2Fimage-dimensions%2F629x118%3Fv%3D1.0%22%20width%3D%22629%22%20height%3D%22118%22%20title%3D%22rangelin_0-1583524903684.png%22%20alt%3D%22rangelin_0-1583524903684.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20is%20a%20picture%20of%20my%20list.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rangelin_1-1583525083750.png%22%20style%3D%22width%3A%20672px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F175698i46EDB599677AEF1F%2Fimage-dimensions%2F672x34%3Fv%3D1.0%22%20width%3D%22672%22%20height%3D%2234%22%20title%3D%22rangelin_1-1583525083750.png%22%20alt%3D%22rangelin_1-1583525083750.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20here%20is%20a%20picture%20of%20my%20TAB%20delimited%20text%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20not%20go%20into%20great%20details%20here.%20Start%20by%20by%20playing%20with%20a%20simple%20two%20column%20list%20like%20I%20did%20and%20then%20expand%20to%20a%20bigger%20list%20as%20you%20figure%20out%20what%20works.%20Just%20remember%20the%20trick%20with%20the%20URL%20column%20and%20the%20rest%20of%20the%20text%20columns%20will%20just%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20helps%20people%20out%20there%20as%20much%20as%20it%20did%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

hello,

 

I have been scouring and have tried everything I have found so far with no luck (add an app - import spreadsheet etc etc) I have an excel spreadsheet that contains url hyperlinks and display names. I would like to import this list, displaying the display name but following the url when clicked. I cannot seem to get this to work at all on Office 365. I really do not want to have to manually add these 700 urls and link them

Any advice?

2 Replies
Highlighted
Best Response confirmed by Mindbender (Occasional Contributor)
Solution

@Mindbender I figured out something that will work. I created a list, renamed the Title column to Display Text, added a column type of Hyperlink named URL. I then went into Quick Edit and was able to copy my data from excel and paste it into the list template in Quick Edit by highlighting both columns and hitting Ctrl+v. I then added this list as an app part to a page.

Highlighted

@Mindbender 

I too have searched all over the web with little results. However I discovered a easy trick that worked for me like a charm. The trick is to use a TAB delimited text file where the columns match the number of columns in your SharePoint list. You then edit the list in Quick Edit then copy and paste from your TAB delimited text file. I used Notepad++ to edit the text file when I do the copy and paste. You can use Excel to generate the text file quickly but you will have to edit the file in Notepad++ before you do the copy and paste. The trick is with the URL column...of course because that it why this issue exists. In the TAB delimited text file the URL column will be formatted as such: ["URL text","Pretty Description"]. The bracket are just for emphasis and are not needed. The reason why you have to edit it from Excel is because it will save the URL column as """URL text"",""Pretty Description""" and on some of the URL entries it will put a TAB at the end of the URL and then the paste will fail because the number of column do not match.

rangelin_0-1583524903684.png

Here is a picture of my list.

rangelin_1-1583525083750.png

And here is a picture of my TAB delimited text file.

 

I did not go into great details here. Start by by playing with a simple two column list like I did and then expand to a bigger list as you figure out what works. Just remember the trick with the URL column and the rest of the text columns will just work.

 

I hope this helps people out there as much as it did me.