none
How to copy Excel sheet data to SharePoint 2010 List?

    Question

  • Hi,

       I need to export excel data to SharePoint 2010 list. I have created 22 columns in list which are of following Column types:

    Single line of text,

    Multiple line of text,

    Choice

    Number,

    Date,

    Person or Group.

    Now i need to export the excel data to SharePoint list.

    When iam trying to copy data from excel to List , it is showing as "The selected cells are read only".

    can someone guide on this to export spread sheet data to SharePoint list without importing Spreadsheet.

    Thanks in advance.


    Badri

    Tuesday, July 30, 2013 12:38 PM

Answers

  • Badri,

    Would you be prepared to anonymise the spread sheet, strip out the data from the list and save it as a template and send me these so I can look at what's happening?  I'd like to throw them in a dev box to see what's happening?

    If so, please can you send to baron_72@hotmail.com and I'll have a look tonight / tomorrow


    Cheers,

    Steven Andrews

    SharePoint Business Analyst

    Blog: Steve's SharePoint Space  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    • Marked as answer by Badrinarayana Thursday, August 01, 2013 2:25 PM
    Wednesday, July 31, 2013 9:03 PM

All replies

  • Plz try explorer view of list and only select the data not column when you are copying data 
    Tuesday, July 30, 2013 4:13 PM
  • Hey Yogesh,

       I tried copying data from excel to list, by opening list in datasheet view. Getting an error as

    "The selected cells are read only".

    Thanks.


    Badri

    Tuesday, July 30, 2013 6:03 PM
  • Lists don't have Explorer Views, so I've unproposed the answer that was nominated above.

    Badri, for your problem please can you take a look at the multiple lines of text columns.  These columns have three settings

    • Text
    • Rich text
    • Enhanced Rich Text

    If any of your columns are set to Enhanced Rich Text, than within the DataSheet view, they will be read only (for reasons I've yet to understand).  If you set these columns to either Text or Rich Text (the top or the middle option) and try again, you shouldn't experience the Read Only message anymore.


    Cheers,

    Steven Andrews

    SharePoint Business Analyst

    Blog: Steve's SharePoint Space  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Tuesday, July 30, 2013 6:52 PM
  • Hi Steven,

             I have changed multiple line of text setting to Rich text from Enhanced Rich Text  and trying to copy data from excel to List(datasheet view), again unable to do that...

    Getting error message as:

    Cannot paste the copied data due to data type mismatches or invalid data.

    some source rows which would have created new rows contained invalid values in required fields.These rows were skipped.

    For some colums in this list , I had made Allow fill in choices type choice column, Is this gonna effect copying cells from execl to list(datasheet view)

    Thanks.


    Badri

    Wednesday, July 31, 2013 1:55 AM
  • Notice how the error message has changed?  What that's saying is that your spreadsheet data probably has formatting, text, images or something beyond Rich Text functionality within those cells. 

    Try formatting one row and change the multi-line cells to minimal formatting then copy/paste that into the DataSheet View.  If it works, than you'll have your answer.


    Cheers,

    Steven Andrews

    SharePoint Business Analyst

    Blog: Steve's SharePoint Space  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Wednesday, July 31, 2013 1:59 PM
  • I don't know if you have tried this, but have you used the import spreadsheet functionality to import the sheet, and after it has been imported changing the columns to the appropriate types?

    Ted Wagner SharePoint Architect


    • Edited by twagner320 Wednesday, July 31, 2013 2:19 PM
    Wednesday, July 31, 2013 2:18 PM
  • You could use PowerShell to import the data.

    Using PowerShell, you can get the excel file using Import-CSV, enumerate each row in the CSV, adding each row to the SharePoint list.

    You would need to run this from one of the SharePoint servers.

    E.g. This example imports a CSV called VesselInPortReport.csv, which contains data about ships in a shipping port. I've also included a delete command at the beginning (but this will run quite slow if you have hundreds of items).

    The CSV file has the following columns:
    TRIP_NO
    VESSEL_NAME
    FLAG
    AGENT_NAME
    CURRENT_LOCATION
    RPT_DATE

    #Get the CSV file and connect to the SharePoint list
    $vessellist = import-csv -Path C:\Temp\VesselInPortReport.csv
    $l = (Get-Spweb "http://devmy101").GetList("http://devmy101/Lists/smarInPort")
    
    #Loop through the items and add them to the list
    $r = 1;
    foreach($item in $vessellist)
    {
    	$ni = $l.items.Add();
    	#Add the Title, using the rows VESSEL_NAME column
    	$ni["Title"] = $item.VESSEL_NAME;
    	
    	#Add the "Date Recorded" field, using the csv rows "RPT_DATE" column
    	[DateTime]$rd = New-Object System.DateTime;
    	if([DateTime]::TryParse($item.RPT_DATE, [ref]$rd)){
    		$ni["Date Recorded"] = $rd;
    	}
    	
    	#Add the csv rows "TRIP_NO" column to the new list items "Trip Id" field (SPFieldNumber)
    	[Int64]$tn = New-Object System.Int64;
    	if([Int64]::TryParse($item.TRIP_NO, [ref] $tn)){
    		$ni["Trip Id"] = $tn;
    	}
    	
    	#Add some other text properties
    	$ni["Flag"] = $item.FLAG;
    	$ni["Agent Name"] = $item.AGENT_NAME;	
    	$ni["Current Location"] = $item.CURRENT_LOCATION;		
    
    	#Update the item
    	$ni.Update()
    	Write-Host ([String]::Format("Added record:{0}",$r));
    	$r++;
    }
    What format is the data in for the Person column (in the spreadsheet)?


    Regards, Matthew
    MCPD | MCITP
    My Blog
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    I just added a webpart to the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos


    • Edited by Matthew Yarlett Wednesday, July 31, 2013 3:26 PM Removed the delete items bit from the code which was irrelevant
    Wednesday, July 31, 2013 2:47 PM
  • Hi Ted,

          Iam aware of importing the Spreadsheet to List and then we can able to column types in list, but we can't be able to change the column to Person or group type column & first column can't be able to change other than single line of text.

       So i am trying to copy the entire excel data and pasting in datasheet view list.

    Getting error as:

    Cannot paste the copied data due to data type mismatches or invalid data.

    some source rows which would have created new rows contained invalid values in required fields.These rows were skipped.

    If someone please assist an appropriate solution to do this.

    Thanks in Advance.


    Badri

    Moreover i am purely admin & not aware of coding stuff..
    • Edited by Badrinarayana Wednesday, July 31, 2013 3:19 PM updated
    Wednesday, July 31, 2013 3:17 PM
  • I've updated the example of using PowerShell to include a Person field (user field) and a choice field.

    The CSV file has the following columns:
    TRIP_NO
    VESSEL_NAME
    FLAG
    AGENT_NAME
    CURRENT_LOCATION
    RPT_DATE
    EMPLOYEE
    EMPLOYEE_TYPE

    #Get the CSV file and connect to the SharePoint list
    $vessellist = import-csv -Path C:\Temp\VesselInPortReport.csv
    $l = (Get-Spweb "http://devmy101").GetList("http://devmy101/Lists/smarInPort")
    
    #Get the lists EmployeeType field (choice)
    $employeeType = $l.Fields["EmployeeType"] -as [Microsoft.SharePoint.SPFieldChoice]
    
    #Loop through the items and add them to the list
    $r = 1;
    foreach($item in $vessellist)
    {
    	$ni = $l.items.Add();
    	#Add the Title, using the rows VESSEL_NAME column
    	$ni["Title"] = $item.VESSEL_NAME;
    	
    	#Add the "Date Recorded" field, using the csv rows "RPT_DATE" column
    	[DateTime]$rd = New-Object System.DateTime;
    	if([DateTime]::TryParse($item.RPT_DATE, [ref]$rd)){
    		$ni["Date Recorded"] = $rd;
    	}
    	
    	#Add the csv rows "TRIP_NO" column to the new list items "Trip Id" field (SPFieldNumber)
    	[Int64]$tn = New-Object System.Int64;
    	if([Int64]::TryParse($item.TRIP_NO, [ref] $tn)){
    		$ni["Trip Id"] = $tn;
    	}
    	
    	#Add some other text properties
    	$ni["Flag"] = $item.FLAG;
    	$ni["Agent Name"] = $item.AGENT_NAME;	
    	$ni["Current Location"] = $item.CURRENT_LOCATION;		
    	
    	#Add user information
    	$ni["employee"] = $w.EnsureUser($item.EMPLOYEE); #In this case, the $item.EMPLOYEE value from the spreadsheet is a persons name. Eg. "Matthew Yarlett"	
    	$employeeType.ParseAndSetValue($ni,$item.EMPLOYEE_TYPE); #In this case, the $item.EMPLOYEE_TYPE value from the spreadsheet is valid choice present in the EmployeeType list field. Eg. "Manager"
    	
    	#Update the item
    	$ni.Update()
    	Write-Host ([String]::Format("Added record:{0}",$r));
    	$r++;
    }


    Regards, Matthew
    MCPD | MCITP
    My Blog
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    I just added a webpart to the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos


    • Edited by Matthew Yarlett Wednesday, July 31, 2013 3:26 PM Removed the delete items bit from the code which was irrelevant
    Wednesday, July 31, 2013 3:25 PM
  • Have you tried linking the list in Access as a linked table?  I've found that Access is a bit more robust with it's cut and paste (and gives you paste errors that are easier to understand and troubleshoot).  Then you should be able to paste your excel data into the linked table in Access and hopfully that works.

    If you're finding that there are some specific fields/data types that are causing problems, again the nice thing about Access is you can have a sharepoint linked list and you can create another table with your excel data, and you can write a SQL query to cast/manipulate your data values to match what your sharepoint list is expecting.

    I use this method a lot when I'm setting up large amounts of data in testing environments to load up various SP lists, and even snuck a couple Access DBs into production with an Autostart macro and a timer job to run nightly imports/processing for file based data that needs to mesh with SP lists - while there are more elegant solutions, sometimes you just need to make it work and move on to the next issue.


    ieDaddy
    Blog: http://iedaddy.com
    Twit: @iedaddy

    Wednesday, July 31, 2013 3:29 PM
  • Badri,

    Would you be prepared to anonymise the spread sheet, strip out the data from the list and save it as a template and send me these so I can look at what's happening?  I'd like to throw them in a dev box to see what's happening?

    If so, please can you send to baron_72@hotmail.com and I'll have a look tonight / tomorrow


    Cheers,

    Steven Andrews

    SharePoint Business Analyst

    Blog: Steve's SharePoint Space  Twitter:   LinkedIn:   Facebook:

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    • Marked as answer by Badrinarayana Thursday, August 01, 2013 2:25 PM
    Wednesday, July 31, 2013 9:03 PM
  • Hey Experts,

                  Finally got data pasted into SharePoint list with some tips & tricks given by you.

    Thanks All...


    Badri

    Thursday, August 01, 2013 2:27 PM
  • Hi,

    I can't able to read "Person" column from Excel to SharePoint List, remaining column are working fine...please suggest me your idea to implement.

    Wednesday, September 04, 2013 7:44 AM
  • I have found this Microsoft free training helpful for importing to SharePoint:

    http://office.microsoft.com/en-us/sharepoint-server-help/introduction-RZ101874356.aspx?CTT=1&section=2

    Tuesday, September 10, 2013 11:07 PM
  • Hi Steven,

    I have been searching months for this solution so thank you a lot for this note. 

    Cheers,
    Guido F

    Tuesday, July 15, 2014 9:06 AM