You'll see that any files that are at the root level of your library are listed before any folders, removing any confusion that they might reside in the last folder that's listed.Īt this point, you can get creative. The result is an index that shows the folder structure. You can now see the actual folder structure. Then sort A-Z on the new column you made. Click the down arrow icon next to Item Type, and you'll see the menu. You'll now need to sort the columns in specific way.įirst sort by A-Z on the Item Type column. This gives you something you can work with, but you're not there yet. This allows you to group folders along with the files that are in that folder. For items, it just retains the original path (that path is the folder in which the item resides). For folders, it appends the name of the folder to the end of the path, in the format of "path/name". Now that they have this new value, take a look at what it does. If you're familiar with Excel formulas, you might notice that the formula doesn't reference cells, but instead references columns in the table and since it's a table, all of the rows below will automatically populate. I've put some more helpful tips at the bottom of the article. The rest of the columns will automatically populate. In the next cell down, in my case F2, enter this When copying / pasting, I find it works better if you click that cell and enter the formula into the formula bar instead of directly in the cell. Once you do that, you'll see that that column becomes a part of the table. In my case, I clicked into F1 and typed the word "Index". To make a new column, click into the top cell of the column to the right of the last column in your table. The way to make this work is to add a new column and use a formula I invented. That's what the next step does.Ĭonfused? I get it! But stay with me once you do this a couple times you'll totally understand. In those cells' current state, they can't be used for grouping them together, but if the Paths in cells E2 and E8 were the same thing, then they could be grouped together. If you look at the end of the path in E8, you can tell that this file would be found in the folder named "Folder 1". For example in the image below, look at the folder named "Folder 1" in Cell A2, and the file "File in 1.txt" in cell A8. I then took a closer look at the Path column, and I realized that with some creative modification, I could leverage that column for sorting. If you look a the Path column you can get an idea of the location where each subfolder and file reside, but there's no intuitive way to sort the rows to show the actual folder structure. In the "Item Type" column, you'll see what you've probably come to expect: all of the folders are displayed first, followed by all of the files. You'll see the exported table will look something like the image below. I couldn't have my users doing that that, so I had to find or invent a better solution.Īfter a lot of trial and error I invented a great way anyone can create the index very quickly and easily.įirst, export to Excel by going to the Library tab, click Export to Excel and take the defaults. That's not a good plan because you're likely to make mistakes. I actually know of a user who planned to painstakingly cut and paste rows to put them in order because a client needed the index. Some are much better than others, but they all require technical skills that an average user would find too difficult, and / or require a good bit of data cleanup to get any benefit. I did find some excellent posts that explain some technical ways to get close to an index. The experience is that I would do an export from SharePoint to Excel, assuming the table should sort by an index showing the folder structure instead, it just shows all the folders, then all the files in alphabetical order.Īfter a whole lot of research, I found many great posts from other people asking if anyone knows of a good way to make it work but no easy solutions. I had also been frustrated by this for a long time. If you have any problems, or you'd like to collaborate on creative solutions for anything at all, feel free to reach out to me in LinkedIn.Īre you looking for a quick and easy way to view the folder structure when you export a SharePoint Document Library to Excel? You're not alone. I also give tips at the end to troubleshoot any issues that may arise. As a courtesy to save you from having to read this whole article (you're welcome!) - If you're familiar with how tables in Excel work, and you already exported from SharePoint to Excel, the short version of the solution is to make a new column to the right of table, use this formula and sort by that that doesn't mean anything to you, follow the detailed steps below.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |