Mohammed Salim

Mohammed Salim

  • 1.8k
  • 56
  • 1.3k

Uploading Excel data to SQL Server

May 30 2018 10:45 AM
Hi
 
Thanks for the help,
 
I'm trying to upload Excel data to SQL Server.
 
There is no issue with uploading, I just dont want to save the excel file. Once the file is selected using File upload it should be read with out saving in the directory. Find the code below. Please do assist,
 
if ((FileUpload1.HasFile))
{
if (!Convert.IsDBNull(FileUpload1.PostedFile) &
FileUpload1.PostedFile.ContentLength > 0)
{
string folderPath = Server.MapPath("~/Upload/");
//Check whether Directory (Folder) exists.
if (!Directory.Exists(folderPath))
{
//If Directory (Folder) does not exists. Create it.
Directory.CreateDirectory(folderPath);
}
//FIRST, SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.
FileUpload1.SaveAs(folderPath + Path.GetFileName(FileUpload1.FileName));
SqlBulkCopy oSqlBulk = null;
// SET A CONNECTION WITH THE EXCEL FILE.
OleDbConnection myExcelConn = new OleDbConnection
("Provider=Microsoft.ACE.OLEDB.12.0; " +
"Data Source=" + Server.MapPath("~/Upload") + "\\" + FileUpload1.FileName +
";Extended Properties=Excel 12.0;");
try
{
myExcelConn.Open();
// GET DATA FROM EXCEL SHEET.
OleDbCommand objOleDB =
new OleDbCommand("SELECT *FROM [Sheet1$] where Avaya_ID IS NOT NULL", myExcelConn);
// READ THE DATA EXTRACTED FROM THE EXCEL FILE.
OleDbDataReader objBulkReader = null;
objBulkReader = objOleDB.ExecuteReader();

Answers (6)