Solved

Querying an Excel Spreadsheet and using GetRows to get it all records into an array

Posted on 2002-04-25
8
349 Views
Last Modified: 2008-02-01
Hi All,

I am trying to query a spreadsheet, put the retrieved recordset into an array, and then insert this array into an access database.  Problem is getting this error....

logic as follows
----------------0
Connecting to Excel spreadsheet using ADO (fine)

Retrieving all columns in a specified area (ie category_area)

Using GetRows on the recordset to create an array
(This is the line in the code that the error occurs on)

Getting the following error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Excel Driver] Numeric field overflow.



Had a look about but no real answers on how to solve this problem.

Excel spreadsheet contains about 10 columns of mixed format types text and numbers.  Some of the colums can be null, blank or incomplete in terms of the data format within the table in Access I am trying to insert them into, but I will catch they sort of errors over at the Access side.

I can output some results by just doing rsInsert("fldID") but I need to grab all of the records at once and insert them into a database.

Can anyone help me... does GetRows work when using a recordset generated from an Excel spreadsheet, why is the milka cow lilac.....

Cheers in advance

eilLOC
0
Comment
Question by:eilloc
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:mgfranz
ID: 6968995
I don't think this a problem with GetRows as it is in the data the Excel table contains.

Generally the 80004005 error is a permission error, but it can mean more as you have found.  I have never seen the two combined in the same message as you have.

Post the page code and a sample row of the Excel Sheet being called.
0
 

Author Comment

by:eilloc
ID: 6969030
Cheers mgfranz, yir a star!


-----------catalogue_insert.asp---------------

Dim numCat, strPath, sqlCurrent, arrCurrent, connExcel, dbconn, exconn, sqlInsert, arrInsert, rsInsert
numCat = Request.Form("CatID")
strPath = Request.Form("SpreadSheet")

If IsNumeric(numCat) = False OR numCat = "" OR strPath = "" Then
     Response.Redirect "/mixedup/admin/ad_catalogue/catalogue_result.asp?type=invalid_qs"
     Response.End
End If

Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strPath) Then
     connExcel = "Provider=MSDASQL.1;" & _
                     "Persist Security Info=False;" & _
                     "Extended Properties=""DBQ=" & _
                      strPath & _
                     ";Driver={Microsoft Excel Driver " & _
                     "(*.xls)};" & _
                     "DriverId=278;FIL=excel 8.0;" & _
                     "MaxBufferSize=2048;" & _
                     "MaxScanRows=8;" & _
                     "PageTimeout=5;ReadOnly=1;" & _
                     "SafeTransactions=0;Threads=3;" & _
                     "UID=admin;UserCommitSync=" & _
                     "Yes;"";" & _
                     "User Id=admin;"
Else
     Response.Redirect "/mixedup/admin/ad_catalogue/catalogue_result.asp?type=no_file"
     Response.End
End If

Set exconn = Server.CreateObject("ADODB.Connection")
exconn.open connExcel

sqlInsert = "SELECT * FROM category_area"
Set rsInsert = Server.CreateObject("ADODB.Recordset")
rsInsert.open sqlInsert, exconn, 1
If rsInsert.RecordCount < 1 Then
     arrInsert = ""
Else
     Response.Write("<br>This is the value = " & rsInsert("fldNoInStock"))
     arrInsert = rsInsert.GetRows()
End If

rsInsert.close
Set rsInsert = Nothing

exconn.close
Set exconn = Nothing

-----------catalogue_insert.asp---------------


AND AN EXAMPLE ROW FROM THE SPREADSHEET


------example row (missed out column headings for brevity)---------------

Concerto
     
2356
     
Bartok     The Three Piano Concertos
     
Anton Dikov - piano / Sofia PO / Dimiter Manolov
     
AVM 1010
     
1988

Balkanton
     
LP
     
4
     
2
     
NM
         
"asldfjlkasjdflkasjdflkjasdfl  ljlkj
dkgjlksdjgsldf

ldkgjlsdfjgl"    
     
FALSE
     
37358  (NB Just noticed, this should be a date! ***)
     
1                                        
-----------example row---------------

*** after noticing I tried it out with proper date formatting but still same error
0
 
LVL 18

Accepted Solution

by:
mgfranz earned 75 total points
ID: 6969163
Personally, I don't like your connection string...

Here is what I would use; Jet 4.0 required...

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .ConnectionString = "Data Source="&strPath&";" & _
"Extended Properties=Excel 8.0;"
     .Open
End With

And do some simple debugging... comment out lines like the "insert" call to see it that is where you are having a problem.

I have an excellent file downloaded and bookmarked, I suggest it to all my friends who use Excel with ASP; http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257819
0
 
LVL 19

Expert Comment

by:webwoman
ID: 6969344
>>Excel spreadsheet contains about 10 columns of mixed format types text and numbers.  Some of the colums
can be null, blank or incomplete in terms of the data format within the table in Access I am trying
to insert them into, but I will catch they sort of errors over at the Access side.

>>

I suspect this is your big problem.

You can't set the numeric fields as numeric, because they probably have alpha info as well. You can't set any fields as required, since they might be blank. You can't set the length of anything, since you don't know what data will be there or how long it will be.

Access will not catch the errors -- ASP will. And did.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 18

Expert Comment

by:mgfranz
ID: 6969413
I don't agree WW, GetRows creates an array of the values parsed from each row in the .xls, the array looks like this

0,0
0,1
0,2
0,3
0,4
1,0
1,1
1,2
1,3
1,4
2,0
2,1
2,2
2,3
2,4
etc...

Even if the data was null, empty or incomplete, the array would still contain that data.

I still suspect the connection string is the primary fault...

And, change the RS Set to this;

Set rsInsert = exconn.Execute(sqlInsert)

'rsInsert.open sqlInsert, exconn, 1  Commented out

0
 

Author Comment

by:eilloc
ID: 6969499
hi all,

Cheers mgfranz and webwoman...

Just back online and getting back to work... will try the connection string and then let you know how it goes....

eilLOC

PS the code is fairly stripped down and any errors I get on insertion in access will be caught elsewhere....not in the code block above!
0
 

Author Comment

by:eilloc
ID: 6969571
Wheyheya!!!

Cheers mgfranz, the connection string was the solver so the points are yours and thank you very much

vitual pints on me over at the netpub!!!!

eilLOC

ps cheers ww
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6969577
I wish!  I'm out... A nice lager sounds good!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now