Import data to Sharepoint list from excel

Its been rough day with excel and importing data into sharepoint list, But as a good developer Aka. Warroir never stop fighting, So finally i managed to import data from excel sheet, I thankful for all the blogs and post that saved my day, So here is a bits and pieces of snippets i collected and build this method as below,

Things to do,
1. Install AccessDatabaseEnging.exe 32-Bit works like charm but not 64 bit [Trust me on this]
2. Excel with sheet name in here i used same as list name to relate between excel data and list. [listName == Sheet Name]
3. Create taxonomy termset fields before run the method, else it will thow term not found exception.
4. Make sure there is no special characters in the document set title else it will thorws exception and docset will not get created

Module Flow:
1.Read excel from Debug folder in project

string dirLocation = Directory.GetCurrentDirectory();
//Location will be like ProjectName\bin\Debug\data.xlsx
String filePath = dirLocation + "\\data.xlsx";

  1. Use OLEDB to push data to the table to make life easier.
  2. Read sheet that related to list
cmd.CommandText = "SELECT * From ["+listName+"$]";

  1. Traversal throw the table and get column name and values
foreach (DataColumn column in data.Columns)
{
string columnValue = i[column.ColumnName].ToString();
Console.WriteLine("Column Name --> " + column.ColumnName + "Value ->" + columnValue);
oListItem[column.ColumnName] = columnValue;
}

  1. Creating list items is just two lines as above but document library need special treatment as below,

listCreationInformation.UnderlyingObjectType = FileSystemObjectType.Folder;
foreach (DataColumn column in data.Columns)
{
if (column.ColumnName == "Title")
{
string columnValue = i[column.ColumnName].ToString();
listCreationInformation.LeafName = columnValue;
}
}
ListItem newListItem = oList.AddItem(listCreationInformation);
newListItem["ContentTypeId"] = "0x0120D520";
foreach (DataColumn column in data.Columns)
{
string columnValue = i[column.ColumnName].ToString();
newListItem[column.ColumnName] = columnValue;
}
newListItem.Update();

Give a try the code below,

public void readExcel_importListData(ClientContext context, String listName) {
	string dirLocation = Directory.GetCurrentDirectory();
	//Location will be like ProjectName\bin\Debug\data.xlsx
	String filePath = dirLocation + "\\data.xlsx";
	string connString = string.Empty;
	Console.WriteLine("Location: : " + filePath);
	List oList = context.Web.Lists.GetByTitle(listName);
	ListItemCreationInformation listCreationInformation = new ListItemCreationInformation();
	if (filePath.EndsWith(".xlsx")) {
		//supports from 2007 Format
		connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", filePath);
	}
	using(OleDbConnection con = new OleDbConnection(connString)) {
		using(OleDbCommand cmd = new OleDbCommand()) {
			//Read Sheet
			cmd.Connection = con;
			con.Open();
			DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			con.Close();

			//Read the Header Row and value
			cmd.CommandText = "SELECT * From [" + listName + "$]";
			using(OleDbDataAdapter da = new OleDbDataAdapter(cmd)) {
				System.Data.DataTable data = new DataTable();
				da.SelectCommand = cmd;
				da.Fill(data);

				foreach(DataRow i in data.Rows) {
					ListItem oListItem = oList.AddItem(listCreationInformation);
					if (listName != "Document Library") {
						foreach(DataColumn column in data.Columns) {
							string columnValue = i[column.ColumnName].ToString();
							Console.WriteLine("Column Name --> " + column.ColumnName + "Value ->" + columnValue);
							oListItem[column.ColumnName] = columnValue;
						}
						oListItem.Update();
						context.ExecuteQuery();
					} else {
						listCreationInformation.UnderlyingObjectType = FileSystemObjectType.Folder;
						foreach(DataColumn column in data.Columns) {
							if (column.ColumnName == "Title") {
								string columnValue = i[column.ColumnName].ToString();
								listCreationInformation.LeafName = columnValue;
							}
						}
						ListItem newListItem = oList.AddItem(listCreationInformation);
						newListItem["ContentTypeId"] = "0x0120D520";
						foreach(DataColumn column in data.Columns) {
							string columnValue = i[column.ColumnName].ToString();
							newListItem[column.ColumnName] = columnValue;
						}
						newListItem.Update();
						context.Load(oList);
						context.ExecuteQuery();
					}
				}

				Console.WriteLine("List data imported Successfully");
			}
		}
	}
}


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s