5 Replies Latest reply: Apr 22, 2013 12:03 PM by Hugo Sheng RSS

    Function error in datetime

      I am trying to parse a fixed width file using a transform operator rule in a dataflow.  See function below:


      function transform(input)
         output = {}
           output.CMSClientID = tointeger(string.substring(line, 4, 7))
           output.CMSClientAccount = tointeger(string.substring(line, 8, 15))
           output.CMSClientLocation = string.substring(line, 16, 21)
           output.ClaimNumber = string.substring(line, 22, 39)
           output.ClaimVendorID = string.substring(line, 40, 42)
           output.ProcessingOffice = tointeger(string.substring(line, 43, 45))
           output.RecordSequence = tointeger(string.substring(line, 46, 47))
           output.Examiner = string.substring(line, 48, 55)
           output.UserEnteringNote = string.substring(line, 56, 63)
           output.DateEntered = string.datetime(string.substring(line, 64, 71), "CCYYMMDD")
           output.TimeEntered = string.datetime(string.substring(line, 72, 77), "HHMISS")
           output.NoteType = string.substring(line, 78, 79)
           output.NoteText = string.substring(line, 80, 1279)
           output.NoteInternalUniqueID = string.substring(line, 1280, 1295) 
         return output;


      When I run the dataflow using this operator, I get an error.  The error output is large so I will paste the relevant portion below:


      NOT - RULE_DESCRIPTOR-1001-F: Rule 'Rule Rule_1' failed while executing the 'transform' function: 
       Rule Rule_1:14: bad argument #2 to 'datetime' (CCYYMMDD)
      stack traceback:
          [C]: in function 'datetime'
          Rule Rule_1:14: in function <Rule Rule_1:1> (FlatFileTransform_RecordTypeExport.Step_1)


      I have nine total rules used to parse different record types from the same file as seen below:



      Each transform operator has an error in the result window that references the first datetime field in it's respective rule with the same "bad argument" error.


      I can't figure out what I'm doing wrong.  I found this document that appears to reference doing exactly what I am doing, with the exception of the datetime format argument I am using.


      Any help would be greatly appreciated!

        • Re: Function error in datetime
          Juan Gerardo Cabeza Luque

          Hi Miguel Ángel,

               I think you have a problem with the argument naming in the output table you have to define in the transform operator. I suggest you use the following function I guess it should work. But please contact me and send me the dataflow and I will check in detail:


          function transform(input)



             output = {

               CMSClientID = tointeger(string.substring(input.line, 4, 7)),

               CMSClientAccount = tointeger(string.substring(input.line, 8, 15)),

               CMSClientLocation = string.substring(input.line, 16, 21),

               ClaimNumber = string.substring(input.line, 22, 39),

               ClaimVendorID = string.substring(input.line, 40, 42),

               ProcessingOffice = tointeger(string.substring(input.line, 43, 45)),

               RecordSequence = tointeger(string.substring(input.line, 46, 47)),

               Examiner = string.substring(input.line, 48, 55),

               UserEnteringNote = string.substring(input.line, 56, 63),

               DateEntered = string.datetime(string.substring(input.line, 64, 71), "CCYYMMDD"),

               TimeEntered = string.datetime(string.substring(input.line, 72, 77), "HHMISS"),

               NoteType = string.substring(input.line, 78, 79),

               NoteText = string.substring(input.line, 80, 1279),

               NoteInternalUniqueID = string.substring(input.line, 1280, 1295)


             return output;




          • Re: Function error in datetime

            It looks as if your function call is correct.


            Generally this error, when the function call is correct, is due to an attempt to process a value that can't be converted into a datetime.  Are you certain of the accuracy of the substring start and stop values?  Are you sure you are not attempting to parse/convert the header row?

              • Re: Function error in datetime

                Thanks for the reply John.


                I have verified that every record contains a date appropriate string in the position specified in the substring.  I also do not have a header in the file so that should not be providing a bad record.


                Aside from that, I am using a filter operator to channel the various rows to its respective transform operator by pulling the first 3 characters of the row.  Maybe this is where I am going wrong.  See screenshot below:


                4-18-2013 4-19-28 PM.jpg

                  • Re: Function error in datetime
                    Hugo Sheng

                    You may want to verify that the 2 prior columns, Examiner and UserEnteringNote, are getting the correct values and the byte offsets are correct.  It could be that you're off by a byte or so and that would cause a bad value to be passed to DateEntered.   


                    To export the project, just go to the file menu and export the project out as a zip file and email the zip file.  You'll need to send some sample data along as well.