Missing Data when using a SQL query on an Excel Spreadsheet

Posted on 2002-04-30
Last Modified: 2012-08-14

I am querying an Excel spreadsheet for stock information but the data being returned is inconsistent, can anyone help?

The excel spreadsheet is set up so that all the cells are text format, but some contain numbers (textual numbers I assume) and some contain strings.

When I query the database, all the cells containing just strings are being returned ok, but anything with a (textual) number can sometimes be returned as a null, although not very consistently.

I use GetRows to dump the query to excel in an array then loop through the array inserting it into the database.  

I keep getting errors saying the Insert SQL is incorrect and it is usually becasue a field that should have been returned as a number from the Excel spreadsheet has returned nothing (even though there is actually a number in the excel spreadsheet!)

If anyone has dealt with this kind of thing any tips would be greatly appreciated.



Question by:eilloc
  • 6
  • 3

Author Comment

ID: 6982201
PS I am getting data back and connecting to the spreadsheet without error, so I am assuming that it is not a problem with the actual query, more a detail in Excel I am missing out...... or maybe not :-)
LVL 20

Expert Comment

ID: 6982515
Are you using those excel cell values in your SQL where cluase ? Then you must be having trouble to decide on which one to put quotes (from string vars) and no quotes for integer/decimal values

I would suggest you to do the followings :
Use vartype function of VBScript to detremine the variable type from excel and then construct the SQL accrodingly.

Here is exmaple of vartype
vbEmpty 0 Empty (uninitialized)
vbNull 1 Null (no valid data)
vbInteger 2 Integer
vbLong 3 Long integer
vbSingle 4 Single-precision floating-point number
vbDouble 5 Double-precision floating-point number
vbCurrency 6 Currency
vbDate 7 Date
vbString 8 String
vbObject 9 Automation object
vbError 10 Error
vbBoolean 11 Boolean
vbVariant 12 Variant (used only with arrays of Variants)
vbDataObject 13 A data-access object
vbByte 17 Byte
vbArray 8192 Array

Dim MyCheck
MyCheck = VarType(300)          ' Returns 2.
MyCheck = VarType(#10/19/62#)   ' Returns 7.
MyCheck = VarType("VBScript")   ' Returns 8.

Now once it return the values, say 8 (for string) you knwo you have to pur quotes around the value. If it is 2 (integer) , then it should not have quotes

Try that and let me know

Author Comment

ID: 6983026
Chhers jitganguly, will try and let you know.....

Author Comment

ID: 6983592

I am deleting all records in the database and then inserting the spreadsheet into the table so I am only ever using an insert sql statement.

The following is one of the ones giving me the error...

INSERT INTO tblItem (fldNoInStock, fldDateAdded, & _
fldTopOffer, fldDescription, fldTracklisting, & _
fldCondition, fldPrice, fldFormatUnits, fldFormat, & _
fldLabel, fldYear, fldCatNo, fldArtist, fldTitle,
fldComposer, fldSortCode, fldSubCat, fldCatID) VALUES & _
(,#2002/04/30#, False, '', '', 'G', 15, , 'LP', & _
'Deutsche Grammophon', '1977', '2740 172-10', & _
'Karajan / Berlin Phil.', & _
'9 Symphonies (8 LP Box Set & Booklet)', & _
'Beethoven', '662', 'Symphonic', 1)

I am using the third lat value as the primary key as it is the clients unique stock code for each item.

The actual example value that is causing the hassle is the first one in the actual values relating to fldNoInStock (shown as nothing above).

The Query on the Excel spreadsheet returns nothing whereas the actual value of this field in the spreadsheet is a number but is not transferring across.

This is happenning very inconsistently.....

I am checking for the actual data types and enclosing them in quotes, hashes or nothing where applicable.  This has been tested out and is not where the problem is being caused.

Thanks for you help

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 20

Expert Comment

ID: 6983641
Ok. Are you doing some validations before constructing this SQL ?
I mean
if corresponding of fldNoInStock in your form is not null/length > 0 then construct the sql

Author Comment

ID: 6983703

I am not doing any validation on the array as I am catching all insert errors in a string and then using On Error Resume Next to go onto the next insert statement.

When the insertion of all the lines is finished, I then display the lines where there was an error on insert to the user updating the catalogue.

The thing is, even though all the lines in the spreadsheet have a value of '1' (test value) for fldNoInStock, the query represents this as null.

My actual problem is not that the SQL fails, more that the correct value just is not being retrieved from the Excel spreadsheet.Thanks for your time, much appreciated

LVL 20

Accepted Solution

jitganguly earned 80 total points
ID: 6983774
Have you tried using casting ? CStr, cdbl,cint etc ? Since VBScript is lossley bound, it is always advisable to use casting while comparing

Author Comment

ID: 6983784
Never thought of that, will try and let you know :-)


Author Comment

ID: 7010833
Hi jitganguly,

Sorry took so long to get back to you.....

Basically here is what I have found out...

If you cut and past a lot into the Excel spreadsheet, then for some reason, the ASP grab of the data seems to miss out random cells, which usually contain numbers.

Cant work this one out but had to build a system where it flagged the missing data and the client has to go in and retype any data that is missing from the database insertion (but present in the spreadsheet!)

No idea how that does that but it seems to be the solution to my missing data problem....

Any hows points are yours and thanks for the help :-)


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: (…
Sending a Secure fax is easy with eFax Corporate ( First, just open a new email message. In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

912 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

19 Experts available now in Live!

Get 1:1 Help Now