6 Replies Latest reply on Jul 23, 2021 9:27 AM by Santosh Gade

    The first row is not getting load

    Avinab Das Seasoned Veteran

      Hi all,

       

      So I am trying to load the data into the table all the data is getting loaded successfully into the table only first row is showing the error:

      Transformation [EXPTRANS] had an error evaluating variable column [v_TimeOn]. Error message is [<<Expression Error>> [TO_DATE]: invalid string for converting to Date

      ... t:TO_DATE(s:'2021-04-01 04:30:26.000',s:'YYYY-MM-DD HH24:MI:SS.MS')].

       

      Can anyone help me with this issue.

       

      Thanks,

      Avi

        • 1. Re: The first row is not getting load
          Smitha HC Guru

          Hi Avinav,

           

          As you notice in the error there is some junk character added in the beginning '2021-04-01 04:30:26.000, Please verify the source data and remove the junck caharcter.

           

          Thanks,

          Smitha

          • 2. Re: The first row is not getting load
            Alexandru Stan New Member

            Hi,

            that symbols are the BOM value if the file is encoded as CP2252. Byte order mark - Wikipedia This can be correct or not, depends on the file. For UTF8 there is no need for BOM ( see FAQ - UTF-8, UTF-16, UTF-32 & BOM  -> What are some of the differences between the UTFs?).

            There are some sollution to remove the BOM, depending on your OS.

            Regards,

            Alexandru Stan

            • 3. Re: The first row is not getting load
              Nico Heinze Guru

              Probalby the "safest" (and most ugly) solution is to change the port type to a String in the source definition.

              Next you would need to forward this string into an Expression transformation; within the EXP, cut off the BOM character if this is the first line, otherwise take the string as it is.

              Now convert this remaining string via TO_DATE() and forward this value.

               

              This solution needs one additional EXP in the mapping and should work fine.

              I name it "ugly" because it would be better if your source system would provide the file without a BOM, but that's often beyond their capabilities for many reasons.

               

              Regards,

              Nico

              • 4. Re: The first row is not getting load
                Avinab Das Seasoned Veteran

                Hi all,

                 

                 

                I removed those special character from the Unix server by using the command:

                awk '{ sub("", ""); print }' events_july-14.txt > mower_faultlog_20210716.txt

                 

                 

                But now I want to use this as Pre Command Task in Informatica session but I am getting the the error CMN_1942:

                 

                 

                CMN_1951  [Pre/Post Session Command] Started shell command [awk '{ sub("???",""); print }' /informatica/InfaFiles/PC/srcfiles/WindBI/events_july-14.txt > /informatica/InfaFiles/PC/srcfiles/WindBI/mower_faultlog_20210716.txt]. Process id is 3867006.

                 

                 

                CMN_1949 Error: [Pre/Post Session Command] Process id 3867006. The shell command failed with exit code 2.

                 

                 

                CMN_1954 [Pre/Post Session Command] Process id 3867006. Standard output and error:

                awk: 0602-521 There is a regular expression error.

                *?+ not preceded by valid expression

                  The source line number is 1.

                  The error context is

                { >>>  sub("???","") <<<

                 

                 

                Can anyone help me with this

                Thanks,

                Avinab

                • 5. Re: The first row is not getting load
                  Nico Heinze Guru

                  A command task is always executed in the following way:

                  On Windows:

                    cmd /c "the command task"

                  On Unix/Linux:

                    sh -c "the command task"

                   

                  In particular that means that you should NEVER use double quotes withn a command task; they simply collide with the double quotes which are inserted by the operating system when running the command task, and it's extremely complicated (to say at least) to "shield" the double quotes from the operating system.

                   

                  As you already included single quotes in the command task (namely for the awk invocation), you simply cannot add the double quotes (at least not without MUCH trial and error).

                   

                  If I were you, I would use a short "if ... else ... fi" sequence. For the very first line, use "cut" to remove the first three characters, every following line would be forwarded without change.

                  Also there's probably no need for the intermediate file. You could change the source type to Command and simply use the output of the awk or cut or whatever command as the input to the mapping instead of first staging the data to a file. then read the file, and then delete the intermediate file.

                   

                  Or you proceed as suggested earlier, namely by reading the very first field as a string field, cut off the leading BOM if it is there, and then translate this into a Date/Time port using TO_DATE().

                  This approach will remove the need for any pre-session command, and you don't have to mess around with quoting characters.

                   

                  Regards,

                  Nico

                  • 6. Re: The first row is not getting load
                    Santosh Gade Guru

                    Is your RS codepage UTF8, I believe its having issue storing the BOM character .

                     

                    You can write the command in a wrapper script and just invoke the wrapper script in the pre session command task .