Importing Excel Spreadsheet Data to SQL Server 2000 Database (using ASP), with field validation levels at each record. Also, unknown record count! Anyone?
I am an ASP programmer and have adopted a ready made script to import data from an excel spreadsheet directly into a SQL server database, after certain field level validations.
The sample of a part of the script is shown below. My problem is:
1. I do not know the row count of the number of rows in the excel sheet, so apparently I have to select like roughly 500 rows in the excel sheet and name the entire range, some name, in my case called it "sheeter". My concern is if the data grows over 500 rows, what shall happen to my SQL statement which I am using to import the table range from excel, which is made of columns and rows (the matrix range). I want to dynamically grow my loop in some way to select more than the 500 if required. If I select the entire column range till the last excel row, then my PC crashes! I have to select a fix and valid range to query, which is logically absolute! Now I need help to grow this record count, till data exists. Now comes the question of empty rows : )
2. I need to validate each and every record and then directly insert the entire row using SQL statements to the database (SQL). My problem is memory and time! Any help on this then?
If anyone has some other alternative, function, procedure etc. please HELP OUT!
Points are your reward, and mine from my boss!
Thanx in advance!
The sample of a part of the script is shown below. My problem is:
1. I do not know the row count of the number of rows in the excel sheet, so apparently I have to select like roughly 500 rows in the excel sheet and name the entire range, some name, in my case called it "sheeter". My concern is if the data grows over 500 rows, what shall happen to my SQL statement which I am using to import the table range from excel, which is made of columns and rows (the matrix range). I want to dynamically grow my loop in some way to select more than the 500 if required. If I select the entire column range till the last excel row, then my PC crashes! I have to select a fix and valid range to query, which is logically absolute! Now I need help to grow this record count, till data exists. Now comes the question of empty rows : )
2. I need to validate each and every record and then directly insert the entire row using SQL statements to the database (SQL). My problem is memory and time! Any help on this then?
If anyone has some other alternative, function, procedure etc. please HELP OUT!
Points are your reward, and mine from my boss!
Thanx in advance!
j2nku
Please, provide the sample - at least the part where you read the excel and do the looping.
ASKER
Oh God I forgot, sorry!!! Here you go...
<%
' Selected constants from adovbs.inc
Const adOpenStatic = 3
Const adLockPessimistic = 2
Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols
' This is all standard ADO except for the connection string.
' You can also use a DSN instead, but so it'll run out of the
' box on your machine I'm using the string instead.
Set cnnExcel = Server.CreateObject("ADODB
cnnExcel.Open "DBQ=" & Server.MapPath("Intake_She
' Same as any other data source.
' FYI: TestData is my named range in the Excel file
Set rstExcel = Server.CreateObject("ADODB
rstExcel.Open "SELECT * FROM sheeter;", cnnExcel, _
adOpenStatic, adLockPessimistic
' Get a count of the fields and subtract one since we start
' counting from 0.
iCols = rstExcel.Fields.Count
%>
<table border="1">
<thead>
<%
' Show the names that are contained in the first row
' of the named range. Make sure you include them in
' your range when you create it.
For I = 0 To iCols - 1
Response.Write "<th>"
Response.Write rstExcel.Fields.Item(I).Na
Response.Write "</th>" & vbCrLf
Next 'I
%>
</thead>
<%
rstExcel.MoveFirst
' Loop through the data rows showing data in an HTML table.
Do While Not rstExcel.EOF
Response.Write "<tr>" & vbCrLf
For I = 0 To iCols - 1
Response.Write "<td>"
Response.Write rstExcel.Fields.Item(I).Va
Response.Write "</td>" & vbCrLf
Next 'I
Response.Write "</tr>" & vbCrLf
rstExcel.MoveNext
Loop
%>
</table>
<%
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
%>
<%
' Selected constants from adovbs.inc
Const adOpenStatic = 3
Const adLockPessimistic = 2
Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols
' This is all standard ADO except for the connection string.
' You can also use a DSN instead, but so it'll run out of the
' box on your machine I'm using the string instead.
Set cnnExcel = Server.CreateObject("ADODB
cnnExcel.Open "DBQ=" & Server.MapPath("Intake_She
' Same as any other data source.
' FYI: TestData is my named range in the Excel file
Set rstExcel = Server.CreateObject("ADODB
rstExcel.Open "SELECT * FROM sheeter;", cnnExcel, _
adOpenStatic, adLockPessimistic
' Get a count of the fields and subtract one since we start
' counting from 0.
iCols = rstExcel.Fields.Count
%>
<table border="1">
<thead>
<%
' Show the names that are contained in the first row
' of the named range. Make sure you include them in
' your range when you create it.
For I = 0 To iCols - 1
Response.Write "<th>"
Response.Write rstExcel.Fields.Item(I).Na
Response.Write "</th>" & vbCrLf
Next 'I
%>
</thead>
<%
rstExcel.MoveFirst
' Loop through the data rows showing data in an HTML table.
Do While Not rstExcel.EOF
Response.Write "<tr>" & vbCrLf
For I = 0 To iCols - 1
Response.Write "<td>"
Response.Write rstExcel.Fields.Item(I).Va
Response.Write "</td>" & vbCrLf
Next 'I
Response.Write "</tr>" & vbCrLf
rstExcel.MoveNext
Loop
%>
</table>
<%
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
%>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have ANOTHER VERY VERY serious problem! I can for ONCE compromise on the issue of performance and inserting rows and columns, but I am facing an unexpected error (below):
Error Type:
Provider (0x80004005)
Unspecified error
/eximport/Default.asp, line 17
in the file!! This is because of some kind of multiple user login problem or connection problem with the excel spredsheet through the webserver. If you can provide a solution for this first than anything else it is much better than the earlier problems!
Thanks again!
Error Type:
Provider (0x80004005)
Unspecified error
/eximport/Default.asp, line 17
in the file!! This is because of some kind of multiple user login problem or connection problem with the excel spredsheet through the webserver. If you can provide a solution for this first than anything else it is much better than the earlier problems!
Thanks again!
Here a link to an excellent site,
you'll find more on how to define dynamic named ranges
http://www.cpearson.com/excel/named.htm
Hilaire
you'll find more on how to define dynamic named ranges
http://www.cpearson.com/excel/named.htm
Hilaire
By design an excel workbook is not intended to be shared.
You may face serious problems with concurrency/read locks.
Your best bet would be to upload the file to a database (Access, SQL Server) to avoid this kind of situation.
Also check that the file is not already opened by an Excel session.
Does the server.mappath resolve the path name ?
Is the file in the same virtual folder than the asp page, as the current path suggests ?
Also check connection.state after opening the connection.
What's on line 17 ??
Hilaire
You may face serious problems with concurrency/read locks.
Your best bet would be to upload the file to a database (Access, SQL Server) to avoid this kind of situation.
Also check that the file is not already opened by an Excel session.
Does the server.mappath resolve the path name ?
Is the file in the same virtual folder than the asp page, as the current path suggests ?
Also check connection.state after opening the connection.
What's on line 17 ??
Hilaire
ASKER
Line 17 error:
cnnExcel.Open "DBQ=" & Server.MapPath("Intake_She
cnnExcel.Open "DBQ=" & Server.MapPath("Intake_She
Can you issue a
Response.Write Server.MapPath("Intake_She
to check the path ?
Response.Write Server.MapPath("Intake_She
to check the path ?
Also try to surround the path whith double quotes
cnnExcel.Open "DBQ="""& Server.MapPath("Intake_She
Since the driver may not support paths/file names with spaces within it
cnnExcel.Open "DBQ="""& Server.MapPath("Intake_She
Since the driver may not support paths/file names with spaces within it
ASKER
No Hilaire, the reason for the error is due to the excel sheet getting locked out after one single import mechanism has run over it. I need to work a way round when no more errors occur due to the kind of locking!
Any one on that...?
Any one on that...?
ASKER
Dear Hilaire, can you please elaborate a bit on checking the connection.state & checking if the file is locked or in closed state? If you explain a bit more on this?
ASKER
I used the double quote suggestion you gave for the excel sheet connection path, and I found this:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.
/eximport/Default.asp, line 19
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.
/eximport/Default.asp, line 19
try using adLockReadOnly value=1
instead of adLockPessimistic
instead of adLockPessimistic
ASKER
Thanks Hilaire...
I was away for a while over the weekend! Ur points are there now! Thanks loadz...
K
I was away for a while over the weekend! Ur points are there now! Thanks loadz...
K
Rahul, I am stuck on a similar problem. I have looked at your code above and it show the contents of an excel spreadsheet on a web page but how do you then import that into a database?