Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

Previously, I have posted some SSIS solutions which would handle dynamic column mapping in data loading. This time, I was wondering how to handle dynamic column mapping when the source is an excel file. I was quick to realize that it would be entirely a C# solution and the program should be able to handle any change in the number or name of columns. In simple words, you give any excel file and the program will load it into database. I used GetOleDbSchemaTable in order to fetch the schema information from the excel file, then built a similar table in the database. Excel data types and SQL data types do not match. Sometimes, they make me panic while loading files using SSIS. In my program also, I had to do some change in the data types; like WChar becomes NVARCHAR, Double becomes INT. Yes, I could use an XML file/database table to save a detailed mapping of this conversion of excel data types to SQL data types to make my program more capable and versatile. But, the conversion is hard-coded at the moment. Another challenge was to get all the data loaded to SQL table without using any column names or hard coded column mapping. SQLBulkCopy came to my rescue. By the way, I had to use a dataview over the table returned by GetOleDbSchemaTable because it was returning the column names in alphabetical order. So, I had to sort the rows using “ORDINAL_POSITION”

using System;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Data;

using System.Windows;

namespace ConsoleApplication2

{

class Program

{

           public static void Main()

{

try

{

///*****************Establish Excel Connection*****************///

String strExcelConn = “Provider=Microsoft.ACE.OLEDB.12.0;”

+ “Data Source=C:\\files\\Book1.xlsx;”

+ “Extended Properties=’Excel 8.0;HDR=Yes'”;

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

///*****Get schema info and Read Sheet Name and save it as TableName *******//

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string TableName = dtExcelSchema.Rows[0][“TABLE_NAME”].ToString();

string Query = “CREATE TABLE “ + TableName + “(“;

/*****Get schema info and read the column names and build Query to Create similar SQL table*****/

//

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });

connExcel.Close();

string FieldNames = “”;

DataView dv = new DataView(dtExcelSchema);

dv.Sort = “ORDINAL_POSITION”;

foreach (DataRowView rowView in dv)

{

DataRow row = rowView.Row;

Query += row[“COLUMN_NAME”].ToString() + ” “ + ((OleDbType)row[“DATA_TYPE”]).ToString() + “,”;

FieldNames += row[“COLUMN_NAME”].ToString() + “,”;

}

/////******************Replace data types************/////

Query = Query.TrimEnd(“,”.ToCharArray()) + “)”;

Query = Query.Replace(“WChar”, “NVARCHAR(500)”);

Query = Query.Replace(“Double”, “INT”);

FieldNames = FieldNames.TrimEnd(“,”.ToCharArray()) + “”;

/******************read records from excel file************/

cmdExcel.CommandText = “Select “ + FieldNames + ” from [“ + TableName + “]”;

DataSet ds = new DataSet();

OleDbDataAdapter da = new OleDbDataAdapter();

connExcel.Open();

da.SelectCommand = cmdExcel;

da.Fill(ds);

connExcel.Close();

//******** Establish SQL Server connection and create table*****//

SqlConnection conn = new SqlConnection();

string sqlConn = “Data Source=YourServerName;Initial Catalog = YourDBName; Integrated Security = SSPI;Persist Security Info=False;”;

conn.ConnectionString = sqlConn;

SqlCommand cmd = new SqlCommand(Query);

cmd.Connection = conn;

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

//*****************Do bulk copy to table*****************//

DataTable dt = new DataTable();

dt = ds.Tables[0];

SqlBulkCopy sbc = new SqlBulkCopy(conn);

sbc.DestinationTableName = TableName;

conn.Open();

sbc.WriteToServer(dt);

conn.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

}

}

}

 

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in MS Visual Studio, C#.NET, SQL, Database, SSIS (SQL Server Integration Service) and tagged , , , , , . Bookmark the permalink.

13 Responses to Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

  1. Manmohan Jangid says:

    sir, I am getting an Exception
    object reference not set to an instance of an object

    • Zahid says:

      It seems the excel file is not in the right place. Plz check file path and file name. Or, the database connection string was incorrect.

  2. is it this solution in script component. says:

    Hi,

    I have been asked solution for this.

    I have Vendors where i get different excel file (diffrenct structure) from different vendor.

    Vendor1 excel file
    ——————
    Name, Address, Age

    Vendor2 Excel file
    ——————
    CandidateName, PhysicalAddress, Age

    what ever Header Names but meaning is same.

    Order of Headers are may vary different…

    but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age)…. Here i need to store correct data columns which are mapped from above two excels.

    Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.

    Thanks,
    Ravikiran

  3. Trish says:

    Thanks Zahid these are great posts. Can you use the above code in a script task compenent in SSIS?
    Kind regards
    Trish

  4. Ankit Mangal says:

    When I try to run above code in Script task. i am getting “Could not find installable ISAM”…any idea??

  5. shivangi says:

    Please include Namespace of the commands used in the code.

    • Zahid says:

      Thanks Shivangi for pointing this out. I have updated my post with namespaces. I also added the line to initialize oledb connection (for excelfile) at the beginning, it was missed somehow previously. Also, note that it is written as a C# Console Application.

  6. Discovery2012 says:

    Can you write a post about, how to read data from a SQL table then convert the data pro-grammatically then write to a SQL table.

  7. mahesh says:

    once table created is it possible to append records using your script? can you provide me script to append records to table dynamically by mapping excel columns?

  8. mahesh says:

    once table created, is it possible to append records using your script? can you provide me script to append records to table dynamically by mapping excel columns?

  9. Pingback: SSIS C#: Load an CSV file – handle any change in the number or name of the columns | Masud Ahmed

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