 |  |  |  |  |  |  |  |
Retriving data from excel sheet into an asp page
Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
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
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
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
Hope this helps, but please provide further information if you want more detailed help!
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 ...
•
•
•
•
Originally Posted by 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
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
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
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
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
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).
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).
•
•
•
•
Originally Posted by 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
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
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
See here
LINK
Google / MSDN is our Friend!
LINK
Google / MSDN is our Friend!
•
•
•
•
Originally Posted by 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
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
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
•
•
•
•
Originally Posted by 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
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
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
|
Similar Threads
- Exporting ASP pages to Excel (ASP)
- Get data from an external url (hotmail) from existing asp page without refresh (ASP)
- 1 .Exporting data from Dataset to XLl sheet in VB.net Windows Application &vice versa (VB.NET)
- To Fetch EXCEL sheet values thro' ASP page. (Existing Scripts)
Other Threads in the ASP Forum
- Previous Thread: Accessing IIS site
- Next Thread: sql help
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest ASP Posts
- Today's Posts
- Unanswered Threads
archive asp asp.net aspandmssqlserver2005 aspandmssqlserver2005connection aspconnection connection database databaseconnection dreamweaver excel fso iis msmsql mssql2005 mssqlserver2005 mssqlserver2005andasp mssqlserverandasp opentextfile record searchbox selectoption single specfic sqlserver sqlserverconnection windows7
