If it's always 49 columns in column A (or any other fixed number) and the pipe character is always used as a column delimiter and not for field content, then it's easy (with one little twist).
Import the file as being pipe-delimited.
Of course that means that you have to do one or two things manually after the import.
First - if there is some quoting character around the contents of column A, e.g. a single quote ' or a double quote " - you have to remove this quote from the beginning of the first field (which must be imported as a string field) "manually", meaning using the SUBSTR() function (before processing the "real" contents of this first column).
Second you have to make sure that you remove everything after the "real" column delimiter in the CSV file (because it doesn't belong to the 49th sub-column of column A), that can be done using SUBSTR() as well.
You would have to use an excel source to read the excel and then pass the one field to a structure parser which then could pass parse the row to columns for you to use.
Thank you Nico for your response. I'm fairly new to Informatica and not sure of how or where to setup the SUBSTR() function to perform the manual processing of removing the double quote or completing the second task of making sure that everything is removed after the "real" column. Can you point me in that direction with an example or any means to perform these 2 task?
1 of 1 people found this helpful
Nico's response is spot on for PowerCenter. While you could use the same approach for CDI it is most definitely not the easiest approach now given the advancements in IICS.
PowerCenter didn't have a easy way to read excel, IICS CDI does.
PowerCenter did not have a easy way to do a mid stream data parser based on a intelligent structure discovery parser. IICS CDI does.
I would highly suggest you look at CDI ISD (Intelligent structure discovery) in conjunction with a structure parser.
Eliminates the need for using SUBSTR and a lot of the overhead.
Thank you user126898 for you response but unfortunately I am not familiar enough with Informatica to perform those sort of actions. I have created several synchronization tasks that are mapped to my target. I have not used the Power Center, and believe my version of Informatica may exclude it from my used. Therefore I'm unable to perform the actions that Nico recommended and you concurred with.
PowerCenter is the legacy on-prem ETL tool. You are correct that is not part of IICS. You would have to use Cloud Data Integration and mappings to solve this use case. Sync tasks will not be enough.
Thank you for very much for your reply...I'm not exactly sure what to do at this point but its good to know my tool has reached it limit.
So as part of your existing license sync tasks are part of the data integration base or even the new IPU model (not sure when you company purchased IICS).
When you go to create a new asset, you would select "Mappings" and then "Mapping" instead of a sync task under tasks.
A mapping would look something at a high level like the second screen shot. The source would be an excel connection to read the excel. The structure parser would parse the piped field into the target.
Configuring the source and target would be exactly like the sync task steps with picking the connection and object.
Now the structure parse is new and all I can do there is provide you with the documentation for you to take a look at.
Hopefully this gives you some direction.
Thank you Scott...this appears doable. I'll update once its done. Going to jump off here for a meeting but thanks again.
Scott...over the weekend I gain some understanding of the structured parser transformation but not enough to clear up this error. I verified Source's connection, source type and object. Any ideas?
TRANSF_1_1_1> STRUCTURE_PARSER_2 [2021-06-07 10:12:50.591] [ERROR] Cannot find the input file. For a flat file source transformation, verify that you used a reference file. Verify that all referenced file paths are valid.
Structure parser need the absolute path of the file that it has to parse.
So the source should have something like this (single column flat file)
map Path column to Structure parser transformation.
Thank you very much for your response Nitin...can you possibly clear this up for me concerning the absolute path. Is it the structured parser needing the path or the source? I get an error with the absolute pass in the source and can't find input field for the path in the parser. Below is what I'm seeing for both. Thank you. Tom
i mean the content of the source file like this, say name is src.txt
Choose src.txt as Source and you will see Path as column in source transformation.