.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Trying to import from Excel file

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :ASP.Net

I have been trying to import from an Excel file and insert it into a table.

I can upload the Excel file

I can view the Excel file in a grid

But when I run the import process to insert the rows in the Excel file I get only one row and it has all nulls except the dateentered and entered by field.

I added breakpoints and tried setting a couple of the fields to labels and I could see that data was getting to the reader.

So now I hoping someone might see something I'm missing here.

Protected Function ExcelConnection() As OleDbCommand

        ' Connect to the Excel Spreadsheet (online) = .ACE.OLEDB.12.0 (local) = .Jet.OLEDB.4.0
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Server.MapPath("~/Import_Process/ExcelImport_vol.xls") & ";" & _
                    "Extended Properties=Excel 8.0;" 'HDR=NO;IMEX=1"

        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)

        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
        ' in this case

View Complete Post

More Related Resource Links

data type problem in import data from excel file


Dear All,

I am importing the data from excel file using following code.

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;"
                conn = New OleDbConnection(connstr)
                Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]"

                Dim cmd As New OleDbCommand(strSQL, conn)

                Dim da As New OleDbDataAdapter(cmd)

Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.

eg. The excel column value     Column1


Import Excel File in SQL Server


Dear all,

I have to create one page where user will upload the excel file in SQl Server and system should check following things

1.Sheet Name should be always sheet1

2.System should check the column names

3.System should show the message that these many rows has been loaded



Import Excel-File (OPENROWSET) on x64 SQL Server


Hi, recently I encountered the following problem:

I tried to execute a stored procedure on the newly installed SQL 2005 Server (now on x64 Win Server 2003) which imports an Excel-File into a DB table.

We use OPENROWSET to access the Excel data. But I recognized this is dependent on Jet OLE DB which seems is not available for x64 windows.


Is there another way to import excel data using a stored procedure.


thank you in advance, rene

Import list from excel file



I try import a table from excel file to become a SharePoint list.

I want the first column in the excel to become the title column (linked to item).

I did some tests and in each tests it tooks a different column of the excel to be the sharepoint column which is "linked to item with edit menu".

What is the logic and how may i control which excel column will be that sharepoint column?


Import Excel file with Data Connection into SSIS


I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.

How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?

When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?

Import List of Worksheet Names from Multiple Sheets Excel File

I am only interested in OPENROWSET/OPENQUERY or xp_cmdshell solutions. Thanks.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Import Excel File to Sql Server on a 64 bit machine


I need to import Excel (xls - 2003-2007) data into a Sql Server 2008 database. Both the Excel file and the database live on a Windows Server 2003 R2 sp2 64 bit machine.  I’m fine with it running in 32 bit mode but it all needs to happen on the same server. Also, I would like it to run in T-Sql but as a fallback we can do this in SSIS.

My T-Sql code which runs fine on a 32 bit machine is as follows:

SELECT * Into #TempTable From OpenRowSet('Microsoft.Jet.Oledb.4.0','Excel 8.0;Database=\\Servername\Folder\filename.xls;HDR=NO;IMEX=1', 'select * from [Main$]')


On the 64 bit server:

Can I import and execute a macro into an Excel file from asp.net?


Is it possible to have an .aspx page open an Excel file, import a macro (saved in a .bas text file), and then run it? I've been able to do this in the past from a MS Access application, but I have no idea where to begin with asp.net. 

If you've done this before code examples would be appreciated, or if you know of any articles on this, links to the articles would be appreciated too.


Import data from excel file to datagridview in vb.net


 Dim DBConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\vb.net\2210201\new marks analysis system\new database\stu_basic.xls;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        Dim SQLString As String = "SELECT TOP 1000 * FROM [Sheet1$]"
        Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
        Dim DBReader As IDataReader = DBCommand.ExecuteReader()
        dg.DataSource = DBReader




i am using this but its doing nothing .

no errors , no data from excel.

whats the problem in it.?

Import and scan excel file


what's the best method to import an excel file and scan it sheet by sheet to find some values.
I'm trying to use interop excel but I don't know if it's the fast and best method. 

Creating Excel file from C#

I used below coding for create a excel sheet.I got from on web site.
In below coding there is no problem.Its create the excel file.If i run second time,its ask to overwrite the current excel file.If I said "Yes" then it ok ,but If I select cancel button,its generate the Error message.The exception is

Import XML File to SQL Table

Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

Convert excel file into xml


Hai, I have two columns with id,name in the excel sheet.I want to convert into xml file in the below format

<?xml version="1.0" encoding="utf-8"?>

<guide xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Boat id="1"> <name> Copper Kettle </name> </Boat> <Boat id="2"> <name> Copper Penny </name> </Boat> <Boat id="3"> <name> Copperhead </name> </Boat> <Boat id="4"> <name> Coppertone </name> </Boat> <Boat id="5"> <name> Copy Boy </name> </Boat> </guide>

suggest me with sample code in ASP.NET. Thanks


Read Excel file from Web Application (C#)



I have a web app where user uploads a file.

File is saved in server locally and I want to read some data from it.

Currently my development  server has office 2003.

So do I need office 2007 in order to be able to  read an excel from office 2007 ?

What are other requirements  ?

I tried a test, using

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wrkBook; // = new Microsoft.Office.Interop.Excel.Workbook();

wrkBook = excelApp.Workbooks.Open(@"C:\www\excel_files\b.xls", ....);

But When trying to open the file I get the error:

Exception from HRESULT: 0x800A03EC

So I'm wondering what I'm missing.


While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Returning datas from two excel file



I used the below code to return the datas from one excel file. My question is if i suppose need to get the records from morethan one excel file using joins, how it is possible.

   string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object 
        OleDbConnection oledbConn = new OleDbConnection(connString);
         // Open connection
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
            // Create new OleDbDataAdapter 
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Employees");




Excel file getting error


when i am trying to save a excel document in library it getting error like "The file cannot be saved because some properties are missing or invalid"

If anyone know the solution for this pls tell me.



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend