1

I have searched and helped my way up to the moment but now i am stuck. Basically i have a text file like this :

"02/01/2018 08:34:15"|"02/01/2018 08:34:16"|"Completed"|"70000000000006632150"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"**580**"|"1600"||"355672079017"||"1600"|"590279"|"588679"|7|101369|102577|-1|200158
"02/01/2018 08:34:03"|"02/01/2018 08:34:04"|"Completed"|"70000000000006632146"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"**601**"|"1100"||"355696369862"||"1106"|"591379"|"590279"|7|101369|102577|-1|200158
"02/01/2018 08:33:17"|"02/01/2018 08:33:18"|"Completed"|"70000000000006632123"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"**319**"|"1100"||"355694523968"||"1103"|"592479"|"591379"|7|101369|102577|-1|200158

And i want to replace the bolded values with a name matching the code. I have created a lookup file like this

"319"|"AS003"
"601"|"Z 477"
"580"|"Z 478"
"101"|"AS006"

And i am using awk to search the first file , find the respective value from the 2nd , replace it and write everything to a new file. Everything works great for 319 and 101 but not for the others, and i suspect this is due to the space between Z and the code. Below the code i am using :

tail -n +2 file_name | while read line  ####used tail _n +2 to exclude header
do
code=$(echo $line | awk -F'|' '{print $12}' FS=\|)
cn=$(awk -v CID=$code '$1==CID {print $2}' FS=\| lookup_file)
echo $line|awk -v CN=$cn 'BEGIN {FS=OFS="|"} {$12=CN} 1' >> test2.txt
done

For the lines with space in the lookup file i recieve this error in the terminal:

awk: code_value"
awk:    ^ unterminated string

and it isn't written in the output file

Any suggestion would be welcomed...

Cyrus
  • 84,225
  • 14
  • 89
  • 153
Fatjon Sakiqi
  • 13
  • 3

1 Answers1

2

awk can do this all assuming your mapping file isn't too large to read into memory. Quoting in files with awk can be a pain but I don't think it matters here.

$ cat file.txt
"02/01/2018 08:34:15"|"02/01/2018 08:34:16"|"Completed"|"70000000000006632150"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"580"|"1600"||"355672079017"||"1600"|"590279"|"588679"|7|101369|102577|-1|200158
"02/01/2018 08:34:03"|"02/01/2018 08:34:04"|"Completed"|"70000000000006632146"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"601"|"1100"||"355696369862"||"1106"|"591379"|"590279"|7|101369|102577|-1|200158
"02/01/2018 08:33:17"|"02/01/2018 08:33:18"|"Completed"|"70000000000006632123"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"319"|"1100"||"355694523968"||"1103"|"592479"|"591379"|7|101369|102577|-1|200158


$ cat map.txt
"319"|"AS003"
"601"|"Z 477"
"580"|"Z 478"
"101"|"AS006"


$ awk 'BEGIN{FS=OFS="|"} FNR==NR{map[$1]=$2;next} {$12=map[$12]; print}' map.txt file.txt
"02/01/2018 08:34:15"|"02/01/2018 08:34:16"|"Completed"|"70000000000006632150"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"Z 478"|"1600"||"355672079017"||"1600"|"590279"|"588679"|7|101369|102577|-1|200158
"02/01/2018 08:34:03"|"02/01/2018 08:34:04"|"Completed"|"70000000000006632146"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"Z 477"|"1100"||"355696369862"||"1106"|"591379"|"590279"|7|101369|102577|-1|200158
"02/01/2018 08:33:17"|"02/01/2018 08:33:18"|"Completed"|"70000000000006632123"|"Activation"||"22200995102577"|"External System"|"ALFUNC ASBS"|"ASBS Shpk"|"VF Shop Asbs 1_209"|"AS003"|"1100"||"355694523968"||"1103"|"592479"|"591379"|7|101369|102577|-1|200158

The awk code first sets the OFS to | since we're changing fields, then for the first file, determined by total lines read equaling the current files line, we build a map of the first to second field, then on the second file we look up the value from that map.

Edit: As noted in a comments, my code will blank $12 if not mapped, using 

awk 'BEGIN{FS=OFS="|"} FNR==NR{map[$1]=$2;next} {$12=($12 in map ? map[$12] : $12); print}'  map.txt file.txt

instead will leave the value in place.

zzevannn
  • 3,414
  • 2
  • 12
  • 20
  • 1
    @EdMorton Thanks for pointing those things out. I see you all over the place around here so appreciate the advice. – zzevannn Feb 15 '18 at 22:20
  • Hey thank you for your response , worked great. Just a small comment , the file names at the end should be swapped , so the data file 1st and look up file 2nd for anyone who will get back to this :) – Fatjon Sakiqi Feb 16 '18 at 08:54
  • No they shouldn't be - what my answer shows is the exact files,code and output you will receive here. If you're wanting to replace values in the data file with values from a look up file using this code, you need to read the lookup file first and create your mapping, then check that mapping based on the values in the data file. See this answer on `FNR==NR` for more understanding there - https://stackoverflow.com/questions/32481877/what-is-nr-fnr-in-awk. – zzevannn Feb 16 '18 at 15:46