Forum Discussion

S_Leonardo's avatar
S_Leonardo
Occasional Contributor
9 years ago

DDT object not reading excel values properly

Hi all,   I have several scripts that read data from excel sheets using DDT objects and i'm having problem with one of them.   I can't explain the problem using just words so i made a little scri...
  • Marsha_R's avatar
    Marsha_R
    9 years ago

    We are giving you workarounds and I would hope someone has a real answer but at least these things work.

     

    My experience has been that we can't leave data cells blank and test for that.  We ended up putting a period in the "no data" cells and testing for a period rather than a blank.  

     

    It's not elegant but it works and we didn't have time to stop and figure out something pretty.  :)

Marsha_R's avatar
Marsha_R
Moderator

We are giving you workarounds and I would hope someone has a real answer but at least these things work.

 

My experience has been that we can't leave data cells blank and test for that.  We ended up putting a period in the "no data" cells and testing for a period rather than a blank.  

 

It's not elegant but it works and we didn't have time to stop and figure out something pretty.  :)

joseph_michaud's avatar
joseph_michaud
Moderator
9 years ago

By default the ODBC driver scans the first 8 rows (ignoring the header row) to determine the data type of the column.  Because column C in Sheet_A has nothing in the first 8 rows I would guess that the column is considered as text.  Any non-text after that would be considered as NULL.

 

https://support.smartbear.com/viewarticle/70340/  "Using Excel Files as Data Storages"

 

Searching 'TypeGuessRows' on the web points to some articles discussing setting TypeGuessRows to 0 and ImportMixedTypes to Text, and using a connection string with IMEX=1 to allow mixed data types:

 

https://jingyangli.wordpress.com/2009/02/13/imex1-revisit-and-typeguessrows-setting-change-to-0-watch-for-performance/

 

(but that is a bit beyond me.  Never tried it.)