3 Replies Latest reply: May 15, 2014 9:55 AM by Steve Caiels RSS

    text file problem

    Churchill _

      I am working with reports that are created by an interally-developed application that will only export reports to Excel.  I have been opening the reports in Excel and using "Save As" to convert the files to tab-delimited text files which I can then work with in Monarch.  The problem is that once the report is saved as a text file quotation marks randomly appear around certain fields. I can't work around it with the model because the quotes can appear in any field but only in certain lines. This is easy to fix if it is a numeric field by reformatting in Excel to Numbers format but when the field is, for example, an employee's name, the only way to get rid of the quotes is to do a Find & Replace in Notepad, which is a pain. How do I convert my spreadsheets to text files without the quotes?  This is Excel 2000 and Monarch 7.01.

        • text file problem
          Tom Whiteside

          There have been several posts on this same issue of useless quotation marks showing up in Excel-to-text translations - - however, since Monarch 7 can read Excel files directly, as File - Open Database, is there some reason that you specifically need to work with text files?  If your application sends all output to Excel, you can use Monarch to open the Excel output and then build and use your models directly.  No need to change back to text files.

          • text file problem
            Grant Perkins

            Welcome Churchill!

             

            Do you have Monarch 7.01 Standard or Pro? If Pro I fully support Tom's view, just read the field directly unless there is something very specific you need to do that treating Excel as a database does not permit.

             

            Quote marks are used to surround any puntuation embedded in the text in the field. If you save as a .prn file ("Formatted Text - Space delimited". Make sure you have all the excel columns set to the maximum  width required - wrapping text in a cell will mean truncation) you will have a fixed format which should then be perfect for Monarch use. No quote marks unless they are in the original text.

             

            Alternatively you could use the MSRP utility to strip the quote marks from the file. That should be quite easy so long as the data do not contain any valid quote marks in the text parts!

             

            There are other options but avoidance would be better than fixing the extracted fields.

             

            I hope this helps in some way.

             

            Good luck.

             

            Grant

             

            Edit - a typo that obscured meaning a little.

             

              Originally posted by Churchill:

            I am working with reports that are created by an interally-developed application that will only export reports to Excel.  I have been opening the reports in Excel and using "Save As" to convert the files to tab-delimited text files which I can then work with in Monarch.  The problem is that once the report is saved as a text file quotation marks randomly appear around certain fields. I can't work around it with the model because the quotes can appear in any field but only in certain lines. This is easy to fix if it is a numeric field by reformatting in Excel to Numbers format but when the field is, for example, an employee's name, the only way to get rid of the quotes is to do a Find & Replace in Notepad, which is a pain. How do I convert my spreadsheets to text files without the quotes?  This is Excel 2000 and Monarch 7.01. /b[/quote]

             

            [size="1"][ November 14, 2003, 11:23 AM: Message edited by: Grant Perkins ][/size]

            • text file problem
              Steve Caiels

              Hi,

               

              Another vote for using Open Database or the Formatted Text (Space Delimited) option from Excel.  If that still gives you the "" problem try solving it with a calculated field using

              [font="courier"]strip(field,chr(34))[/font][/quote]Where field is your original field name.

              Cheers

              Steve

               

              [size="1"][ November 14, 2003, 09:16 AM: Message edited by: Steve Caiels ][/size]