How to count words in a delimited file?

I was asked how would one do word count in a delimited file and it took me by a surprise since my goto Unix command "wc" works only with tab or space delimited text.

From wc manual page - "A word is defined as a string of characters delimited by white space characters."

This is where sed is comes to rescue.

ruch:coding ruchi$ cat poem.csv 
twinkle,twinkle,little
star,how,I, wonder
what,you, are.
ruch:coding ruchi$ cat poem.csv | wc -w
       5
ruch:coding ruchi$ sed 's/,/ /g' poem.csv | wc -w
      10
ruch:coding ruchi$ 

Can you guess why wc returns 5 instead of 3 from poem.csv?

Substring : Right to Left

Informatica does not have this documented but you could use SUBSTR function to go from Right to Left as well. Just use a negative start.

IIF(SUBSTR(FILENM,-4)='.PDF', 'PORTABLE')

 

Regular Expressions in Informatica

Informatica supports PERL like regex syntax. So any parsing you would do in shell, can be done in Informatica as well.

REG_EXTRACT('Fiction__JK_ROWLING__HARRY_POTTER__MAX07','(\w+)(__\w+__)(\w+)(__.*)',3)

In the code above, __ are the delimiters in the string.

The regext part
\w looks for any alphanumeric character including underscore. Adding + to it makes it look for multiple alphanum characters.
() are to group the patterns
. looks for any character and adding & * makes it look for all the occurrences of any characters.
So we have four groups and the output is the 3rd group.

OUTPUT

HARRY_POTTER

Can you figure the other groups? Now can you figure how many groups we need if we just need the number of books in series or just the genre?

Using Shell Variables in awk and sed

For sed - use double quotes around the variable.

sed '/'"$pattern"'/d' filename

Another option is to enclose entire sed command in double quotes.

sed "/$pattern/d" filename

For awk - use combination of double quotes single quotes double quotes

awk '{ if ($(1) == "'"$pattern"'" ) {print $2}}'

Optionally, you could create an awk variable from shell variable.

awk '{ if ($(1) == ipattern ) {print $2}}' ipattern=$pattern filename

Router Transformation Groups

Router Transformation's Groups are not the same as CASE statement conditions or what you may also call waterfall logic.

Router transformation routes data to all the groups that evaluate to TRUE unlike the CASE statement that executes the code only for the first true condition.

Awk one liner IF

Here is a quick one liner AWK for field comparison.

awk -F, '{if ($(1)=="0") {print $1}}' myfilename

What does it do?
From the comma separated file every record with first field as zero is printed.

It's important to keep parentheses as indicated. Although "print $1" is good; "if $1" poops.

Python - All Set for Comparison

I spent entire evening looking for a way to sort the characters in a string and figured it out.

1. Turn the String into List. 
2. Sort it. 
3. Join it back as a String.

And then, I stumbled upon a better way to do string comparison. Convert them to sets!!!

set(stringA).difference(stringB)

Note that it may not be same as
set(stringB).difference(stringA)

Details on set theory here:

http://docs.python.org/library/sets.html#set-objects

Discussion here:

http://stackoverflow.com/questions/1388818/how-can-i-compare-two-lists-in-python-and-return-matches