In this post let’s see how we can write data in a excel file into a database using a DataSet and SqlBulkCopy.
The concept here is fairly simple. First I am going to load data in the excel file into a DataSet and then for each tables in the DataSet, I am going to use SqlBulkCopy to push the data into the database. In my case, it will be a single table.
For demonstration purpose I have the following excel file, which has only one sheet which is named as “MySheet”.
Now let’s see the whole process in action. I’m creating a new console application. I have the following method to get excel data into a table in DataSet.
Here when creating the OleDbConnection, in the query string along with the excel file path, I have mentioned some properties.
- "HDR=Yes;" indicates that the first row contains columnnames. If "HDR=No;", it indicates the opposite.
- MEX=1 is a safer way to retrieve data for mixed data columns. (You can read more on excel connection strings on http://www.connectionstrings.com/excel/).
Once I do that, now I have to push the data in my DataSet into the database. Before doing that here I have following two helper methods.
Following method will check if a table already exists with the given name in the database.
If the table doesn't exist, following method will create a table with all the column names as in provided DataTable. Please note, here for all columns I am using “varchar(MAX)” as the column type.
Once I have the DataSet and the table created in database, all I have to do now is push the data in DataSet into the database.Finally from the Main method, I am initiating the process.
Once the program completed, I can see the data in my database table.
|Table in the Database|
So that's it. I have uploaded the sample into my SkyDrive.