File transformation - what is most efficient method

Shell Programming and Scripting
1

I've done quite a bit of searching on this but cannot seem to find exactly what I'm looking for. Say I have a | delimited input file with 6 columns and I need to change the value of a few columns and create an output file. With my limited knowledge I can do this with many lines of code but want some expert opinions on what would be the most efficient as I'll be working with some large files.

Example input row:
12345|employee1|customer2|d. gibbins|20091028|10000

column1 - leave as is
column2 - if employee is in lookup1.txt, set to Y else N
column3 - if customer is in lookup2.txt, set to Y else N
column4 - upcase
column5 - change date format to MM/DD/YYYY
column6 - add explicit decimal

Example output row:
12345|Y|N|D. GIBBINS|10/28/2009|100.00

I'm not looking for exact syntax, but a general idea of commands you would use and/or workflow.
Thanks!

2 
awk -F\| '{
if (v == 3) printf("%s|%s|%s|%s|%s/%s/%s|%.2f\n",
  $1,
  ($2 in emp)? "Y" : "N",
  ($3 in cus)? "Y" : "N",
  toupper($4),
  substr($5, 3, 2), substr($5, 1, 2), substr($5, 5, 4),
  $6/100)
else if (v == 1) emp[$0]
else if (v == 2) cus[$0]
}' v=1 lookup1.txt v=2 lookup2.txt v=3 maininput.txt
3

This worked perfectly. Thanks alot for your time.

One other quick question if I could. If I have to trim white space from a column, is there a way to do that with printf? I was able to do it by assigning the column to a variable and use gsub. Just wondering if there's a quicker way.

Thanks again.

4

Instead of trimming the spaces, you can set your column delimiter to:
optional spaces followed by "|" and then followed by optional spaces with

awk '
BEGIN { FS=" *\\| *" }
...
'
5

Interesting, didn't know that was an option for FS. Unfortunately my input file is fixed length and I'm doing a bunch of substr's to pull out the data I need. I said | in my original question just to keep it simple.
Thanks

6

Trim left white spaces:

gsub(/^[ \t]+/, "", string)

Trim right white spaces:

gsub(/[ \t]+$/, "", string)