Solved

Missing Data when using a SQL query on an Excel Spreadsheet

Posted on 2002-04-30
9
246 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
  • 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

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…
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 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

11 Experts available now in Live!

Get 1:1 Help Now