Forcing column datatypes, but allowing user entry

LAYGO · #1 (**permalink**) 11-12-09, 13:36

Reading XL: How to Lock Individual Cells in a Worksheet has given me the gist on how to protect a worksheet & select cells. My problem lies in the fact that I will be querying this spreadsheet with ASP/ADO & if we mix datatypes, I end up with Null values and problems. I can not simply SKIP the data that's Null, especially if there IS DATA, but it's just the wrong datatype.

Here is gist of the situation:

we're defining a spreadsheet in which we'll send to Vendors that they'll be entering data we're requesting (see screenshot)
they'll be entering data in cells we define
(green = they can enter data + 1 drop down {A8})
they'll be entering in an unknown amount of data from A14:H65536
(avg has been about 25 rows)
A13:F13 are my own column headers (to be hidden) with their own defined range
(A14:A65536, B14:B65536, etc)
a master range of A14:F65536 is defined
(G/H aren't saved to the DB)
we *NEED* to have all columns be the same datatype from row 14 down, don't care if it's text or number. If they mix the datatypes, I run into this issue:
How To Use ADO with Excel Data from Visual Basic or VBA
(See Considerations That Apply to Both OLE DB Providers)

I'm already successfully querying the spreadsheet w/the use of Named Ranges I define and my column headers. This is the query to get ALL the data:

Code:

SELECT * 
FROM DataAllFrags
WHERE 
    ComponentFragmentName IS NOT NULL 
    OR 
    HomogeneousName IS NOT NULL 
    OR 
    MaterialName IS NOT NULL 
    OR 
    CASNumber IS NOT NULL 
    OR 
    SubstanceMass IS NOT NULL 
    OR 
    SubstancePPM IS NOT NULL

Using named ranges and the column headers I added allowed me to go from generic queries like this:

Code:

SELECT DISTINCT * 
FROM B14:A1000

To this:

Code:

SELECT DISTINCT HomogeneousName
FROM DataHomName
WHERE
    HomogeneousName IS NOT NULL

Bottom line:

I need to protect the spreadsheet so they can't add/delete columns/rows above row 14. They can add all they want to below row 14.
The datatype of the columns must all be text or A-D text, E-F numbers.
If E-F are numbers (see the spreadsheet values screenshot):
- E = a number that can be whole number to a decimal to the ten thousandths or more: 1234 or .00001234
- F = a number that's a Part Per Million number based on the sum of the E where they are of the same part
See the spreadsheet values screenshot to see where some numbers are stored as text and that's what kicked off this whole issue.
I need to protect the sheet & certain cells. No new columns can be inserted, but rows passed row 14 can be as many as they need.
A subquestion is interating through all the worksheets vs 1 worksheet per file without being slower than it already can be. Possible?

IS ANY OF THAT POSSIBLE? I have free reign to define the spreadsheet how I want, but in all honesty, I'm not a spreadsheet designer.

Any help is appreciated. I hope I made *SOME* sense.

LAYGO · #2 (**permalink**) 11-12-09, 14:13

Also, this page has been insightful:
Daily Dose of Excel Blog Archive External Data - Mixed Data Types

Here is my connection string:

Code:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & inFilename & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

IMport EXport (IMEX) value is 1, saying send mixed datatypes as Text, but that's not true in my case. I'm getting Nulls.

I've even attempted to cast the values when selecting them to no avail:

Code:

SELECT 
      CStr(SubstanceMass)
    , TYPENAME(SubstanceMass) 
FROM [DataAllFrags]

LAYGO · #3 (**permalink**) 11-12-09, 16:26

I wasn't sure I wanted to but I set my registry TypeGuessRows from 19 to 0 & it returned all strings, which is good.

Went to the Supe to see if he'd be willing to do it on the server & he was ok with it. So, now I can have mixed datatypes and the TypeGuessRows = 0 sees that if it is mixed, return text. I can cast the value before DB insertion.

sco08y · #4 (**permalink**) 11-14-09, 21:26

I'm not sure how to manage ADO's interaction with Excel. The docs confirm what you're saying: it will just wipe out data that's the wrong type, not even try to throw an error.

Some newer versions of Excel allow you to put type constraints on individual cells. I know the 2007 version does, and I think the 2003 can do it too. (Don't have Excel in front of me at the moment, but I think it's under the Data menu.) I don't think you can do it for new rows, so you'd have to disable adding rows and deleting rows. I think all these protections are available through worksheet protection options.

One way to read data that is far more flexible is to script Excel and actually navigate through the spreadsheet. The downside of this is that you're, naturally, open to any possible macro viruses embedded in the sheet. Of course, it's also _much_ slower...

Another way is to use a library that will parse the .xls file format without invoking Excel. I've done that in Perl, but I don't know if there is a VB library.

Quote:

A subquestion is interating through all the worksheets vs 1 worksheet per file without being slower than it already can be. Possible?

I think you'd have to set up defined names per worksheet. It shouldn't affect speed much. Note that names can apply to the whole workbook or apply to individual worksheets.

LAYGO · #5 (**permalink**) 11-16-09, 12:24

Thanks Scoo8y. While I've got most of the work done, finding/including a library to parse the spreadsheet would only require a rework. As long as the server and my dev server have the TypeGuessRows=0 & IMEX=1, then it will give me just STRINGS. I'm ok with that. So, that's done. It won't really matter if the user inputs a cell as text when it is in fact a number. I get a string. I just have to cast the 2 columns on my side as DOUBLES.

Everything thus far is adding up . . . except when comparing values of the sums of those columns!

I think someone just ran a formula, not accounting for text as number & the sum was off. I was racking my brain trying to figure out what was going on, but my application was RIGHT & the spreadsheet with mixed datatypes was reporting a WRONG sum.

It's ok. I'm verifying the data before DB insert. If the SUMS aren't equal, then it'll fail. The only problem is while adding them up cast as DOUBLES, it works, but when comparing my calculated sum to the report sum (as a STRING), even when cast as a DOUBLE, it still fails to match!

This failed!

Code:

thisCalculatedPPM = ((thisComponent.pFragSubstanceMass / thisMassSum) * 1000000)
            
boolIsEqual = False
If CDbl(thisComponent.pFragSubstancePPM) = CDbl(thisCalculatedPPM) Then
    boolIsEqual = True
End If

This works! Note going from CDbl -> CStr

Code:

thisCalculatedPPM = ((thisComponent.pFragSubstanceMass / thisMassSum) * 1000000)
            
boolIsEqual = False
If CStr(thisComponent.pFragSubstancePPM) = CStr(thisCalculatedPPM) Then
    boolIsEqual = True
End If

(thisComponent.* = object reference to the XLS data)

I'm really hoping I don't run into any instances where that truly won't be equal.

Internet Services

Web Development

Digital Marketing

Consumer Tech