5 Replies Latest reply on May 12, 2021 6:35 AM by Jharana Patra

    Does my double quote piped source file need to be converted before processing in Informatica?

    Thomas Kennedy Seasoned Veteran

      I am creating a synchronization task to update records and will be receiving a pipe delimited double quotes file with all the data in a single column. An example is below, which shown column headers then source data:

       

      MY_ID|VIP_ID|Contact_ID|First_Name|Middle_Name|Last_Name|Final_Banner_Application_Decision|

      XYZ65407|"00710500"|"0011A00002bnG92DIY"|"Mickey"|""|"Doe"|""|""|"202105"|""|""|""

      ABC12357|"00876543"|"0022A00003cnG94DIY"|"Bill"|""|"Tester"|""|""|"202105"|""|""|""

      FOO65407|"00712345"|"0033A00002dnG95DIY"|"Jane"|""|"Smith"|""|""|"202105"|""|""|""

       

      My questions are, will this file need converting or can I simply map it in my synchronization task as is and Informatica somehow work through the pipes and double quotes. If it needs converting or all the data in the single column needs separating into multiple columns, how is this done and can it be done in Informatica or will I have to perform those actions prior to the file being mapped my task?

       

      Thank you,

      Tom

        • 1. Re: Does my double quote piped source file need to be converted before processing in Informatica?
          Andrea Picasso Active Member

          Hi Tom,

           

          thanks, I think this can be worked out - if I have understood correctly the Use Case you could escape the " and use delimiter the pipe or tab? hope it helps:

           

          https://knowledge.informatica.com/s/article/629639?language=en_US&type=external

           

           

           

           

           

           

          Regards

          Andrea

          • 2. Re: Does my double quote piped source file need to be converted before processing in Informatica?
            Thomas Kennedy Seasoned Veteran

            Thank you Andrea for your response. I'm not certain if it made any difference but my Formatting Options window is quite different from the one presented in the link. Below is what mines looks like. As you can see, once I changed my setting to mirror the link I get the error. I've checked my source file and do not see any empty column(s). Also, in the Formatting Options window provided in the link there is a Field Label row with the Import from Row radio button checked and the value 1 as the selection/input. I'm not sure if this matter but I included it for reference. Are there other areas I can check to resolve my error? Thank you...Tom

            • 3. Re: Does my double quote piped source file need to be converted before processing in Informatica?
              user126898 Guru

              Hey Thomas,

              Looking at the header row you actually do have an empty column.  At the end of the header row you have a trailing | and then nothing after that.  Since you defined the Delimiter as | we try to parse the row and find a NULL/empty name after the last pipe that is the error.

               

              MY_ID|VIP_ID|Contact_ID|First_Name|Middle_Name|Last_Name|Final_Banner_Application_Decision|

               

              Now if you can correct the header row the double quotes are fine.  I used your sample and removed the trailing | on the header row and everything parses just fine.

               

              Thanks,

              Scott

               

              • 4. Re: Does my double quote piped source file need to be converted before processing in Informatica?
                Thomas Kennedy Seasoned Veteran

                Scott,

                Thank you for taking you time and looking into my error and let me apologize for an inaccuracy in the example. I should have removed that last pipe (|) symbol, as the true source file has no trailing pipe after the last header column. Because of the file's length I chopped it off and for that I ask you apology. Here's the complete file and the first 2 records off of it:

                 

                 

                USC_ID|VIP_ID|Contact_ID|First_Name|Middle_Name|Last_Name|Final_Banner_Application_Decision|Credit_Term_Course_Hours_Enrolled|Current_Term|Overall_Credit_Hours_Earned|Overall_USC_GPA|SC_Residency_Verification|Concentration_Code_2|Concentration_Code_1|Program_Code|Start_Term_Year|Start_Term|Attribute_Codes|TargetX_External_ID|Holds|GMAT_Total_Score|GMAT_Verbal_Test_Score|GMAT_Quantitative_Test_Score|GRE_Analytical_Score|GRE_Quantitative_Score|GRE_General_Verbal_Score|Applicant_Email_Address|Permanent_Street_1|Permanent_Street_2|Permanent_City|Permanent_State/Province|Permanent_Street_3|Permanent_Zip_/Postal_Code|Permanent_Country|CurrentStreet_1|Current_Street_2|Current_City|Current_State/Province|Current_Street_3|Current_Postal_Code|Current_Country|Home_Phone|Mobile_Phone|International_Phone|Emergency_Last_Name|Emergency_First_Name|Emergency_Relationship|USC_Email|TX_Application_Number
                XYZ65407|"00710500"|"0011A00002bnG92DIY"|"Mickey"|""|"Doe"|""|""|"202105"|""|""|""|"R098"|"NONE"|"3GBA242AMBA"|"2021"|"202105"|"3M05"|"DoeMickey11041998"|""|""|""|""|""|""|""|"mdoe@gmail.com"|"2112
                  Paula Dr"|""|"Johnston"|"AR"|""|"72404-8008"|""|"824
                  N Tenth
                  Ave"|""|"Columbia"|"AR"|""|"72701-1922"|""|""|"800-530-1234"|""|""|""|""|""|"0000007090"
                ABC12357|"00876543"|"0022A00003cnG94DIY"|"Bill"|""|"Tester"|"AC/Admit
                  w/Conditions"|"12"|"202008"|""|""|"N"|""|"NONE"|"3GBA245AMHR"|"2020"|"202008"|""|"TesterBilly07311997"|""|""|""|""|""|""|""|"billyt@ameritech.net"|"921
                  Laurens St"|""|"Simpson"|"SC"|""|"29201-3931"|""|"921
                  Greene
                  St"|""|"Columbia"|"SC"|""|"29201-3931"|""|""|"803-334-3838"|""|""|""|""|"btester@email.sc.edu"|""
                • 5. Re: Does my double quote piped source file need to be converted before processing in Informatica?
                  Jharana Patra Guru

                  Hi Thomas,

                   

                  Select the delimiter as other and mention | .This should be able to separate and read the header correctly.

                   

                  Let us know if this helps.