Import Excel file without saving in folder in ASP.Net

Last Reply one year ago By dharmendr

Posted one year ago

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 (!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();
}

 

You are viewing reply posted by: dharmendr one year ago.
Posted one year ago

Hi Mohdsalim,

As per your question you don't want to save the file in directory. So you need to use ClosedXml.

Refer below article.

Read Excel file using FileUpload without saving in Folder in ASP.Net

Once you get the DataTable by reading the excel you can use SQLBulkCopy to import data to Database.

Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net using C# and VB.Net