?
Solved

Missing Data when using a SQL query on an Excel Spreadsheet

Posted on 2002-04-30
9
Medium Priority
?
252 Views
Last Modified: 2012-08-14
Hi,

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.

Thanks

eilLOC

0
Comment
Question by:eilloc
[X]
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
9 Comments
 

Author Comment

by:eilloc
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 :-)
0
 
LVL 20

Expert Comment

by:jitganguly
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
0
 

Author Comment

by:eilloc
ID: 6983026
Chhers jitganguly, will try and let you know.....
0
Technology Partners: 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

by:eilloc
ID: 6983592
jitganguly,

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

eilLOC
0
 
LVL 20

Expert Comment

by:jitganguly
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
0
 

Author Comment

by:eilloc
ID: 6983703
Hi,

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

eilLOC
0
 
LVL 20

Accepted Solution

by:
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
0
 

Author Comment

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

eilLOC
0
 

Author Comment

by:eilloc
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 :-)

eilLOC
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
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/…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

777 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