It seems you're working with PowerCenter and not the Informatica platform. That makes it easier for me to explain what's going on.
When you set up a session in PowerCenter for a mapping with a flat file source or target, then the session will pick up the delimiter character when it's created; afterwards you may change this property in the target definition as often as you want to, the session will NOT pick up this kind of change. Never. It's simply not implemented this way.
The same holds true, for example, for the code page of a flat file source or target definition.
What you have to do is to change the file properties in the session itself.
Edit the session; go to the Mapping tab; click the flat file target in the Transformations tree on the left side; the right part of the panel contains three blocks of things to edit, namely Writers, Connections, and Properties; in the top line of the Properties part, you will find a link named Set File Properties; click here and edit the delimiter character.
Thanks for you reply Nico. Actually the screenshot I have attached with my question is at the session level. I did the same thing which you said, but if you look at the screenshot above, I can not change the delimiter even at the session level. I am not sure if there are other way.
The reason I am trying to change the delimiter to a pile is because, for instance on a column named Insured there is Mark Lewis, Jr. But when I open the csv file, its only Mark Lewis in the Insured column and Jr is moved to another one. I have never had this issue before, but I do not work much with flat files as well.
1 of 1 people found this helpful
These are probably two distinct issues. Let us cope with them one by one.
First I have the impression that you're working in a repository with version control turned on. That means you have to explicitly check out the respective session, otherwise you cannot change any of its properties.
Please correct me if my interpretation is wrong.
Second flat file handling: If I understand right, you have a comma-delimited file, and in one of those columns (Insured) there's one value which contains a comma as part of its text.
That's a bad situation and requires either of the following two approaches.
Either you make sure that the CSV file uses a delimiter character which NEVER occurs as a text content. That's the safest (and best-performing) solution.
However, that's not always possible, I know that. So the other approach is to make sure (which is something you have to discuss with those people delivering the CSV file to you) that those fields which may contain a comma are always enclosed in single or double quotes (not both, only one). AND that this character (the single OR double quote) does NEVER occur as part of a field content.
These are really the only things you can do in PowerCenter.
I am well aware that many people (in particular non-IT people) don't understand these requirements. In such cases it SOMETIMES helps to explain with a sample CSV file; some of those guys are bold enough to understand that technical necessities need their attention.
And if those people are simply not willing to change their CSV files as described above (either approach is safe to use), then they are out of luck, you CAN NOT process such files automatically. Whether they like it or not does not matter, that's a simple fact of life.
Thanks for your detail explanation Nico. I only mentioned the problem to you but let me explain the process to generate the flat file.
There is one very large report the reporting group is developing and its big in terms of the complexity of the query as well as the number of rows and columns it has. The problem is that it takes a really long time to generate it in the reporting environment and the reporting tool we use can not handle that much data. So what I am doing is I am just taking the query someone else wrote. I created a table in the data warehouse that has the exact name of the columns returned by the query. This table is the source in my mapping. Then in the source qualifier I included the query wrote by someone else so it will run the query against the warehouse and it writes it out to a flat file which is then dropped to the person's folder who uses this file for analysis.
So, when i just run the query against the data warehouse, the Insured column that has the comma is not a problem. The problem happens when the result of that query is inserted into the flat file.
Also, we do not use version control. I believe if an additional feature in PowerCenter that that you have to pay more to get. Thanks for your suggestion. For now, I will just write an expression that replaces that , with null using REPLACECHR.
Make sure that you set the optional quotes in the target file to double quotes, then Excel (or whatever tool this person is using) should be able to handle this situation gracefully.
By any chance is this session a reusable one? If so, try opening the session in the Task Developer and then make the necessary changes.