Thursday, 12 March 2009

Excel to SharePoint

In these days where we are all told we have to recycle I am going to talk about an under used part of SharePoint (both MOSS and WSS) that allows you to convert your old Excel spreadsheets into new super SharePoint lists. But first I am going to moan about refuge (sorry ‘recycling’) collections in the West Midlands. Friday in my road is bin day. Well, it used to be ‘bin day’ now it is called ‘two large wheelie bins (one green, one black), a green box and a large square bag day’. Yes, for any pedestrian on a Friday in our street it is an obstacle race similar to the Grand National especially for old Reg with his zimmer frame. Okay enough moaning I know we need to recycle to help save the planet and I am doing my bit for the planet (don’t forget you can still buy my Car Sharing SharePoint Template at Buy It Here). Oh, and I must have watched every episode ever of ‘The Wombles’. It just gets me annoyed when you hear that ninety-two year old Mrs. Batty has been sentenced to twenty years in jail for putting her old copy of ‘Woman’s Weekly’ in the wrong bin. Okay exaggeration she only really got fifteen years. Surely in this technical age somebody could invent a machine that can sort our waste out for us? Back to SharePoint then after that politically incorrect rant. Many organisations have got Excel spreadsheets that contain useful information, maybe going back years. You might have a list of all your Purchase Orders, a customer details list or a catalogue all stored on Excel spreadsheets. Now you are using SharePoint you don’t need to throw out these spreadsheets or even upload them into a Document Library. Instead you can import them straight into SharePoint. Why would you want them in SharePoint? By having the data from the spreadsheet in a SharePoint list it makes it easier to filter the data, display the data to the right audiences, search the data, add new records to the list and most importantly it is easier for people to have access to the data. So how do you import the spreadsheet into SharePoint? If the spreadsheet is in Excel (any version since 97 to the current 2007) it is a very quick and painless process. It is worth checking first that the Excel columns are vertical going from left to right with the column names in Row 1. Open up the SharePoint site where you want to create the new SharePoint list that will contain the data from your spreadsheet. Click ‘Site Actions’ – Create. Then from the column ‘Custom List’ you will find ‘Import Spreadsheet’. Enter a ‘Name’ for the List and a ‘Description’, if you wish. Then in the ‘Import from Spreadsheet’ section browse to your spreadsheet. Oh, perhaps I should have mentioned earlier that you need to know where the spreadsheet is. Next click the ‘Import’ button. You will now get the ‘Import Windows SharePoint List’ screen as well as your Excel spreadsheet. This shows that it is working though WSS so you are not just restricted to MOSS. Cell A1 is highlighted but you can’t select all the cells until you change the dropdown ‘Range of Cells’ and then click in the bottom box ‘Select Range’. Now you can click on cell A1 hold the mouse button down and drag to the last cell in your spreadsheet so that all cells including the column titles are selected. Now click the ‘Import’ button and after a few seconds you will have a neatly presented SharePoint list with all the columns and data from your spreadsheet now displaying. You can add views and filters now to your heart’s content. Don’t forget to dispose of your excel file in the correct Recycling Bin after you have finished. SharePoint has many hidden gems like this and whilst I try and cover these in my blog I would always recommended attending a one day SharePoint Taster course with a company like Officetalk to really see what SharePoint can do. It might help you decide if you really need to invest in full MOSS or Windows SharePoint Services will do. Just hope my large recycling bag hasn’t blown away again after it has been emptied and that my empty wheelie bins aren’t blocking my drive when I get home.


Khushi said...

Hi Andy,

How you doing? Well, I read your post Excel to Sharepoint. I must appreciate you for sharing such an informative stuffs. I have a quick question for you. What would you recommend is the best approach to bring Excel spreadsheets which are heavily equipped with macros having complex formulas/ calculations/logics.


Alex said...

If you have this situation,then I recommend to use this application-Microsoft Excel the file error: there is a possibility that data is lost.,which in my view one of the best in this sphere,it has free status,also software is used to process important information, such as graphics, diagrams, commercial and statistical data,recover *.xlsx, *.xlsm, *.xltm, *.xltx or *.xlam formats,tool will help you if you have to repair Excel file error message: Excel error the file is not in a recognizable format,allows choosing a file, that shown an error like: Excel error message this file is not in a recognizable format, then, you should fix Excel file for errors and proceed with its analysis,attempts to recover your document with this message: Excel error and this file is not in a recognizable format and shows a preview window.

Veena said...

Hi Andy,

Thanks for the clear instructions. Can I import spreadsheets with multiple worksheets ?


Officetalk said...

Unfortunately not. You need a separate list for each worksheet.

Anonymous said...

Some days ago I reinstalled my MS Excel and on unknown reasons a lot my data were corrupted.To my surprise myself helped-xlsx corrupted,this tool I found in own computer archives.As far as I remember tool is free and in addition it can both recover all these graphics, interviews, tables and reports in Microsoft Excel format.

Alexis said...

For work with excel files I usually use-excel restore backdate,because tool is quite reliable and has free status.Besides that software can recovery of Microsoft Excel worksheets, performed by some other tools.