3 Replies Latest reply: May 15, 2014 9:54 AM by Data Kruncher RSS

    Monarch v7 Export to Access and NULL Values

    Derek G.

      Hi All, First post here.

       

      I am using Monarch v7 and using a template to extract the data and dump into a MS Access 2003 database. This database contains queries and also interacts with a visual basic application developed in house.

       

      The previous method of dumping the data into this database was with an ODBC connection in which Text fields were imported and if blank were considered NULL. (In access or VB if you used the Is Null function in a query it would return the blank fields)

       

      With the use of a Monarch templete on a flat file, Strings / Text fields are imported as empty strings (""), not NULL values, meaning all the queries and VB functions with IsNull is not reporting correctly.

       

      Before I go amending all my queries and VB code is there a way for strings to be pulled in a NULL values and not "" using a Monarch template? It seems Monarch only reads blank numbers and date as null shown as (NULL) in table view.

       

      Any help would be appreciated, Thanks,

       

      Derek

        • Monarch v7 Export to Access and NULL Values
          Data Kruncher

          Hi Derek, and welcome to the forum.

           

          The solution here lies in creating some calculated fields, one for each field which may have null values, which employ a bit of a trick that's been used around here for a while.

           

          For example if one field is CustomerName, name the field extracted from the flat file, say, PreCustomerName. Now build a CustomerName (character type) calculated field with the expression:

          [font="courier"]If(PreCustomerName="",Str(1/0),PreCustomerName)[/font][/quote]The 1/0 generates the null you need, and the Str function allows it to be accepted by a character type field.

           

          Finally, hide the PreCustomerName field and reposition the CustomerName field within the table as necessary.

           

          HTH,

          Kruncher

          • Monarch v7 Export to Access and NULL Values
            Derek G.

            Great Advice, worked exactly as stated. Somewhat of a pain with many text fields, but its better than having to edit all my queries / vb code.

             

            Does v9 have to ability to do this without the calculated fields?

             

            Appreciate the help!

             

            Derek

            • Monarch v7 Export to Access and NULL Values
              Data Kruncher

              No, V9 won't do it without the calculated fields either.

               

              There is one nice ability that may have been introduced in V8 (not sure). You can duplicate existing calculated fields easily and then just revise the field names as necessary. Better than starting from scratch. Especially when, as you say, you have quite a few fields which require this approach.

               

              Glad to hear that it helped. Thanks for the update.