Mass import from Excel

HelpDesk SharePoint 2013/2016 version
1

Hello,

I’m trying to import 48000 rows of old tickets from excel into the helpdesk tickets table. What’s the best way of doing that?

2

Hi @jaitsujin,

You can switch the view into Quick Edit mode and copy-paste data from Excel. Please, note that only metadata e.g. Status, Subject, Priority, etc. will be imported. Description and Comments must be added manually via forms.

Another option is importing data programatically with CSOM.

Anna Dorokhova
Plumsail Team

3

Thanks for the response. I'll have to look into CSOM, because manual entry won't be an option. it's too much.

4

i found another solution that might be helpful to others. Importing using Powershell.

Add-PsSnapin Microsoft.SharePoint.PowerShell -erroraction silentlycontinue  
  
# CSV path/File name
$contents = Import-CSV "C:\Users\....\Desktop\Export.csv"

# Web URL
$web = Get-SPWeb -Identity "https://.../Service Ticket" 

# SPList name
$list = $web.Lists["Tickets"] 


# Iterate for each list column
foreach ($row in $contents) {
    # 
    "Adding entry for " + $row."Title".ToString()
    # Create Row
    $newItem = $list.AddItem()
    # Get Data
    if ($row.Title –ne "") {
        $newitem["Title"] = $row."Title".ToString() }
    if ($row.Status –ne "") {
        $newItem["Status"] = $row."Status".ToString() + ";#" }
    if ($row.Requester –ne "") {
        $newItem["Requester"] = $row."Requester".ToString() + ";#" }   
    if ($row.DateRequested –ne "") {
        $newItem["Date Requested"] = $row."DateRequested".ToString() }
    if ($row.RequestBody –ne "") {
        $newItem["Body"] = $row."RequestBody".ToString() }
    if ($row.CompletionDate –ne "") {
        $newItem["Completion Date"] = [datetime]($row.CompletionDate) }
    if ($row.LaborHours –ne "") {
        $newItem["Labor Hours"] = $row."LaborHours".ToString() }
    if ($row.TechComments –ne "") {
        $newItem["Tech Comments"] = $row."TechComments".ToString() }
    if ($row.creatorID –ne "") {
        $newItem["AssignedTo"] = $web.Site.RootWeb.EnsureUser($row.creatorID) }
    if ($row.devicetype –ne "") { 
       $newItem["Device_x0020_Type"] = $row."devicetype".ToString() }
    if ($row.Phone –ne "") {
       $newItem["Phone"] = $row."Phone".ToString() }
    if ($row.Category –ne "") {
        $newItem["Category"] = $row."Category".ToString() }
    # Update
    $newitem.update()
}
1 Like
5

HI @jaitsujin,

Thank you for sharing it with other community members!

Anna Dorokhova

1 Like
6

Can attachment be mass uploaded?

7

Yes, Charlene, you can upload several attachments in one go but please remember about its limits. SharePoint allows to attach not more than 250 MB to one item. Also, the attachments are to be sent through e-mail to the requester and that also has its own restrictions.