Import Excel file without saving in folder in ASP.Net

Last Reply 9 months ago By dharmendr

Posted 9 months 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();
}

 

Results 1 - 5 of 6 12
Posted 9 months ago

1. The FileName property is the Client end path while you need to use the Server side path for reading Excel file.

2. OLEDB can only read for file path. If you want to read directly from FileUpload, you need to use Closed XML.

Refer my article

Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net

Just remove the following lines

 string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);

and replace following line

using (XLWorkbook workBook = new XLWorkbook(filePath))

with

 using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))

 


Posted 9 months ago

Hi Mudassar,

 

When i try run in localhost(IIS), I get the following error.

attached code as well.

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

 

 

tring strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();
                    string path = FileUpload1.PostedFile.FileName;

                    SqlBulkCopy oSqlBulk = null;

                    // SET A CONNECTION WITH THE EXCEL FILE.
                    OleDbConnection myExcelConn = new OleDbConnection
                        ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"");
                        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();

 


Posted 9 months ago

Will you please let us know which line you are getting the error?


Posted 9 months ago

Hi,

Share the Line number and let us know whoch line error is coming.


Posted 9 months ago

Hi Mudassar,

 

This is the line where im getting an error

 new OleDbCommand("SELECT *FROM [Sheet1$] where Avaya_ID IS NOT NULL", myExcelConn);