I have been asked to provide an example of uploading data from an Excel 2007 document to a SQL Server Database . While there are a number of different approaches you may take in this example I will use the SqlBulkCopy Class .
Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.
The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
To start off we need to define the connection string both for the database and Excel.
<connectionStrings> <add name="Batman" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:tempAuthors.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES""/> <add name="Thor" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf"/> </connectionStrings>
In the above example Batman represent the datasource to the Excel 2007 document and Thor is the mighty database. If you ever need details on connection strings the be sure to visit ConnectionStrings.com , they have just about every possible connection string you may ever need. Note: You may notice the use of HDR in the connection for Excel; this is because my document contains a header. HDR=Yes;” indicates that the first row contains columnnames, not data. “HDR=No;” indicates the opposite.
If you are considering taking an exam in the near future of just curious where you current knowledge is with SQL Server 2005 then give Microsoft SQL Server 2005 – Implementation and Maintenance Special Edition Practice Exam a look. Candidates for this exam are professionals who typically pursue careers as database administrators, database developers or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions, but who want to show their breadth of technology experience, such as developers, systems administrators, and others.
The next step is to wire up a method that will take the data from Excel and process it into the backend database. For the purpose of this example I am not demonstrating best practices rather a to the point mechanism of accomplishing this task.
/// <summary>
/// Copies the author data from an Excel 2007 document to a SQL Server database instance.
/// </summary>
public void CopyAuthorData()
{
using (OleDbConnection source = new OleDbConnection(_sourceConnectionString))
{
string sql = string.Format("SELECT * FROM [{0}]", "Authors$");
OleDbCommand command = new OleDbCommand(sql, source);
source.Open();
IDataReader dr = command.ExecuteReader();
using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString))
{
copy.DestinationTableName = "Authors";
copy.WriteToServer(dr);
}
}
}
That is all there is to it when it comes to processing data from Excel to SQL Server using SqlBulkCopy. Should you find yourself seeking further detail on querying an Excel document be sure to visit this resource .
Related posts:
