Migrating CSV data into SharePoint lists

A common migration scenario of data into SharePoint involves CSV files to be imported via Datasheet view into SharePoint lists.

However there are some limitations such as multi-lookup values, where data should be provided in the following format: "5;#technology;#3science;#". The same format has to be provided for columns of type 'People or Groups', such as "67;#John Doe;#123;#Anne Jackson;#". As a result, you cannot use DataSheet view and you have to upload the CSV programmatically.

This post focuses on reading the CSV file programmatically. You have two options:

• if you use Powershell, you can use the PS command import-csv and then access the data like this:
  
  import-csv c:\folder\file.csv
  foreach($line in $excelFile){$title = $line.name 
  
• if you use C# you can read the CSV file via a OLEDB driver you need to install locally, to permit your 64-bit code (you have to run on 64 bit to be able to execute SharePoint API calls for the actual lookup of values and list item creation).
  
  You can find the driver here Microsoft Access Database Engine 2010 Redistributable.
  This driver is simply a replacement for JET OLEDB to be run on server applications. JET is only running on 32 bits.
  
  

  string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\folder_name\;Extended Properties='text;HDR=Yes;FMT=Delimited'";

  
  
  string excelSheetName = "file.csv";
  
  
  OleDbConnection myConnection = new OleDbConnection(connectionString);
  myConnection.Open();
  OleDbCommand myCommand = new OleDbCommand("Select * from " + excelSheetName + ";", myConnection);
  OleDbDataAdapter adapter = new OleDbDataAdapter(myCommand);
  adapter.Fill(ds);
  
  
  myConnection.Close();