conditional split on field in csv file

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

Might there be trailing spaces on the file?

Try:

Right(Trim([FieldA]),4)=="ABCD"

|||

Thanks fo reply, yes I tried that one but it doesn't have trailing spaces. I think it has something to do with fields in Input file.

When I look at the file in notepad values are

"123","somevalue"

"123ABCD","somevalue"

I have seen some posts regarding text qualifier as a problem, mine is set to " but conditional split still fails.

|||Is it throwing any type of error message?|||

No the file processes fine.

If I add another row to file and put in the suffix then the conditional split works but I think this is because the text qualifiers are removed once I had row to file eg

Original file

"123","somevalue"

"123ABCD","somevalue"

goes thru data flow task with no errors but conditional split doesn't work

Modified file will look like

123,somevalue

123ABCD,somevalue

456ABCD,somevalue

and will work for both rows with suffix.

|||

Could you put a data viewer before the conditional split and see what data is getting in?

Thanks.

|||

Have put data viewer in and first riow looks correct but rows following look like they have another delimiter shown below

123, somevalue

o “456”, “somevalue”

o “123ABCD”, “somevalue”

Row delimiter is set to {CR}. Column 1 delimiter is set to {CR} if I change this to {CR}{LF} then the process fails with error delimeter for column 1 not found.

Thanks

|||

Got it to work in the Columns tab

Row Delimeter is {LF}

Column delimiter is Comma {,}

Text qualifier is "

in advanced tab

Coliumn delimeter of 2nd column is {LF}

which ended up making data come in as

123, somevalue"

456, somvalue"

123ABCD, somevalue"

which the conditional split was happy with.

Thanks for your help