Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have an excel and I need to import those datas to sharepoint list. So I click on datasheet view and try to copy the contents and the problem is I have Person/Group column type which is not allowing me to copy the contents in the datasheet view.

Is there any way I can copy those contents to sharepoint list. Really appreciate any help on this.

share|improve this question
    
Could you show me the format of the field you're trying to copy into the datasheet view? –  Christoffel de Gruyter Aug 19 at 15:18

3 Answers 3

I have copied data from excel to sharepoint multiple times using the datasheet view. Do you really need that column for your list? You could easily change it to something like an "Assigned to:" column.

If you really need to keep it, then I suggest you modify the view so the person/group column comes first and make sure it is not a required field. Then go in excel and copy-paste that column so it also shows up as the first one. All you have to do now is select all the data except that one column and paste it in sharepoint, starting with the second column of the list

Then you would only have to enter the data for the said person/group column manually. Let me know if this helps.

share|improve this answer
    
Yes that should be fine, but I need to update the SharePoint list on weekly basis and I can't update the person/group column manually since I have around 2000 rows. Just curious to know is there any way to export the data's to existing SharePoint list using access DB. –  Learner Aug 20 at 7:06

From my experience, this is not possible through the SharePoint Dataview Sheet View. This is a new feature in 2010 and although super delicious and powerful, it still has a lot of issues that need to be resolved before it will be as powerful as it can be. So you will have to manually update, use a powershell script or use a data migration tool such as ShareGate.

share|improve this answer
    
So you mean to write a powershell script to import excel data to SharePoint list? –  Learner Aug 19 at 15:16
    
Yes just the person column values will be enough so the script will run fast. –  Malin De Silva Aug 19 at 15:19

To follow along what Malin already pointed out, Sharegate can help you achieve the desired result.

Please note that I am part of the Sharegate User Experience team so I am more aware of what is possible with Sharegate and less with other tools available. However, other tools might also be a good fit in your case. If you would like to see how Sharegate can help you, you can simply download the free trial and follow the procedure below:

1 - Before the import, go to the destination list settings (in the advanced settings) and disable the attachments.

2 - Open the tool "Copy SharePoint Content".

3 - Choose any site/list as source. It will not be used (nor will it be modified), this is simply necessary in order to get to the next step

4 - Select the desired destination site and list (where you want to import list items).

5 - Click on Excel -> Import and import your Excel file. The Excel file will need to contain an "ID" column with fake numbers in it. The other columns can be used to fill the item's metadata in SharePoint. You will also need a "ContentType" column with the content type that you want to set on the imported items. (item, issue, comment etc.)

If your data is in CSV files, you can use Excel to transform them into ".xlsx" files (which is the only supported format in Sharegate).

6 - You will be prompted with a screen that lets you choose between "Import Now" and creating a custom template.

7 - Click on Create a Custom Template and make sure you do not select the option to copy the permissions. Select the other options that you need.

If the columns in your Excel file have the same name as the columns in your destination list, you can then click on "Save and Start". If you need to map some columns or define special actions for some columns, you can go in the Metadata section.

If it is the case, I suggest you read the documentation here: http://en.share-gate.com/support/documentation/property-template.html

and here: http://en.share-gate.com/support/documentation/map-properties.html to help you get the mappings and property template done correctly.

The reason for disabling the attachments on the destination list and not selecting the option to copy permissions is to prevent Sharegate from trying to fetch this information from the source list (which in truth does not contain any information for this use-case). In any case, since you are importing from an Excel file, you probably don't have attachments and permissions to set on the imported items.

Jimmy De Santis | User Experience Guardian at Sharegate

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.