Introduction

Usually it is expected from client side to upload only structured Excel sheets (sheet with defined Column Names and data types for Rows) to load to server. But sometimes you might also require to dump whatever is in the Excel directly to your database. While there is a tool that is provided by SQL Management Studio for importing data from Excel files, this tutorial covers how you can automate such task using C# and .NET framework.

Steps

Two important pieces for uploading excel file of unknown column format to SQL database are:

1. Reading Data from Excel into a Data Table
2. Creating Table in SQL Server based on the Data Table

Once these two steps are done, the final step is simply to bulk load the data table into the table in SQL server.

Reading Data from Excel into a Data Table

I am using Excel Data Reader nuget package for this purpose.

/// <summary>
/// Gets a data table based on provided Excel File
/// </summary>
/// <param name="pathToExcelFile"></param>
/// <returns></returns>
public static DataTable GetTableFromExcel(string pathToExcelFile)
{
FileStream stream = File.Open(pathToExcelFile, FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader;
if (Path.GetExtension(pathToExcelFile) == ".xls")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}

excelReader.IsFirstRowAsColumnNames = true;

//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
DataTable dt = null;
if (result != null && result.Tables.Count > 0)
{
dt = result.Tables[0];//get first sheet only
}
return dt;
}

Excel Data Reader package supports multiple sheets of Excel to be read into a Data Set at once and also allows other configurations such as: Supporting multiple file formats and Checking Column Names in First Row.

Creating Table in SQL Server based on the Data Table

Now, to generate a “Create Table” query, I found a very useful code on the Stack Overflow site for this purpose.

/// <summary>
/// Generates sql create table query for given data table
/// </summary>
/// <param name="tableName">name of the table to be created</param>
/// <param name="table">data table</param>
/// <returns></returns>
public static string GenerateCreateTableQuery(string tableName, DataTable table)
{
var sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
string columnType = table.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint";
break;
case "System.Byte":
sqlsc += " tinyint";
break;
case "System.Decimal":
sqlsc += " decimal ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.String":
default:
sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
break;
}
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
}

Once we have the data and create table script, uploading to database is quite simple and can be achieved with following steps:

1. Decide upon a table name
2. Check if it exists or not in the server
3. Create table if it doesn’t exist
4. Bulk load data table into your newly created table.