Uploading Excel data to SQL Server

Mohammed Salim

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
hi,
may be in your excell cell having empty field, check that and try again
Mohammed Salim8y ago
Hi @Rafnas T P,

I tried to few modification to code. When I try to upload the file . It throw up the following error. Im running web setup in IIS

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] Avaya_ID.AvayaID.BtnUpload_Click(Object sender, EventArgs e) +1420 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +11764988 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +149 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1664
Leon D8y ago
Hi,

Check the following article:

https://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database
Rafnas T P8y ago
Ya, but you can use this as reference and you can make some changes accordingly
Mohammed Salim8y ago
Hi Rafnas,

the below link for Windows App. Can we use file upload & try.
Rafnas T P8y ago
Hi,I have an article for this folow below link. https://www.c-sharpcorner.com/article/import-excel-data-into-sql-table-using-sqlbulkcopy-in-c-sharp/