12 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    Variable Column Lengths

    Simon _

      I am pulling reports from a SAP R3 system and the column lengths in each field tend to vary from day to day. Each column is seperated by a pipe (|). In the past this wasn't a problem because I could lock the column length in R3 prior to exporting.  Now with a new "upgrade" to the system I have lost that capability.

       

      I am currently using an old version of Monarch 4.05.  Each time the column lengths shift my field names no longer line up with my model and I find myelf having to re-do the model.

       

      I understand that v.7 offers floating field and variable field options -  does anyone know if this will this solve the problem for me?  If not, any other way to fix this?

       

      Thanks very much!

        • Variable Column Lengths
          Steve Caiels

          Hi Simon,

           

          The pipe character is used as a "numeric or" trap in Monarch so can't be used as a floating trap.

           

          If you can change the pipe to another character, then you'll be able to use the floating trap and it will solve the problem you describe.

           

          Does the file just contain line after line of info delimited by pipes?  If so, then the professional version of Monarch can do the job.  It will allow you to open the file as a delimited text file using the pipe as a seperator.

           

          Cheers

          Steve

          • Variable Column Lengths
            Simon _

            Thanks Steve! 

             

            My data has a few header lines that I ignore and then yes, essentially it is line after line of data that is separated by pipes.  If the pro version will recognize those as delimiters I think will solve the problem for me.

             

            My traps aren't as much a problem as the fields.  I can trap on numeric values within fields as long as the columns lengths don't shift too much  smile.gif[/img]

             

            Thanks again!

            • Variable Column Lengths
              Steve Caiels

              Hi Simon,

               

              If your text file has spurious lines at the beginning, you’ll need to strip them out prior to opening the database.  This can be done using a utility called PREP that comes with Monarch.  You can either do this manually or as a command line in a batch file.

               

              Spurious lines embedded within the file shouldn’t cause a problem as they can be filtered out in the table.

               

              Cheers

              Steve

              • Variable Column Lengths
                Mike Urbonas

                From a Monarch Tip article in the February 2003 issue of The Monarch Report:

                 

                "The key to best working with such output is to not open the file not as a report and build a model, but instead open the file as a “database” using Monarch V6 Pro."

                 

                Monarch V7 Pro will directly read your pipe-delimited SAP file as a database with no need to build a model.  You simply indicate in Monarch Pro you wish to "Open Database..." instead of "Open Report..." and specify the pipe "|" character is your delimiter.

                 

                Here is the link to the Feb 2003 issue -- see pages 6-7.  Monarch V7 Pro has been out since May 2003 and is definitely worth buying!

                 

                [url="http://www.datawatch.com/pdf/products/monarch/Feb03_Report_Final.pdf"]http://www.datawatch.com/pdf/products/monarch/Feb03_Report_Final.pdf[/url]

                 

                Regards,

                Mike

                • Variable Column Lengths
                  Mike Urbonas

                  Simon,

                   

                  Another quick SAP question for you: do you ever just run any existing SAP reports themselves, simply running to a print file and not to a printer?  In other words, is your particular SAP implementation limited to pipe-delimited output?

                   

                  Please let me know - thanks,

                  Mike

                  • Variable Column Lengths
                    Simon _

                    Hi Mike - thanks for the info.  Good question:) - I'm not really sure.  I'm picking up the development pieces from somebody else that left the company, so to my knowledge the best way for us to extract data from SAP was to list and export rather than capture a print job.  The later may not have even been tried since they were able to extract data with the List menu function.

                     

                    Thanks again,

                    Simon

                    • Variable Column Lengths
                      Simon _

                      Hello again,

                       

                      Ok so now I have v7.01 and I am trying to import data from my R3 reports and keep getting the following error;

                       

                      An error was encountered while reading the sample data.  The value at row , column could not be read.  This and subsequent bad values will be treated as nulls.[/i]

                       

                      I have tried saving the file as many different types, eg. .csv, .tab. .txt .xls and used many different delimiters including the '|" and still get the same error.

                       

                      Any help with this one would be much appreciated  smile.gif[/img]

                      • Variable Column Lengths
                        Bruce _

                        Not sure if this will help, but are you reading as ASCII or ANSI? SAP might be spitting out charteres 129-256 that are not being read correctly.

                         

                        Also prep.exe (included on the monarch CD) might help stripping out these characters.

                         

                        Bruce

                        • Variable Column Lengths
                          Simon _

                          Thanks Bruce - maybe but I have even imported the text file into MS Excel and saved it with an .xls etension.  I still get the same error - the funny thing is that the error is always the same but seems to get triggered by different cells depending on how the file is formatted.  It really bugs me because this is the biggest reason that I purchased version 7 pro.

                           

                          Thanks for you help though!

                          • Variable Column Lengths
                            Grant Perkins

                            Simon,

                             

                            I have seen this error but so far, in my usage, it has been a reasonable result of data field incompatibility such as an empty 'value' or a character value in what otherwise looks like a numeric field. If I remember correctly the import feature parses a number of lines at the beginning of the file to be imported and decides what type of field the column should be based on the results comes up with.

                             

                            If the data in that column on some lines is genuinely incorrect you can just ignore the message.

                             

                            On the other hand if Monarch is assessing a numeric column where alpha would be consistently more appropriate, once you have run the initial import, modify the field definition accordingly and save it. If you then refresh the data from the source the alpha values will be populated.

                             

                            If you then save the model you can apply you definition each time you open a new R/3 import by applying the previous definition.

                             

                            You may also find that the field sizes produced by the first upload are not large enough for all data inputs - for example your sample input has descriptive fields with a maximum of 22 characters but the source system may allow 40 characters and later extracts may require a larger size for completeness. Once again you should redefine the field and save the model then re-applying it during future imports of the same source data will give you control over the format.

                             

                            Hopefully this will explain what you are seeing but, if not, tell us what you are getting and we can look at it again and seek a different solution.

                             

                            Best regards,

                             

                            Grant

                             

                             

                            Originally posted by Simon:

                            Thanks Bruce - maybe but I have even imported the text file into MS Excel and saved it with an .xls etension.  I still get the same error - the funny thing is that the error is always the same but seems to get triggered by different cells depending on how the file is formatted.  It really bugs me because this is the biggest reason that I purchased version 7 pro.

                             

                            Thanks for you help though! [/b][/quote]

                            • Variable Column Lengths
                              Laura Afamado

                              Hello, I have the same problem here. I have a file in which each column is separated by a (|), but the column length in each field vary from day to day.

                               

                              I need to process this with a batch file, now I have a .bat that does this:

                              %PREP% "%OUTPUTS%fpmen.log"  "%OUTPUTS%fpmen.prn" AD "|" N

                               

                              "%MONARCH%" "%OUTPUTS%fpmen.prn" "%MODEL%fpdiario.mod" "%OUTPUTS%fpmensual.mdb" /T

                               

                              This works fine for the file I take for doing the model, but not for the rest of the files all day.

                              I´m working with Monarch Pro 7, I read here that "allow you to open the file as a delimited text file using the pipe as a seperator." I couldn´t find that option. Can somebody tell me how to do this?

                               

                              Thanks

                              Laura

                              • Variable Column Lengths
                                Grant Perkins

                                Hi Laura,

                                 

                                Use the "File> Open Database" option.

                                 

                                Navigate to your input file and select it.

                                 

                                You can apply a model but I will assume that you don't have one yet.

                                 

                                On the NEXT screen the data will probably look bad. Set the "Delimited Text Import - Delimiter Character" to "Other" ans enter the pipe character "|" in the box provided. (Check the other setting on that screen as well to make sure they match your input file).

                                 

                                Go ahead and load the data into a Monarch table.

                                 

                                Now you can save the cureent appearance as a model. You can apply the model the next time you open the file or one like it.

                                 

                                However, as you say, the columnd width's may be different. If you know the maximum possible wodths for the input data (field sizes from the originating system specification) you can adjust the model to fit then save it again. That should deal with all (known!) variations of the report.

                                 

                                Even better - if you have a consistent report name, you can save the whole thing as a project and simply run the project each time there is a new report.

                                 

                                Your batch file then does not need the PREP line.

                                 

                                You can run the batch for the PROJECT  (report and model in one command). You may also get some benefit from the PROJECT EXPORT features, although your script seems to suggest that your output requirement is quite straightforward.

                                 

                                The HELP files are quite useful for examples on these features but if there is anything that does not become clear just post back for additional input from the group or contact me via a private message.

                                 

                                I hope this helps.

                                 

                                Grant

                                 

                                 

                                Originally posted by lafamado:

                                Hello, I have the same problem here. I have a file in which each column is separated by a (|), but the column length in each field vary from day to day.

                                 

                                I need to process this with a batch file, now I have a .bat that does this:

                                %PREP% "%OUTPUTS%fpmen.log"  "%OUTPUTS%fpmen.prn" AD "|" N

                                 

                                "%MONARCH%" "%OUTPUTS%fpmen.prn" "%MODEL%fpdiario.mod" "%OUTPUTS%fpmensual.mdb" /T

                                 

                                This works fine for the file I take for doing the model, but not for the rest of the files all day.

                                I´m working with Monarch Pro 7, I read here that "allow you to open the file as a delimited text file using the pipe as a seperator." I couldn´t find that option. Can somebody tell me how to do this?

                                 

                                Thanks

                                Laura /b[/quote]