Load Excel data in a SQL Server table

  • Thread starter Thread starter Luigi
  • Start date Start date
L

Luigi

Hi all,
what's the simple way to read an Excel file (always in the same format and
name) in my WinForm and pass this data to a SQL Server stored procedure?

Thanks in advance.

Luigi
 
Thanks for response Mark.
Having this code:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=C:\\TemplateExcel.xls;Extended Properties=Excel 8.0;";


objConn = new OleDbConnection(connectionString);

oleDA = new OleDbDataAdapter("select * from [Sheet1$]",
objConn);
ds = new DataSet();
//Fill the Data Set
oleDA.Fill(ds);

// Create Connection to Excel Workbook
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
OleDbCommand command = new OleDbCommand("Select * FROM
[Archivio$]", connection);
connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString =
ConfigurationManager.AppSettings["DJConnectionString"];

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new
SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}

how can I modify it to use an OpenFileDialog instead?

Luigi
 
how can I modify it to use an OpenFileDialog instead?
Click to expand...

Use the dialog to get the Excel file name and alter the connection
string to represent that file instead. What you have:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=C:\\TemplateExcel.xls;Extended Properties=Excel 8.0;";

Instead, use something like:

//You should know how to get file name, so this is to save typing
string filePathFromOpenFileDialog = GetFileName();

string template =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};";

string connectionString = template.Replace("{filePath}",
filePathFromOpenFileDialog);

As long as you check to make sure the file is excel before passing and
add exception handling in case the user picks an Excel file that is not
the right format, you should be fine.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top