Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Missing Data when using a SQL query on an Excel Spreadsheet

Posted on 2002-04-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.....
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

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 240 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 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