locked
Import list from excel file RRS feed

  • Question

  • Hi,

    I try import a table from excel file to become a SharePoint list.

    I want the first column in the excel to become the title column (linked to item).

    I did some tests and in each tests it tooks a different column of the excel to be the sharepoint column which is "linked to item with edit menu".

    What is the logic and how may i control which excel column will be that sharepoint column?

    thanks

    Sunday, October 10, 2010 3:42 PM

All replies

  • Hi Keren tsur,

     

    We can import data from Excel into SharePoint 2010 lists. The steps are as follows:

     

    1.       Go into SharePoint 2010 and select the option “More options…” under “Site actions”. This will open the create dialog of SharePoint 2010 which normally allows you to create content like sites, pages and lists. Look for something called “Import spreadsheet” and press the “Create” button.

     

    2.       “Import spreadsheet” will create a custom list from an Excel data file. Give the custom list a name and use the browse functionality to select the earlier mentioned xlsx file. Click on the “Import” button to start the import.

     

    3.       This will open Excel and let you select the range of cells to import into the custom list of SharePoint 2010. The first row in the Excel sheet will define the column names.

     

    For more information about Import and export data between Excel 2010 and SharePoint 2010, please refer to the following article:

     

    http://www.bloggix.com/archive/2010/06/15/import-and-export-data-between-excel-2010-and-sharepoint-2010/

     

    Hope this is helpful.

     

    Rock Wang


    Regards, Rock Wang Microsoft Online Community Support
    Tuesday, October 12, 2010 7:10 AM
  • thanks,

    actually i already imported the file but i have some  questions:

    1. What should be done to set the first column in the excel file which is imported - to become the list column which is "linked to item with menu"? (in SharePoint 2007 it used to be the first text column...)

    2. Is it not possible to update excel file (which is linked) so it'll update the SharePoint list? only one-way of update works here? (SharePoint to the iqy file?)

    Thanks

     

     


    keren tsur
    Tuesday, October 12, 2010 7:37 AM
  • Hi Rock,

    I too am running into the same error that Keren is.

    I have an Excel file (Office 2007) with cells in Row A as the column title and then the rest of the data in the rows below it. When I follow the procedure that you describe (as well as the blog that you reference) the data in Row A does not become the Column Names in my SharePoint list but rather imports all of it as entries with the colum names showing up as Column 1, Column 2, etc.

    Also, I am using a reference book and they provide an Excel file to download to use in the example (http://tinyurl.com/38h58qm).  When I create the List in SharePoint and press Import I have the option of selecting "Range Type: Table Range" and the Table Range automatically shows up.  When I use my own Excel file I do not get this option and again, when I select the Cell Range the First Row is not converted to the Column Title in my SharePoint List.

    I have examined the downloadible file and there apprears to be no difference between my file and it and so am confused as to what is happening. So any help that you can provide would be most appreciated.

    ***UPDATE***

    Using my simple Excel file, after selecting the Cell Range and pressing Import I see a line appear just above Row A and it looks like above each Column it has "Column 1, Column 2 ..." and what looks like a drop down option ... but as soon as I see it the import happens and there is no option/time to change anything.  Strange.

    EK

    Friday, October 22, 2010 1:12 PM
  •  

    Hello

    Have you created table inside excel sheet?

    if not then you need to create one table in exelsheet , then add coloums to that table.

    Insert data in that table then import it to sharepoint using "Import spreadsheet" option present in “More options…” under “Site actions.

    while importing use select rangetype as Table Range and select table name from drop down list present under it.

    Refer http://sharepointbuzzer.wordpress.com/2010/10/22/import-excel-sheet-as-sharepoint-list/ blog for detailed explation.

    it worked for me

    let me know if that works for you
     

     

     

    • Proposed as answer by EKK Tuesday, October 26, 2010 11:36 AM
    Saturday, October 23, 2010 4:55 AM
  • The import itself can be flaky and is not easy to deal with.  I want to help though, so... worse case scenario:

    1. Create a new custom list, and create all of the appropriate columns.
    2. Go into datasheet view and make sure that the columns are lined up in the same order that they are listed in the spreadsheet.
    3. copy and paste the contents of the spreadsheet over to datasheet view on the list.

    I don't recommend setting any required fields until after all the data has been copied over.  For troubleshooting, try copying just one row first, to see if any errors come up.  Usually errors will happen regarding a mismatch of field types.


    Laura Rogers, MCSE, MCTS
    SharePoint911: SharePoint Consulting
    Blog: http://www.sharepoint911.com/blogs/laura
    Twitter: WonderLaura
    Saturday, October 23, 2010 4:10 PM
  • Thank you very much for the help.

    Setting the Table Range and then importing the document into SharePoint worked exactly as expected.

    EK

    Tuesday, October 26, 2010 2:44 PM
  • Hi -

    I have the same issue as the others in this thread.  I want the first column in my spreadsheet to be the linked column in the generated SharePoint list, but SharePoint is selecting the 2nd column for that.  Even though both columns are text, SharePoint is converting the first column to multiple lines of text and the 2nd column to single line of text (which seems to be why that one is selected as the linked one, since the list Title is always single line of text).  This defies logic, since the 1st column values are nowhere near 255 characters - the largest is 1/4 that size. 

    Does anyone know how SharePoint determines the column type in the generated list?

    Thanks,

    Shelly

    Tuesday, April 10, 2012 9:17 PM
  • The issue seems to lie in respect to what version of Office you are using when importing the spreadsheet.

    If you are using 64 bit install then the spreadsheet will import using the first column that is single line text as the (linked to item) field.

    If you are importing using 32 bit office then it picks up the first column that has no entry or a numeric value and uses this as the Linked to Item field.

    I don't know why this is happening but we have investigated using many desktops with 64 bit and 32 bit and Office 2010 and the same issues arises using 32 bit. 64 bit office install works and imports fine.

    Windows 7, Office 2010, SharePoint 2010. IE10.

    Monday, July 7, 2014 1:11 PM