5 Replies Latest reply: May 15, 2014 9:57 AM by RalphB _ RSS

    Fill Date column

    RalphB _

      I am importing an excel spreadsheet that is a deposit log that has a date that only appears once on the first line of a batch.  Is there a way to have Monarch fill the rest of the column with the date like you can when working with a text file?

        • Fill Date column
          Nigel Winton

          Ralph

          Can you not use the 'Fill Empty Cells with value from previous record' button on the Field Properties. If not you could put the date field in as a page header.

           

          Regards

           

          Nigel

          • Fill Date column
            Grant Perkins

            Ralph,

             

            Is this Excel worksheet in some form of 'report' format? So the problem field would be like a section header if it were printed out - as Nigel has indicated?

             

            Or is it that the date field has a column to itself but the data imported (?) to Excel only populates the first occurrence of the cell?

             

            If the former I think about all you can do is is either generate an alternate worksheet within Excel (if that is possible, I'm no Excel expert) and then use the fully populated alternate OR print/export the entire table to a report like file and then use Monarch on that, just as a standard report. I guess you might be able to read inot Monarch and export from there as well, but sometimes that can get messy.

             

            The issue is that Monarch has to treat an Excel sheet as a database with with each cell being treated as if it contains discrete data, blank included. So the 'append' concept does not apply there is no way to trap cells for different purposes.

             

            If the date is really in a column where the duplicates have been suppressed (has this come from a Monarch output?      ) it may be best to look for a solution to that need within Excel.

             

            Sorry I don't have more to offer.

             

            Grant

            • Fill Date column
              RalphB _

              Nigel, Grant

               

              Thanks for your replies.  As this is a spreadsheet that I am importing into Monarch, Nigel's responses won't work.  Grant offered some unique solutions but like he said could get quite messy.

               

              The spreadsheet file name will contain the date that I need and I can work with that with not much problem

               

              I was hoping there was something I was missing in Monarch that I could "fill empty cells" within a database that would work.  Maybe something for a future release?

               

              Thanks

              • Fill Date column
                Grant Perkins

                Originally posted by RalphB:

                I was hoping there was something I was missing in Monarch that I could "fill empty cells" within a database that would work.  Maybe something for a future release?

                 

                Thanks /b[/quote]Interesting idea Ralph and I was wondering, as I wrote my original reply, if such a facility would be OK or might be liable to easy compromising of the validity of the database in the spreadsheet. I could not decide!

                 

                However your response gave me another idea. You could have a calculated field that performs a lookup (for every record) from an external 'file' which contains the date. I think an external lookup would be easier than an internal lookup. Or the value could be entered by the operator at run time if this is a manual process.

                 

                To populate the external 'file' with the required date would either be a manual task, a prior extract using Monarch perhaps (and so batchable) or a bit of code to creat something from the file name prior to running the Monarch session.

                 

                Or maybe some other options comes to mind? You would just need to be sure that the process could not lookup from an old version of the 'file'.

                 

                In fact you may be able to do the whole thing in the original Excel Workbook using a new sheet (which becomes the lookup value) or named range perhaps. So your extra sheet carries just the date value from the main sheet and the lookup points to that value.

                 

                What do you think?

                 

                 

                Grant

                • Fill Date column
                  RalphB _

                  Thanks Grant. 

                   

                  Your response brought up some interesting solutions with lookups that might work and I think I'll file away for future reference.  For audit purposes, I do not want to add or modify the original spreadsheet in any way.

                   

                  I think the best solution for this is to use a calculated field to extract the date from the file name.  I already do that for some other excel exports that I do.  This is only one part of a 6 step process that we use here for balancing cash deposits on a daily basis for our clients.  I have everything set up in a vb script to pull the appropriate information from various sources and export out what is needed and compared when necessary based on dates and other criteria.

                   

                  Thanks again Grant.