6 Replies Latest reply on Jan 4, 2019 2:42 PM by Nico Heinze

    Apostrophe missing in Target

    Ashish Gupta Active Member

      My source JSON contains the following string name/value pair:

      "text": "We’re pumped for the last ceremony of 2018"

      But the output in my target (Salesforce String Object.Field, also tried a CSV file) is -> "We?re pumped for the last ceremony of 2018"

       

      I tried using REG_REPLACE(text, '[^[:ascii:]]', ' ') but then I end up with the following output string "We re pumped for the last ceremony of 2018"

       

      Expected Output is: "We’re pumped for the last ceremony of 2018" (basically exactly as we receive it from the Source)

       

      We are finally using this data to create a newsletter email so the "?" is really not looking good. We can’t risk having the newsletter content without the correct punctuation. Any suggestions would be greatly appreciated.

       

      Thanks,

      Ashish

        • 1. Re: Apostrophe missing in Target
          Nico Heinze Guru

          Try to find out what character code this apostrophe has in the JSON file. My guess is that it's not a standard single quote (Chr(39)) but some other character and that your machine can't handle that other character code cleanly (hence it's replaced by an error indicator "?").

           

          Regards,

          Nico

          • 2. Re: Apostrophe missing in Target
            Ashish Gupta Active Member

            Sorry for the late response and a Very Happy new year!! we were closed for the Winter break.

             

            The incoming data is representing the apostrophe in Unicode, example: "We\u2019re".

            How can we make the data travel in Unicode from Source to Target for this particular Taskflow or Mapping/Mapping Task?

             

            Thanks,

            Ashish

            • 3. Re: Apostrophe missing in Target
              Nico Heinze Guru

              First you have to find out where exactly this special apostrophe vanishes: is it when reading the JSON file? Does it happen in the mapping? When writing to the SFDC target?

               

              Without this knowledge we cannot find out what to do exactly about this issue.

               

              If you want, I can provide you with a Java Transformation (JTX) which produces a hex dump of an input string in the session log. When inserting this JTX directly after the Source Qualifier, you can verify if the mapping receives this apostrophe correctly or if you have a code page problem with either the JSON file or the SFDC connection.

               

              Regards,

              Nico

              • 4. Re: Apostrophe missing in Target
                Ashish Gupta Active Member

                Thanks a lot, yes please, I can certainly give the JTX a try.

                 

                Thanks,

                Ashish

                • 5. Re: Apostrophe missing in Target
                  Ashish Gupta Active Member

                  Nico, quick update - tried generating the Verbose logs and found that the data is garbled when passed on to the internal web service DT parser.

                   

                  The response looks good here:

                  TRANSF_1_1_1> CMN_1053 [2019-01-03 10:59:03.159] WebServices_h2r_udt:INPUT row pushed from WebServices: Control=[None]
                  WebServices_h2r_udt:INPUT row pushed from WebServices: Rowdata: ( RowType=0(insert) Src Rowid=1 Targ Rowid=1
                    XML_OUT (UDTInput:UniChar.102400:): "{"Response_200":{"version":"1.0","title":"It’s beginning to look a lot like winter"}}
                    PT_type_OUT (type:UniChar.255:): "image"
                  )
                  

                   

                  The response is corrupted here:

                  TRANSF_1_1_1> pmudt_10062 [2019-01-03 10:59:03.159] [TRACE] Execute group UDTInput.
                  TRANSF_1_1_1> pmudt_10063 [2019-01-03 10:59:03.159] [TRACE] Run DT Parser WebServices_h2r_udt_h0bg3_ONLY_H2R_XMAP_H2R 
                  with buffer value-{"Response_200":{"version":"1.0","title":"It’s beginning to look a lot like winter"}}
                  TRANSF_1_1_1> CMN_1053 [2019-01-03 10:59:03.159] WebServices_h2r_udt: Send OUTPUT row to WebServices_h2r_udt_HIER_EXP: Control=[None]
                  WebServices_h2r_udt: Send OUTPUT row to WebServices_h2r_udt_HIER_EXP: Rowdata: ( RowType=0(insert) Src Rowid=1 Targ Rowid=1
                    root_title (title:UniChar.255:): "It�s beginning to look a lot like winter"
                    root_type (type:UniChar.255:): "image"
                  )
                  

                   

                  Thanks,

                  Ashish

                  • 6. Re: Apostrophe missing in Target
                    Nico Heinze Guru

                    Then you have a problem reading the JSON file.

                    What's the code page of the file, what's the data transformation mode of the Informatica node running the Integration Service, and what's the code page of that node?

                     

                    Regards,

                    Nico