6 Replies Latest reply: May 15, 2014 9:57 AM by KLR358 _ RSS

    Record Data in Columns Instead of Rows...  Need Advice

    KLR358 _

      Hi There,

       

      I'm a newbie at this and need some advice.  I have a file that contains the following (comma delimited):

       

      Day,Farm0,Farm1,Farm2,Farm3...

      0,28,16,16,23

      1,,16,22,19

      2,101,62,338,621

      3,153,99,452,899

       

      This is what people would call a crosstab.  What I want to do is take the data in the Farmx column and place it into a series of records for input into my database.  The number of farms is actually greater than 100, so there are a lot!  A row orientation looks straightforward, but the column orientation like the above looks like it might be more complicated.  I need it denormalized as my input routine requires it in the format below.

       

      The desired result would be:

      Farm,Day,Qty

      Farm0,0,28

      Farm0,2,101

      Farm0,3,153  

      Farm1,0,16

      Farm1,1,16

      and so on... 

       

      I could write some vba in Excel to transpose these to rows, but if Monarch can do this, then all the better.

       

      Thanks,

      Kevin

        • Record Data in Columns Instead of Rows...  Need Advice
          Grant Perkins

          Hi Kevin and welcome to the forum.

           

          I think we could be struggling here. Especially back at version 5 standard level - however nothing is impossible, just difficult to achieve sometimes.

           

          In later versions the Multi-Column Region concept was introduced and would sort of help if the input file was fixed width rather than delimited. (There were some utility programs available back in v5 for manipulating files to make them easier to work with, more recent releases have the Monarch Utility to do the same things. On  of the programs allowed the sort of change required here.)

           

          However in the Multi-Column Region in V9 does not expect to be dealing with 100 columns in a single report. So it would work but would require 3 (iirc) models, all the same except for the positioning of the columns, to extract the data.

           

          All three extraction could be gathered into an exported file and then analyzed with another monarch model to deliver the result you need.

           

          As I have V9 and I don't program that would be my approach. However unless anyone here can offer an alternative from their V5 days that will work for you I suspect you may decide to write the vba code ...

           

          If I have any other thoughts I will pass them on.

           

          Grant

          • Record Data in Columns Instead of Rows...  Need Advice
            KLR358 _

            Cheers, Grant.  I can crank out the VBA code if no one comes up with something.  I want to pressure-test Monarch (the old version isn't ideal but that is all I have to work with for now).  If I can get this close and have some good bboard feedback solution I'll propose an upgrade to the decision makers.

             

            I also noticed that I will likely run into a character limit problem too.  Do you know if v5 has a utility to allow a split of records if they exceed the maximum length?  I'm still waiting on the v5 CD to get here.

             

            Kevin

            • Record Data in Columns Instead of Rows...  Need Advice
              Grant Perkins

              Originally posted by KLR358:

                 Do you know if v5 has a utility to allow a split of records if they exceed the maximum length?   /b[/quote]Have a look [url="http://www.datawatch.com/support/downloads.asp"]here[/url] for downloads.

               

              The Utility EXE files can be very useful. I suspect the PREP.EXE is what you may need. It's DOS so run as a command. I can't remember if the V5 Help has anything about it. MSRP.EXE can also be very useful (both of these functionally are not included in the Monarch Utility from V8 onwards.)

               

              I was trying to think of a way the MSRP could be used to change the format of your file but while it could change things I don't think it would be an easy task.

               

              I have also found EXAMINE.EXE especially useful from time to time.

               

               

              HTH.

               

               

              Grant

              • Record Data in Columns Instead of Rows...  Need Advice
                KLR358 _

                I'll take a look at these utilities, but from first glance I may be better denormalizing from VBA code.  This will overcome the lack of the multi-column region and line length limitations in the version I'll be using.

                 

                I can write less than 100 lines of VBA code to put the report in a format natively usable for V5.  It would just be ideal if I could avoid VBA altogether.

                 

                Question.  Since I have the Standard version, should I write the output file as a tab-delimited text?  Is that the easiest file format for the Standard version to parse and process?

                 

                I'll let you know how I get on... Thanks again for your very useful feedback.

                 

                Have a great evening.

                 

                Kevin

                • Record Data in Columns Instead of Rows...  Need Advice
                  Grant Perkins

                  Originally posted by KLR358:

                   

                  Question.  Since I have the Standard version, should I write the output file as a tab-delimited text?  Is that the easiest file format for the Standard version to parse and process?

                  /b[/quote]Kevin, The Pro version of Monarch, introduced as an optional purchase from V5 iirc, does allow direct reading of delimited files as a database, though this would not help as far as I can see with your existing report.

                   

                  If you modify it with a vba routine the best bet would be to make it fixed width output. Whether a TAB delimited version would do that may not be certain -  I guess it would depend on how you set the tab separation parameters in relation to the potential size of the data field fills.

                   

                  Even running Monarch Pro and using the database read facility I tend to introduce a template line for the maximum field sizes possible in the input file to ensure that my model will deal with any future incoming 'report' should it contain field fills larger than existed in my development sample.

                   

                  When assessing what Monarch may offer you company don't forget that beyond the data extraction and manipulation advances available in later versions there are entire areas of functionality that simply to not appear in any form in V5 (Standard or Pro versions).

                   

                  Also some thanks - your requirement has suggested an enhancement idea for a future version of Monarch that I will add to the wish list topic.

                   

                  HTH.

                   

                  Grant

                  • Record Data in Columns Instead of Rows...  Need Advice
                    KLR358 _

                    Update as promised...

                     

                    Since I had no experience with Monarch, I decided to work with one of my worksheets where the data was already row-oriented.  I figured a real-world problem we face was the best way to get up to speed.

                     

                    I saved the data file for input as a comma separated (csv) file from Excel.  This file opened OK in Monarch, and I was able to manually define all the fields (approx. 15) and also create another 15 calculated.  The multi-line detail capability was very nice and intuitive.  The file was nearly 30,000 records and Monarch seemed to move very quickly through.

                     

                    I then exported to a delimited file, and used the msrp utility to remove quotes and change commas to the pipe symbol (my import utility recognizes these as delimiters).

                     

                    Even being 7 years old, this program has already saved a lot of programming effort.  The only code I'll have to write is to denormalize the data I described in an earlier post.

                     

                    In summary, this program will make a great data-reduction tool for performance data we collect on our farms.  Out of this data set I'll have performance data from approx. 1700 different flocks totaling 58,000 records.

                     

                    I'll be looking forward to licensing version 9 soon.

                     

                    Question.  Can you specify the delimiter when outputting a file?  If not, this would be a great enhancement.  I do know the pipe symbol is used in special situations in the program, but I would hope that in the situation where files are exported, it could be used.  The ability to avoid the use of quotes when exporting would be great.  Both of these features would eliminate the need for an external utility like msrp.

                     

                    If version 9 Pro handles these things then all the more reason to upgrade.

                     

                    Grant, thanks for the pointers.  Now, on to the denormalizing process in VBA...

                     

                    Kevin