Retriving data from excel sheet into an asp page

[Pallavivt]

Hello

I am new in ASP

what i want is to retrive the records from excel sheet and show them on an asp page in tabular format

i tried to do it with ADODB.Connection
conn.Provider = "MSDASQL"
conn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" & Server.mappath("/"& excelfilename) &";ReadOnly= false ; UID=admin;"

the connection get opened successfully but no the main problem is to fetch the data into recorset. the error comes into query

pls do tell me what should b the query for fetching the record

i also tried the "Microsoft.Jet.OLEDB.4.0" provider but it gives an error in connection as "cant find installable isam"

thanks

[Paladine]

Ok,

Well since you provided no information about what columns are in your table (excel spreadsheet) and what data you want from that table (i.e. DDL (Data Definition Language)) providing help on a query will be rather hard.

So until you provide that information, help will be difficult to get.

The provider "Microsoft.Jet.OLEDB.4.0" is for Access not Excel, so your first choice was correct.

Provide the query you have tried!

Do you know how to creat a recordset?

Example:

Table (excel spreadsheet)
Col A || Col B

Dim objConn
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" & Server.mappath("/"& excelfilename) &";ReadOnly= false ; UID=admin;"

Dim strSQL
strSQL = "SELECT Col A FROM excelfilename"
Dim objRS
Set objRS = Server.CreateObject ("ADODB.RecordSet")
objRS.Open strSQL, objConn
...
...
'  Your code to display this recordset
...
...
'  Memroy/garbage clean up.... in the proper order
objRS.Close
Set objRS = Nothing
objConn.Close
Set objClose = Nothing

...

Hope this helps, but please provide further information if you want more detailed help!

:cool:

Hello

I am new in ASP

what i want is to retrive the records from excel sheet and show them on an asp page in tabular format

i tried to do it with ADODB.Connection
conn.Provider = "MSDASQL"
conn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" & Server.mappath("/"& excelfilename) &";ReadOnly= false ; UID=admin;"

the connection get opened successfully but no the main problem is to fetch the data into recorset. the error comes into query

pls do tell me what should b the query for fetching the record

i also tried the "Microsoft.Jet.OLEDB.4.0" provider but it gives an error in connection as "cant find installable isam"

thanks

[Pallavivt]

hello
thanks

i want to select all the columns from the excelfile say abc.xls which contains sheet name abc

i tried the following queries and got error

excelfilename = "abc.xls"
"select * from " & excelfilename

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'xls'. Make sure the object exists and that you spell its name and the path name correctly.

select * from [abc.xls$]

The Microsoft Jet database engine could not find the object 'abc.xls$'. Make sure the object exists and that you spell its name and the path name correctly.

"SELECT ColA,ColB,ColC FROM abc"
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'abc' Make sure the object exists and that you spell its name and the path name correctly.

pls reply soon

thanks

pallavi

[Pallavivt]

now it is giving me another erroer as
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

i tried with
;Readonly = false; and also ;Readonly= true;
but still it is giving the same error
psl tell me how to do solve this error
also the file on the server is not readonly

[Paladine]

I am guessing you have it open already. Don't have the Excel Spreadsheet open when trying to connect to it... but I am just guessing.

Here is what you do for the select statement.

Select * FROM [Sheet1$]

Where the [Sheet1$] represent the Worksheet that is in the workbook (the XLS) you are accessing.

Hope that helps .... and thanks for clarifying your request (you will always get a faster response that way).

:cool:

now it is giving me another erroer as
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

i tried with
;Readonly = false; and also ;Readonly= true;
but still it is giving the same error
psl tell me how to do solve this error
also the file on the server is not readonly

[Pallavivt]

even if i stype the sheet name teill it gives me error

the excel filename and the name of the sheet are the same
strSQL = "SELECT * FROM [DIV$]"

Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'DIV$'. Make sure the object exists and that you spell its name and the path name correctly.

i tried doing with "Select * from DIV.DIV" & excelfilename

Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

if the query is "Select * from DIV.DIV"

Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'DIV'. Make sure the object exists and that you spell its name and the path name correctly.

even "Select * from DIV.[DIV$]" gives the same error

[Paladine]

See here

LINK

Google / MSDN is our Friend!

even if i stype the sheet name teill it gives me error

the excel filename and the name of the sheet are the same
strSQL = "SELECT * FROM [DIV$]"

Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'DIV$'. Make sure the object exists and that you spell its name and the path name correctly.

i tried doing with "Select * from DIV.DIV" & excelfilename

Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

if the query is "Select * from DIV.DIV"

Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'DIV'. Make sure the object exists and that you spell its name and the path name correctly.

even "Select * from DIV.[DIV$]" gives the same error

[ManjuPeter]

Hi Pallavi,

If you have the solution for the "The Microsoft Jet database engine could not find the object 'abc.xls$'. Make sure the object exists and that you spell its name and the path name correctly." error, Do let me know.

I am trying to access excel similarly and update my database. I can open the connection but my record set is not getting opened . My excel file has a worksheet named Sheet1 and I am using "select * from [Sheet1$]" as the query string.

please help

hello
thanks

i want to select all the columns from the excelfile say abc.xls which contains sheet name abc

i tried the following queries and got error

excelfilename = "abc.xls"
"select * from " & excelfilename

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'xls'. Make sure the object exists and that you spell its name and the path name correctly.

select * from [abc.xls$]

The Microsoft Jet database engine could not find the object 'abc.xls$'. Make sure the object exists and that you spell its name and the path name correctly.

"SELECT ColA,ColB,ColC FROM abc"
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'abc' Make sure the object exists and that you spell its name and the path name correctly.

pls reply soon

thanks

pallavi

[madmital]

http://www.asp101.com/samples/xl_data.asp

[AlThomson]

Hi guys

I read this post when I got stuck connecting to excel.

I now have a method that works for me. I'm not entirely sure of the pros and cons of using this method.

'Dims
Dim cn
Dim rs
Dim strQuery
'---------
set cn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("adodb.recordset")



With cn


.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=C:\inetpub\wwwroot\server\adotest.xls; ReadOnly=False;"
.Open


End With


'If not using a named range, replace myrange1 with [sheet1$]
strQuery = "SELECT * FROM myRange1"
rs.Open strquery, cn


'Check for records
rs.MoveFirst
if rs.BOF = true and rs.EOF = true then
'no records
response.Write "Error:: Products spreadsheet is empty!"
else
'Records Exist
'Loop through records printing out the fields
while not rs.EOF
response.Write rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & "<BR><BR>"
rs.MoveNext
wend
end if


'Cleanup
rs.close
cn.Close
set cn = nothing
set rs = nothing

[CCruciger]

From personal experience with Excel and data pump tasks in a VB.Net call to an SQL DTS I can almost guarantee that your original problem with the file not found was a permissions issue across server and client.

I am hoping I can adapt your work around to my own problem.

[cancer10]

http://www.tutorialized.com/tutorial/Displaying-Records-From-An-Excel-Database/15688

[tejaswinis]

Hi All,
I am facing the same problem... i have an excel sheet named test.xls and the date is in the sheet bnamed sheet1
i need to upload the excel file to the server.
it was working for me all these days.
all of a sudden it says........
Microsoft JET Database Engine (0x80040E37)
The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

please help me
:)
thanks in advance

[CrashPunk]

The solution!

Sheet name='pepe'
Range=A5:F50

Select * From [pepe$A5:F50]

Hector Coarasa

[Pawankrgt]

string filename = @"C:\myfile.xls";

 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

                "Data Source=" + filename + ";" +

                "Extended Properties=Excel 8.0;";

 

OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet myDataSet = new DataSet();

 

dataAdapter.Fill(myDataSet, "ExcelInfo");

 

DataTable dataTable = myDataSet.Tables["ExcelInfo"];

 

var query = from r in dataTable.AsEnumerable()

            select new

            {

                RelationNr = r.Field<double>("RelationNumber"),

                ClientName = r.Field<string>("ClientName"),

            };

 

foreach (var item in query)

{

    Console.WriteLine(item.ClientName);              

}

...Hope it will help.

[harikapotham]

I am trying to read data from excel sheet into drop down list of asp page.
but i got an error.plz tell the correct code..

string connString = ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString;
OleDbConnection oledbConn = new OleDbConnection(connString);

OleDbCommand cmd = new OleDbCommand("SELECT * FROM [harika$]", oledbConn);

OleDbDataAdapter oleda = new OleDbDataAdapter();
oledbConn.Open();

oleda.SelectCommand = cmd;

DataSet ds = new DataSet();

oleda.Fill(ds, "Employees");

DropDownList1.DataSource = ds.Tables[0].DefaultView;
DropDownList1.DataBind();
oledbConn.Close();

[risha9177719594]

My excel file is on local and i want to read the data from that file, and display the same on the asp page. is that possible?

[cyberdaemon]

have u try to use xml.?

here is example of it.. and place save it in the sql table

@xmlInTransit xml

INSERT INTO tblInTransit
        (
        ReceivingPlant
        ,SupplyingPlan
        ,STONumber
        ,ItemNo
        ,Material
        ,MaterialDescription
        ,Quantity
        ,Unit
        )

        SELECT      NODE.value('@ReceivingPlant','[varchar](4)')
                    ,NODE.value('@SupplyingPlan','[varchar](50)')
                    ,NODE.value('@STONumber','[bigint]')
                    ,NODE.value('@ItemNo','[int]')
                    ,NODE.value('@Material','[bigint]')
                    ,NODE.value('@MaterialDescription','[nvarchar](50)')
                    ,NODE.value('@Quantity','[int]')
                    ,NODE.value('@Unit','[int]')




        FROM @xmlInTransit.nodes('dsExcelInTransitRecords/dtExcelInTransitRecords') TEMPXML(NODE)

once it compiled you ca now code your code behind..

here is my code behind for that..

string conn = "";
            if (FileUpload1.HasFile)
            {
                string fname = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string fExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string fLocation = Server.MapPath("/ExcelFileUpload/" + fname);


                if (fExtension == ".xls")
                {
                    conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (fExtension == ".xlsx")
                {
                    conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                else if (fExtension != ".xlsx" || fExtension != "xls" || fExtension == String.Empty)
                {

                    return;
                }
                else
                {

                    return;
                }

                FileUpload1.SaveAs(fLocation);

                OleDbConnection con = new OleDbConnection(conn);
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                DataTable dtCOO9Report = new DataTable();
                con.Open();
                DataTable dtExcelsheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string getExcelSheetName = dtExcelsheetName.Rows[0]["TABLE_NAME"].ToString();
                cmd.CommandText = "Select * from [" + getExcelSheetName + "]";
                dAdapter.SelectCommand = cmd;

                DataSet ds = new DataSet("dsExcelC009ReportRecords");
                dAdapter.Fill(ds, "dtExcelC009ReportRecords");

                foreach (DataColumn dc in ds.Tables["dtExcelC009ReportRecords"].Columns)
                {
                    dc.ColumnMapping = MappingType.Attribute;
                }


                con.Close();

                _c009ReportEntities.xmlC009Report = ds.GetXml();
                if (_c009ReportBll.InsertXMLC009Report(_c009ReportEntities) > 0)
                {

                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                }

just double check my code. but it runs on my machine..

good luck