4 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Summary Report to Excel Date Field

    Scott Johnson

      I have a field that I am trapping with the following format 10/05/02.

      Monarch seems to be recognizing this as a date and allows me to change the format in Monarch.

      In table view or summary view, I can highlight several of the records and paste them into an Excel spreadsheet and Excel recognizes the field as a date field.

      However when I use the same file and export the entire summary file using the export summary function in Monarch, Excel does not recognize the field as a date field but as text.

      Any suggestions as to why this is happening and how I can correct it will be appreciated.

        • Summary Report to Excel Date Field
          Grant Perkins

          I have seen this. Are you using Monarch 6? If so the Help info I have just looked up indicates that Monarch fields identified as Dates type are exported in 'serial date format' and suggests the spreadsheet cells need to have their format changed after the data has been imported.

           

          The 'cut and paste' option always seems to work differently to export/import. It's nice to have standards. If I can remember what I discovered when I last looked into it I will update later. I seem to remeber that it could work if you were importing to a spreadsheet template where the target cells were already defined as date type.

           

          Grant

           

          Originally posted by Scott Johnson:

          I have a field that I am trapping with the following format 10/05/02.

          Monarch seems to be recognizing this as a date and allows me to change the format in Monarch.

          In table view or summary view, I can highlight several of the records and paste them into an Excel spreadsheet and Excel recognizes the field as a date field.

          However when I use the same file and export the entire summary file using the export summary function in Monarch, Excel does not recognize the field as a date field but as text.

          Any suggestions as to why this is happening and how I can correct it will be appreciated.[/b][/quote]

          • Summary Report to Excel Date Field
            Grant Perkins

            This item from the Monarch FAQ page may be relevant to your question. I think it is what I discovered whe i looked into the problem I had.

             

            Grant

             

            When I export from the Summary Window, my numeric fields no longer hold a numeric data type.

             

            A: Key Fields are exported from the Summary Window as character data types due to the functionality of the "Unspecified Values" to be displayed as "All Others".

             

            Since the values found in the Key Fields have been consolidated from multiple records, the values shown in the Summary represent a label rather than a value. This is true for Date fields as well.

             

            Bring the Numeric fields into the Summary Definition as Summary fields or export from the Summary Window as a Fixed Length Text file, bring the new file into Monarch (build a Model to extract all fields) and export from the Table window.

             

             

             

            Originally posted by Grant Perkins:

            I have seen this. Are you using Monarch 6? If so the Help info I have just looked up indicates that Monarch fields identified as Dates type are exported in 'serial date format' and suggests the spreadsheet cells need to have their format changed after the data has been imported.

             

            The 'cut and paste' option always seems to work differently to export/import. It's nice to have standards. If I can remember what I discovered when I last looked into it I will update later. I seem to remeber that it could work if you were importing to a spreadsheet template where the target cells were already defined as date type.

             

            Grant

             

              /size[quote]quote:[/size]Originally posted by Scott Johnson:

            I have a field that I am trapping with the following format 10/05/02.

            Monarch seems to be recognizing this as a date and allows me to change the format in Monarch.

            In table view or summary view, I can highlight several of the records and paste them into an Excel spreadsheet and Excel recognizes the field as a date field.

            However when I use the same file and export the entire summary file using the export summary function in Monarch, Excel does not recognize the field as a date field but as text.

            Any suggestions as to why this is happening and how I can correct it will be appreciated.[/b][/quote][/b][/size][/QUOTE]

            • Summary Report to Excel Date Field
              Scott Johnson

              Ok thanks so I think I got it.  I need to feed the summary report back into monarch and then extract the information from the table instead of the summary.  Wish me luck.

              • Summary Report to Excel Date Field
                Grant Perkins

                Hi Scott,

                 

                Thought I would expand my suggestion a little. It's not a Monarch solution.

                 

                I think what I did (it's a while ago) was simply export from the summary as a text file, of whatever sort you prefer but fixed length fields may be more consistent for this, and then import that file directly into Excel (or Access, also possible but I think the target was Excel for this.)

                 

                If you then open Excel, get Excel to open the file just created and you can set the field types for the import.

                 

                I presume most other packages these days will have similar functions so it should be easy enough.

                 

                Which way you choose will be dependent on other factors - ease of use, etc. I assume you must use the summary route and cannot simply extract the data set you want from the table anyway?

                 

                One of the reasons I used this method quite frequently was simply that earlier version of Monarch could only export 16k lines to Excel (Version Compatibility). Now at 64k lines the situation is improved.

                 

                One of the benefits of the suggested method is that you can produce a summary output which is still too large for Excel export and filter it to reduce the number of records before you export it again.

                 

                I guess the whole process might be cleaner if all handled within a Monarch aware script. However unless I did it often or was preparing this for others to use, I would simply run 2 Monarch sessions on the PC and use the second session to process the exported summary and export that to Excel (or whatever).

                 

                As ever, there can be many ways of reaching the same result!

                 

                Grant