7 Replies Latest reply: May 15, 2014 9:57 AM by SSM _ RSS

    Null value displayed on import of Excell data

    SSM _

      Yes, I've read the other posts and I'm not having a problem with the field being assigned the correct field type.  I'm running version 5 pro.  When I import a particular spreadsheet that contains records it fills in the blank fields with the term "NULL," and I want them to remain empty.  When I try to export to say a comma delimited file Monarch inserts the NULL term in every field that should have been empty?  How do I get around this?

       

      Next - I noticed I couldn't use the Field Properties - Copy Field Values option to have Monarch "fill the empty cells with value from previos record."  Has this been fixed in a later version?

        • Null value displayed on import of Excell data
          Grant Perkins

          Originally posted by SSM:

          Yes, I've read the other posts and I'm not having a problem with the field being assigned the correct field type.  I'm running version 5 pro.  When I import a particular spreadsheet that contains records it fills in the blank fields with the term "NULL," and I want them to remain empty.  When I try to export to say a comma delimited file Monarch inserts the NULL term in every field that should have been empty?  How do I get around this?[/b][/quote]You can change the display (not the underlying field 'value' ) for data values from the Options > View Options screen. Top of the pop up screen and specify how you want null to be displayed. Blank it out and it will display as blank.

           

          Originally posted by SSM:

           

          Next - I noticed I couldn't use the Field Properties - Copy Field Values option to have Monarch "fill the empty cells with value from previos record."  Has this been fixed in a later version? /b[/quote]I assume you are still importing from a database rather than using a report when you see this. The feature is intended to allow the population of fields where the display of the logical input has been suppressed on a printed output in order to make the reports more readable or simply more cosmetically acceptable.

           

          Monarch allows the data to be recreated if required without having to resort to using an Append template (for example).

           

          When you are reading an external source file as a database the data in the individual fields or cells should be accepted as it is so filling the fields would seem to be inappropriate.

           

          However a simple way to overcome the apparent problem might be to use a lookup table (or external file) calculated field to populate the data as required (I assume that you are looking at something that would be acceptable and possible to treat that way) or to export the newly created table (or summary) fo a file and then re-read that into Monarch as a second phase activity. Being now a report you could map the field and fill from the previous record as you wish.

           

          I hope I have understood the requirement reasonably correctly. If not let me know where the solution does not work and I will have another look at it.

           

          Finally - if you have the opportunity to upgrade to version 8 I would certainly recommend it. You will see many extremely useful new features and functions which should make your modelling life easier.

           

          Grant

          • Null value displayed on import of Excell data
            SSM _

            Hi Grant - thanks for the great reply. I follow what you are saying, but I'm not importing from a database.  I'm actually importing a Cognos report that was dumped to Excel, and as you so noted logical input was suppressed in the Cognos report and it dumps it as displayed to Excel, a real pain.

             

            I don't think a lookup table will work in this situation unless I'm missing something?  I've tried dumping the data out of Excel into a text file that I could open under Monarch using the more traditional tools and that failed also.  Somehow the fields get chopped short in the text file dump out of Excel.  Also tried importing into Access and printing to a text file that way but that failed also.  Now that I think of it, there's probable a way in Access to make it read the previous record for missing data and fill it in...but that's beyond my knowledge to execute and I think some VBA work might be needed for that to work.

             

            I will try to export the newly created table to a file and then re-read that into Monarch as a second phase activity as you suggested...I hadn't thought of that.

             

            I'm working on upgrading to version 8 in a round about way.  Just received version 6 pro I bought off eBay today and plan to acquire the update for v6/v7 to v8 pro in the next few weeks.

             

            Best Regards - Steve

            • Null value displayed on import of Excell data
              Grant Perkins

              Hi Steve,

               

              Do I read it right that the Cognos report is only available to you view the Excel dump and the Excel worksheet is basically a replica of the report with all the text lines in single fields?

               

              By the way, in the Monarch context when I refer to reading from a database that would mean reading directly from Excel, Access, etc., or a delimited file as is possible from the Pro versions. The 'Standard' Monarch functionality would be some form of text based file templating. So I am using the Database term quite loosely!

               

              The Excel export does tend to be a bit unhelpful as I recall. (Not had to use it for a while.) If you set the column widths I think it works OK. Or if you have the imported data in something resembling almost useful information but the Excel reading interface is causing problems, you could consider exporting from Excel as a delimited file and then reading into Monarch from that.

               

              Still, if the original Cognos report was formatted for printing to a disk file it might be an even better source ...

               

              I hope this helps a little.

               

              Grant

              • Null value displayed on import of Excell data
                SSM _

                Grant -

                 

                I have now imported the file into Monarch and dumped it out as a comma delimited file.  I then reopened the new file under Monarch and set floating traps to extract the data.  Now I have two more problems...

                1.  Data fields that have commas in them as part of the data are picked up as floating traps, so for instance a field with the comment: "RVR DP/SHIP ACCRUAL, JAN-MARCH" gets seen as two separate data elements.

                2.  The normal data line looks as follows:

                200510,"07","051","000","SE-AR TRADE","AR",1,20050412,"AR CASH ENTRY #2225",-88.37   - so floating comma traps work well here.  But then it and traps nothing on the following:

                ,"","","115","","SE-SALARIES","GJ",263,200505504,"",12307.7

                 

                Any idea on how to tackle these issues.  I've upgraded to V6pro now so I was able to deploy floating traps. Hope to get upgraded to v8pro in a couple of weeks.

                • Null value displayed on import of Excell data
                  SSM _

                  I thnk I got it figured out.  Needed to trap the quotation marks also and eliminated a numeric trap in the first position on the trap line. - Steve

                  • Null value displayed on import of Excell data
                    Grant Perkins

                    Steve,

                     

                    If you have a CSV file have you tried opening it as a database? The commas between quote marks should import as part of single text fields.

                     

                    The pre-load assessment of the fields sometimes defines fields you may want to be numeric as character where blanks or nulls exist. There is greater flexibility to manage that in V8. A V6 solution is to accept what you get but use a calculated field to change replicate the imported field and change the text to numeric values, converting blanks and nulls to 0 (zero) as you do so.

                     

                    On the other hand this would not provide a solution to your replicate down a column problem.

                     

                    Plan G would be to use the Monarch PREP.EXE utility program to convert the .csv output to a fixed width output and then treat that as a report. My only concern would be the potential for exceeding the 1000 character max for the line width limit. Again this should be a problem gone when you get V8.

                     

                    You could try the floating trap to ne a comma immediately followed by "not blank", but I do think there are some complexities of trying to deal with floating traps that make their use for csv files with a significant number of component fields somewhat haphazard in their results. Basically you need to find the longest possible field in any position and map the trap for that even when that situation does not appear on any line. To put it another way, the only safe (ish) way to map the trap would be to apply the max field sizes for the original output fields at the gap size between trap characters. That gets a bit messy I find. I would rather import as a database.

                     

                    I hope this helps.

                     

                     

                    Grant

                    • Null value displayed on import of Excell data
                      SSM _

                      Problem Solved!!  I found the following on the board and it works a lot faster than using Monarch - it simply copies the formula to every blank cell that exists.

                       

                      "It's pretty simple to fill the empty spaces with data from the rows above, but it does come across as a bit of a fancy Excel trick.

                       

                      Supposing that your Branch No, Name, and Manager are in columns A, B, and C respectively, here are the steps:

                      • Select columns A, B, and C

                      • Type Ctrl-G or hit F5 to bring up the Go To dialog box

                      • Click the Special... button

                      • Click Blanks and click OK

                      • Type + UP ARROW

                      • Type Ctrl-Enter instead of just Enter to complete the formula

                      • Select columns A, B, and C once more

                      • Copy the selection and paste it back as values

                      fill the empty spaces with data from the rows above."