6 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    Demilited text problem

    tsutter _

      Hello,

       

      I am trying to use Monarch v8 Pro to export a Tab demilited file.  The problem is when I export to CSV format, it removes the extra spaces from fields.  When I export to .txt, it keeps the full width of each record, but doesn't places tabs between fields.

       

      I've thought of a few ways to work around this within Monarch, but I was hoping there was an easier way.

       

      I placed a 1 char calculated field between each data field with a tab character.  Another way is to build a large nested IF statment to pad " " based off field length minus data length.  These methods work on smaller projects, but takee a lot of extra time with the multiple models and large number of fields I have.  Also, I believe there is a maximum column number I am approaching by adding a tab field between each record.

       

      Maybe I am missing something easier.

       

      Any help is appreciated.

       

      Thanks,

      Tim

        • Demilited text problem
          Data Kruncher

          You might get lucky with this one Tim, though I don't have v8 handy at the moment, so I'm not sure.

           

          Nonetheless, go to your Options menu. Export and Clipboard. On the Text files tab, in the section for Delimited Text files, select Tab, and OK the dialog.

           

          Try again.

          • Demilited text problem
            tsutter _

            I have turned on and off almost every option in Export and Clipboard.  Checking the TAB seperator box just changes what is used in the CSV file.  (Instead of the default comma).

             

            However, this points back to my problem.  When using the Tab delimiter, you have to export to a .CSV file for it to work.  For some reason, monarch trims each field down to the data when using .CSV, leaving almost all of fields short of the required data length.

             

            When exporting to a .txt file, it doesn't matter what option you have checked.  It will keep the field lengths including spaces, but will not include the delimiter(even when chosing delimited text in the save as option).  Then records have no seperator, and no way to tell when they end/start

              • Demilited text problem
                Grant Perkins

                Tim,

                 

                In theory a delimited file should not need to specify the field widths - they should be known to the target database. Otherwise fixed width should be used.

                 

                Now, that said, it depends on what you are doing and if that involves creating a database using the newly exported de-limited file rather than producing a file to be read into an existing database.

                 

                If you need to ensure that a newly generated database file has fields of a certain width you need to one approach is to include a dummy line in the file. You could do this by adding a line at the TOP of the original report and getting Monarch to read and use it for fields sizes.

                 

                Or you could just edit or concatenate the line to the start of the Monarch exported file before loading it into the database target file.

                 

                Do also ensure that the field types being exported are the same as the target database.

                 

                Since I'm not sure yet which approach you are trying I will just float these points for now and follow up on your response to them (hopefully).

                 

                 

                HTH.

                 

                 

                Grant

                  • Demilited text problem
                    tsutter _

                    Grant,

                     

                    You are correct in that a demilited file should not need field widths, since the delimiter seperates fields.

                     

                    However, the company I am creating this layout for basically wants a delimited fixed length data file.  I have no idea how thier database works, or how they import this file.  I'm guessing it will be imported into an existing database and applying updates.

                     

                    So, for now I just exported my file as fixed length with calculated fields containing a tab character between each data field.  It took some extra time, but I doubt any other companies will request this type of layout.

                     

                    I was just hoping for an easy way to do this using Monarch.

                      • Demilited text problem
                        Nick Osdale-Popa

                        Might be a bit of a hassle, but could you make another calculated field that combines all the fields into the format you want? Then hide all the other fields and just output that one field to a text file.

                         

                        Just a thought.

                          • Demilited text problem
                            Grant Perkins

                            Tim,

                             

                            If it were my project my curiosity would force me to make some gentle enquiries about how they were going to use the file since 'there might be some better and more efficient output to be derived from the knowledge'. Or words to that effect.

                             

                            At least you might discover whether they fully appreciate what they are asking for.

                             

                            IF[/B] they want the file in a certain format to link to another file, as in a lookup for example or simply need to append it to an existing file using some demanding protocol, you may still need to know the size of every field and include that in a dummy line that forces specific field sizes into the output even if the data being analysed and presented would not present in that size.

                             

                            If this process is to be run more than once the dummy line is a safe way to ensure the output format is always consistent, no matter what the data content looks like.

                             

                            Just my thoughts.

                             

                             

                            Grant