Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Question Unanswered: Forcing column datatypes, but allowing user entry

    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.

    Attached Thumbnails Attached Thumbnails mdf-spreadsheet.bmp   mdf-spreadsheet-values.bmp  

  2. #2
    Join Date
    Oct 2009
    Posts
    67
    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]

  3. #3
    Join Date
    Oct 2009
    Posts
    67
    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.

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

    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.

  5. #5
    Join Date
    Oct 2009
    Posts
    67
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •