7 Replies Latest reply on Aug 14, 2015 2:11 PM by nheinze

    Delimited flatfile

    New Member

      I created a source definition using single pipe (|) as delimiter.

      Now the source file I am getting is delimited by double pipe (||).

      How can I load the data from this data file?

      I tried loading this double delimited (||) flatfile but found that all records are getting rejected. ( I had changed the delimiter to || in source defintion and session file properties, but records still got rejected).

       

      Is it mandatory that the data file should always be delimited by same delimiter as the one with which you created the source definition??

      Looking forward for help from you guys. Thanks in advance!!

        • 1. Re: Delimited flatfile
          Guru

          As of my knowledge PowerCenter can't read files with composite delimiter characters, in your case a double pipe character. Single delimiter characters are no problem.

          Off the top of my head I see two alternatives:

          First you could feed each line as one single long string into an Expression transformation. In this case the delimiter character should be set to \037 , I haven't seen this character (ASCII Unit Separator) in use at least since 1982. Then use repetitive invocations of InStr() within the EXP to identify the positions of those double pipe characters and split up each line into fields using SubStr().

          Second (easier in the mapping, more work with the session) you could feed the file into some utility which replaces those double pipe characters by the character ASCII 31 (the Unit Separator mentioned above); the session has to be set up such that it reads the output from this utility (input file type = Command instead of File). Then the source definition should contain the \037 as the field delimiter instead of any pipe character or so.

           

          About your second question, you can always overwrite the delimiter character from the source definition in the session (there's a link called File Properties above the file name, if I recall correctly).

           

          Regards,

          Nico

          1 of 1 people found this helpful
          • 2. Re: Delimited flatfile
            gopi kolla Guru

            You can probably run a script/command in pre-session to convert double delimiters to single.

            Assuming unix something like sed 's/||/|/g' should make it a single delimiter and you can then run your workflow.

            • 3. Re: Delimited flatfile
              New Member

              You are correct Nico. Informatica does not read double pipe delimiter. Hence to overcome this, I had replaced all double pipe by single pipe in unix and had created the source definition. But, now I find data also has single pipe in data and hence I need to process the double pipe delimited flatfile.

              My current mapping is easily processing single pipe delimited flatfile now (except few records where single pipe is coming as part of data). But when I try to change the delimiter to double pipe (||) in session then all the records are getting rejected. I had changed the delimiter in session (Mapping Tab --> Sources --> Set file properties -->Advanced).

              Looking forward to your suggestion.

              • 4. Re: Delimited flatfile
                New Member

                I had already replaced double pipe to single pipe using Unix (I had used the same sed command as you have suggested here). The problem is the data also contains single pipe in few records and those records are getting rejected.

                 

                My source definition is created using single delimited file (as I had replaced double pipe to single for Informatica to read).

                But now I want to process double pipe delimited flatfile. I find single pipe delimited flatfile is getting processed easily (except few records where single pipe is part of data) but all records are getting rejected when I use double pipe delimited flatfile. I had overwritten the delimiter in session (Sources--> Set File Properties --> Advanced) but still all records of double pipe delimited flatfile getting rejected.

                • 5. Re: Delimited flatfile
                  Guru

                  Kushal,

                   

                  what do you think why I have proposed replacing those double pipe characters with the character \037? This character is safe to use as a delimiter!

                  Please re-read my post again, obviously you have not been able to get the gist of it.

                  And if after re-reading this thing is still unclear, please ask whatever is left unclear.

                   

                  Regards,

                  Nico

                  • 6. Re: Delimited flatfile
                    New Member

                    I have understood your point Nico. Thanks a lot!

                    I was just telling you what are the things I have tried

                    While trying different things, I realized Informatica does support composite delimiter characters but the characters should be different. In my case of double pipe (||) both characters were same and hence was causing a problem.

                    I will surely be considering your suggestion to replace by \037. The source team has recommended ASCII 255 as delimiter, (I am not sure whether that is supported in Informatica). I will ask them to use \037 as delimiter.

                    • 7. Re: Delimited flatfile
                      Guru

                      Chr(255) may or may not be supported, depending on the actual code page of the file. As far as I know (I haven't looked them up, to be honest) ISO-8859-15 and MS Windows Latin 1 both support this character, but I wouldn't use it because it's a potential real character. As mentioned, I haven't seen the Unit Separator in use at least since 1982, that's why I dare to assume it's safe to use.

                       

                      Yip, it's sad that PowerCenter doesn't support all kinds of composite delimiter characters, sigh...

                       

                      All the best,

                      Nico