csharp Import data from Excel

static bool ProcessExcelFile()
{
	try
	{
		int ID;
		string Sql = "Select * From [SheetName$]";
		OdbcConnection ExcelConnection = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\folder\Data.xls;DefaultDir=C:\folder;ReadOnly=1;");
		OdbcCommand ExcelCommand = new OdbcCommand(Sql, ExcelConnection);
		OdbcDataReader dr;

		ExcelConnection.Open();
		ExcelCommand.CommandType = CommandType.Text;
		dr = ExcelCommand.ExecuteReader();

		while (dr.Read())
		{
			Console.WriteLine(dr["Code"].ToString());
			ID = ImportItem(dr);
			Console.WriteLine("ID: " + ID.ToString() + " successfully imported");
		}
	}
	catch (Exception e)
	{
		//log error: e.Message
		return false;
	}

	return true;
}

static int ImportItem(OdbcDataReader dr)
{
	int ID = 0;

	using (SqlConnection Connection = new SqlConnection("ConnectionString goes here"))
	{
		Connection.Open();

		SqlCommand Command = new SqlCommand();
		SqlParameter Parameter;

		Command.CommandText = "StoredProcedureName";
		Command.CommandType = CommandType.StoredProcedure;
		Command.Connection = Connection;
		
		//add input parameters
		Command.Parameters.AddWithValue("@Name", dr["Name"]);

		//add the ProductID, which is returned
		Parameter = Command.CreateParameter();
		Parameter.ParameterName = "@ID";
		Parameter.Direction = ParameterDirection.Output;
		Parameter.DbType = DbType.Int32;
		Command.Parameters.Add(Parameter);

		Command.ExecuteNonQuery();

		ID = (int)Command.Parameters[2].Value;

		Command.Dispose();
		Connection.Close();
		Connection.Dispose();
	}

	return ID;
}
Reads an Excel file and imports it into a SQL database.

Updated: Sunday 10th October 2010, 02:00am

Share Snippet

There are 0 comments

Leave a comment of your own

Comments are currently closed.