11 Replies Latest reply: May 15, 2014 9:51 AM by Dirk Schulze RSS

    Input is a tab delimited file

    Nancy _

      I'm having trouble reading a tab delimited file that comes in like an excel spreadsheet.  Looking at the report, everything lines up column wise, but when I start trying to set it up in Monarch, things aren't what they seem.  Ex. A name field that has 19 chars lines up with everything else, but if the name field has 21 chars, then this row has one less tab in it.  Has anyone ever run across this before?  How did you get around it?  Is there an archive message that has already documented this (I couldn't find anything).  Thanks.

        • Input is a tab delimited file
          Nick Osdale-Popa

          Which version of Monarch? Floating traps introduced in ver6 should take care of your problem nicely.

           

          If you have a lesser version, you could trap the whole line as a single field, then using LSplits() and RSplits(), you should be able to use the tab character as your delimiter in the functions.

           

          [size="1"][ March 30, 2004, 10:21 AM: Message edited by: Nick Osdale-Popa ][/size]

          • Input is a tab delimited file
            Grant Perkins

            Nancy,

             

            Do I understand correctly that you are importing the data as a database link? And that you have a problem that the apparently missing TAB(s) mean the columns either combine or shift relative to each other?

             

            If so that seems odd and suggests a problem with the original file creation. Where does you file originate? The whole point about a tab delimited file is that the columns can be of a variable width (as with any other delimited files.)

             

            Do you have any option to get the file in a different format (say CSV or fixed width) for a comparison?

             

            Grant

             

            Originally posted by nancy:

            I'm having trouble reading a tab delimited file that comes in like an excel spreadsheet.  Looking at the report, everything lines up column wise, but when I start trying to set it up in Monarch, things aren't what they seem.  Ex. A name field that has 19 chars lines up with everything else, but if the name field has 21 chars, then this row has one less tab in it.  Has anyone ever run across this before?  How did you get around it?  Is there an archive message that has already documented this (I couldn't find anything).  Thanks. /b[/quote]

            • Input is a tab delimited file
              Nancy _

              First, Thank you for your help.  I am a newbie.  And I'll admit that I've only read just the basics on how Monarch works.  This is the first Monarch task I've done.

              We are running v5 - no floating traps.

              This isn't the Pro version, so I can't 'Open Database...'.

              I've tried using the Monarch Prep tool, but it splits report lines at 256 chars and that screws up some numbers that I need.

              The input file is a tab delimited file that has a .xls suffix.  I can open it in Excel and save it as a .csv file.  When I open it in Excel, all the data lines up properly in the columns.  When I open it in Monarch, it shifts the data depending on the length of a data field near the beginning of a report line.  The number of tabs (or commas) is the same in all lines.  Does that make sense?

              The report is coming from a customer, who is unwilling to change anything on the report (in fact, that's why we are trying to get the info off the report in the first place).

              • Input is a tab delimited file
                Bruce _

                Nancy

                 

                Do you have any commas in the data itself perhaps? Are there any ascii charcters 1-32 in the file "gumming" up the works.

                 

                Maybe if you could post an edited snippet of the file, the guys could help out.

                 

                Thanks

                • Input is a tab delimited file
                  Grant Perkins

                  Nancy,

                   

                  OK, I understand the problem - apologies for guessing you were using the Pro version.

                   

                  An upgrade to a Pro version would make life easy for you but an alternative might be to import to Excel and then save it as a fixed width output with the columns set to a suitable maximum size.

                   

                  That way you should end up with a columnar report that Monarch can easily read as if it were a 'normal' report.

                   

                  If the rows prove to be too long you could use prep to split the lines at a suitable point and deal with each record as a a multi row entity.

                   

                  If those ideas don't work for you Bruce's suggestion of posting an example of the file format sounds like a good idea if it is acceptable to you. It would give us something to play with.

                   

                  Grant

                   

                  Originally posted by nancy:

                  First, Thank you for your help.  I am a newbie.  And I'll admit that I've only read just the basics on how Monarch works.  This is the first Monarch task I've done.

                  We are running v5 - no floating traps.

                  This isn't the Pro version, so I can't 'Open Database...'.

                  I've tried using the Monarch Prep tool, but it splits report lines at 256 chars and that screws up some numbers that I need.

                  The input file is a tab delimited file that has a .xls suffix.  I can open it in Excel and save it as a .csv file.  When I open it in Excel, all the data lines up properly in the columns.  When I open it in Monarch, it shifts the data depending on the length of a data field near the beginning of a report line.  The number of tabs (or commas) is the same in all lines.  Does that make sense?

                  The report is coming from a customer, who is unwilling to change anything on the report (in fact, that's why we are trying to get the info off the report in the first place). /b[/quote]

                  • Input is a tab delimited file
                    Nancy _

                    Well, I've tried saving the file as all sorts of different formats, and can't seem to find one that it will read consistently.  Saving as a space delimited file kept the columns, but it splits the records at 240 chars (which wouldn't be bad except that it puts the split part at the end of the file). 

                    I think I've convinced my manager to upgrade from V5 STD to V7 PRO.  I'm hoping that it can read the file as a .csv and life will be good.

                    Thank you all for your suggestions.  This is one of the nicest forums I've ever visited.

                    Nancy

                    • Input is a tab delimited file
                      Nancy _

                      Bruce - thank you for the suggestions.  Yes, there are a few extra commas in the input, but that shouldn't matter with a tab delimited file.  When I saved it as a comma delimited file, it did mess it up a little bit more till I edited them out in the xls file.

                      Thanks for the offer to look at the file.  I'm sure everyone has better things to do than that.  We'll give the v7 pro a try first.  That will probably do the trick.  Thanks.

                      • Input is a tab delimited file
                        Bruce _

                        Nancy

                         

                        I went from ver 5 pro at my last job, and now get the ver 7 pro here & it is worth the upgrade money.

                         

                        The biggest thing for me is exporting large projects at night.

                         

                        Good  luck

                        • Input is a tab delimited file
                          Bruce _

                          Nancy

                           

                          I just noticed that you said that you use a tab seperated file, but that it is encoded as a .csv?

                           

                          If Excel or Monarch sees csv, it assumes it is seperated by commas, despite what format is. Save under tsv ot txt instead.

                           

                          Let me know if this helps.

                            smile.gif[/img]

                          • Input is a tab delimited file
                            Nancy _

                            The tab delimited file had a .xls extension.

                            The comma delimited file had a .csv extension.

                            By the way, we did get v7 pro (actually a user got the license, so I'm out of the loop already), but I asked her how it was going, and she is amazed at how easy it is to manipulate data out of a report.  She is thrilled with this product.

                            Thanks again.

                             

                            Originally posted by Bruce:

                            Nancy

                             

                            I just noticed that you said that you use a tab seperated file, but that it is encoded as a .csv?

                             

                            If Excel or Monarch sees csv, it assumes it is seperated by commas, despite what format is. Save under tsv ot txt instead.

                             

                            Let me know if this helps.

                                smile.gif[/img]   /b[/quote]

                             

                            [size="1"][ April 09, 2004, 01:03 PM: Message edited by: Nancy ][/size]

                            • Input is a tab delimited file
                              Dirk Schulze

                              Nancy -

                               

                              I've had to deal with a file with practically identical characteristics using v6 Pro.  I had to resort to correcting it's format in Textpad which was a tedious process.  Has your v7 user had success in mining data from your report?