• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Missing Data when using a SQL query on an Excel Spreadsheet

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
eilloc
Asked:
eilloc
  • 6
  • 3
1 Solution
 
eillocAuthor Commented:
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
 
jitgangulyCommented:
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
 
eillocAuthor Commented:
Chhers jitganguly, will try and let you know.....
0
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!

 
eillocAuthor Commented:
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
 
jitgangulyCommented:
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
 
eillocAuthor Commented:
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
 
jitgangulyCommented:
Have you tried using casting ? CStr, cdbl,cint etc ? Since VBScript is lossley bound, it is always advisable to use casting while comparing
0
 
eillocAuthor Commented:
Never thought of that, will try and let you know :-)

eilLOC
0
 
eillocAuthor Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now