1

We had a requirement to migrate 6 Millions record from .csv file to 13 sharepoint list including lookup column, we come up with tool using Client Object model but the performance is low ...did any one come across like this, what will be the best approach to be followed here, please share your ideas...Thanks in Advance.

  • Imo, bulk processing through the client object model is the wrong tool for the job. Simply switching to something on the server (like powershell mentioned below) is going to improve performance drastically. – Christoffel de Gruyter Aug 19 '14 at 19:35
  • Sorry, but to ask the obvious question, why are you adding 6 million rows to SP? What benefits do you hope to get vs a database? – Mike2500 Aug 19 '14 at 19:39
3

Try PowerShell if you have access to the server or admin access to the list you need to import into.

I will break down the parts of the script you might need as follows: 1. Set up the correct modules for SharePoint manipulation in your PowerShell script. 2. Set up the CSV import file in a variable in your script so that you can access the content of the CSV - i.e. your columns and data. 3. Set up the parameters to access the SharePoint site where the items will be imported into.

Depends on the type of records in your CSV file but I have previously uploaded batches of about 100,000 files using PowerShell and CSV. In my case the CSV file contained the metadata that I wanted to store against the file once it had been uploaded to a SharePoint document library. The CSV file also contained the path (to a network share) of where the file was stored before upload to SharePoint.

Pre-planning for the script (in my case) was that I had to copy the file to a staging area and rename it before upload - you may not need to do this.

# Setup the correct modules for SharePoint Manipulation 
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) 
{ 
Add-PsSnapin Microsoft.SharePoint.PowerShell 
} 
$host.Runspace.ThreadOptions = "ReuseThread"

# Set up the CSV import file in a variable.
$ImportFilePath="C:\CSV_Import_File"
$ImportFileName="YourCSVFileName.csv"
$ImportFile = (Join-Path $ImportFilePath $ImportFileName)
# Get Data from CSV File 
$FileExists = (Test-Path $ImportFile -PathType Leaf) 
if ($FileExists) { 
"Loading $ImportFile for processing..." 
$tblData = Import-CSV $ImportFile 
} else { 
"$ImportFile not found - stopping import!" 
exit 
}

$sourcePath = ""
$destPath = "C:\Uploaded"

# Setup the parameters to access the SharePoint site where the documents will be uploaded to.
$siteurl = "http://your.sharepoint.web"
$spWeb = Get-SPWeb $siteurl
$docLibraryName = "YourListOrDocLibName"
$stagingFolderPath = $destPath
$docLibrary = $spWeb.Lists[$docLibraryName]

Then do the upload (here you will modify the columns referenced for your own CSV file. You will need to match the columns in your CSV to the columns in your SharePoint List.

# Copy and Rename each file in the CSV file locally from source folder to destination folder.
$tblData | % {
$sourcePath = $_.FOLDER_FULL_PATH 
if(Test-Path -Path (Join-Path $sourcePath $_.CSVFILENAME)) { 
    Copy-Item -Path (Join-Path $sourcePath $_.CSVFILENAME) -Destination (Join-Path           $destPath "$($_.FILENAME)")
write-host "Copying" $_.FILENAME "to" $destPath "..." -foregroundcolor Green

Do some checks before upload and assigning the metadata:

# Check if the file has been copied to the staging folder before trying to upload it.
if(Test-Path -Path (Join-Path $stagingFolderPath $_.FILENAME)) { 
    # Upload the file and tag it
    $currentFile = (Join-Path $stagingFolderPath $_.FILENAME)

    $fileStream = ([System.IO.FileInfo] (Get-Item $currentFile)).OpenRead()
            $contents = new-object byte[] $fileStream.Length
            $fileStream.Read($contents, 0, [int]$fileStream.Length);
            $fileStream.Close();
            write-host "Uploading" $currentFile "to" $docLibrary.Title "in" $spWeb.Title "..." -foregroundcolor Green
            $folder = $docLibrary.RootFolder
            $spFile = $folder.Files.Add($folder.Url + "/" + $_.FILENAME, $contents, $true)
            $spItem = $spFile.Item
            # Change the following columns to match your SharePoint columns.
            $spItem["Title"] = $_.DOCUMENT_TITLE;
            $spItem["AddedBy"] = $_.ADDEDBY;                
            $spItem["ItemID"] = $_.ITEMID;
            $spItem["Category"] = $_.CATEGORY;
            $spItem["DateAdded"] = $_.DATEADDED;
            $spItem["CSVFilename"] = $_.CSVFILENAME;
            $spItem["OrigFilename"] = $_.ORIGFILENAME;
            $spItem["ContentType"] = $_.CONTENTTYPE;
            $spItem.Update();
            # OUTPUT RECORD OF UPLOADS
            $timestamp = Get-Date
            $uploadedfile = "$currentFile,$timestamp"
            Add-Content C:\Powershells\Outputs\Upload_Output.csv $uploadedfile
}

Even though my scenario involved files uploading your scenario is similar and should run faster as there is no reason to wait for upload of large files.

I would recommend you output a log file from your script so that you know if some items failed while script runs.

0

First of all for everyone's sake, 60 lakhs corresponds to 6 million records :)

Going to the problem at hand, I am hoping that you have already analyzed performance and noticed where your bottlenecks are occurring. Suggestion for improving insert time would be to use ProcessBatchData to speed up performance.

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spweb.processbatchdata.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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