3 Replies Latest reply: May 15, 2014 10:15 AM by RalphB _ RSS

    Trim character and convert to date

    Andy Hewitt

      I have a process where I open a delimited txt file and create a table.  Each line contains values seperated by a "^" and terminated with a ";"

       

      BTC5153612982201303114523776831F110000003^^0000000020130321;

       

      The process works fine except for the last field which has date information.

       

      When I open the table the last field contains the date and the ";" like " 20130321; "

       

      I've tried several formulas but none allow me to format the resulting value as a date. 

       

      How do i remove the ";" and display the date in short date format?

       

      Thanks in advance.

        • Trim character and convert to date
          Grant Perkins

          Hi DFASANDY,

           

          My Initial reaction would be to suggest processing it (ad any other similar files that may follow it) to a more satisfactory format using the Monarch Utility option for preparing files.

           

          Is this some sort of EDI type file? Looks similar. Monarch U has features to deal with that and create, say, a columnar output.

           

          If you feel a need to workk with it as it is the Monarch should recognise the date so long as you remove the ";" (and end of line/row marker I would think). Try the STRIP() function to remove the ";" and RTRIM as well if that leaves you with and space at the end. Or simply use LEFT(,8). That should give you a field that I would expect to be recognisable for a Date field type. If for some reason is is not recognisable as a date type (YYYYMMDD) we may need to do a little more.

           

          HTH.

            • Trim character and convert to date
              Olly Bond

              Hello Andy,

               

              Once you have a clean string like "20130405", then the ctod() function will let you convert this to a date field.

               

              Best wishes,

               

              Olly

                • Trim character and convert to date
                  RalphB _

                  Hi Andy,

                   

                  Monarch has the capability of reading the yyyymmdd field as a date.  If you click on Options and then click on the Input button you can set your date format to y/m/d format.  I have some reports that have the date in that format and I have my model set to read the date field as that and it exports it out correctly.  Just be sure and set your field in the model as "Date" and you should be OK.

                   

                  Ralph