Export/Import CSV file from/to listitem Sharepoint

We are able to export items from Sharepoint list into CSV files (comma-separated-values) and do opposite too, import csv into Sharepoint list.

Export : using codes below, you will get csv as download-file. Prepare a list inside your site, named it “Item List”, with 3 columns : Title, Price, Description.

private void ExportToCsv()
{
	Response.Clear();
	Response.Buffer = true;
	Response.AddHeader("content-disposition", "attachment;filename=Itemrecord.csv");
	Response.Charset = "";
	Response.ContentType = "application/text";
		
	// get data
	DataTable dtItem = GetDataFromList();
	StringBuilder sb = new StringBuilder();
	if(dtItem != null)
	{
		// separate each column with comma
		foreach (DataColumn dc in dtItem.Columns)
		{
			sb.Append(dc.ColumnName + ',');
		}
		
		// newline for value
		sb.AppendLine();
		
		// get value
		foreach (DataRow dr in dtItem.Rows)
		{
			foreach (DataColumn dc in dtItem.Columns)
			{
				sb.Append(dr[dc.ColumnName].ToString() + ',');
			}
			sb.AppendLine();
		}
		
		Reponse.Output.Write(sb.ToString());
		Response.Flush();
		Response.End();
	}
}

private DataTable GetDataFromList()
{
	// I have a list 'Item List' with 3columns
	SPQuery query = new SPQuery();
	query.ViewFields = "<FieldRef Name='Title' />
						<FieldRef Name='Price' />
						<FieldRef Name='Description' />";
	SPList list = SPContext.Current.Site.RootWeb.Lists.TryGetList("Item List");
	SPListItemCollection itemColl = list.GetItems(query);
	
	return itemColl.GetDataTable();
}

As result, you will get file “Itemrecord.csv”, contains firstline as column name, nextline as values.

Import : prepare file “Itemrecord.csv” and save it in C:\. Remember that file content should match with export file, position of value will determine it is Title / Price / Description.

private void ImportCsvToList()
{	
	SPList itemList = SPContext.Current.Web.Lists.TryGetList("Item List");
	SPItem oItem;
	string line;
	string[] array;
	
	// get file
	StreamReader reader = new StreamReader("C:\Itemrecord.csv");
	
	// read file
	while ((line = reader.ReadLine()) != null)
	{
		oItem = itemList.Items.Add();
		
		// split each item value
		array = line.Split(',');
		
		// get value
		item["Title"] = array[0];
		item["Price"] = array[1];
		item["Description"] = array[2];
		
		// save new item to Item List
		item.Update();
	}
}

Advertisements

2 thoughts on “Export/Import CSV file from/to listitem Sharepoint

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