Issue
Have a file with the following data in it:
samplefile:
id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl
NoData|\N|6ypXQwLw8H9svA|04271|NoData
NoData|\N|7MFzlQriUjohVg|60475|NoData
NoData|\N|8AtEVeaU3fiK5w|91305|NULL
NoData|\N|AA3B5BNqNTokWw|87818|NoData
NoData|\N|CG79LhAgIeBkPw|80199|NULL
Using sed
to replace the NoData
,NULL
and \N
:
sed -i "s/\\\\\N//g" samplefile
sed -i "s/\x0//g" samplefile
sed -i "s/NoData//g" samplefile
Actual result:
id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl
NoData|\N|6ypXQwLw8H9svA|04271|NoData
NoData|\N|7MFzlQriUjohVg|60475|NoData
NoData|\N|8AtEVeaU3fiK5w|91305|NULL
NoData|\N|AA3B5BNqNTokWw|87818|NoData
NoData|\N|CG79LhAgIeBkPw|80199|NULL
Expected Result:
id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl
||6ypXQwLw8H9svA|04271|
||7MFzlQriUjohVg|60475|
||8AtEVeaU3fiK5w|91305|
||AA3B5BNqNTokWw|87818|
||CG79LhAgIeBkPw|80199|
Not getting what's wrong or missing in the sed
command that it's not producing the desired result.
Updating with the test results after implementing the suggestions:
Suggestion_1:
sed -Ei 's/\\N|NULL|NoData//g' samplefile
id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl 6ypXQwLw8H9svA|04271 7MFzlQriUjohVg|60475 8AtEVeaU3fiK5w|91305 AA3B5BNqNTokWw|87818 CG79LhAgIeBkPw|80199
Suggestion_2:
awk 'BEGIN{FS=OFS="|"} NR>1{$1=$2=$5=""} 1' samplefile
id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl 6ypXQwLw8H9svA|04271 7MFzlQriUjohVg|60475 8AtEVeaU3fiK5w|91305 AA3B5BNqNTokWw|87818 CG79LhAgIeBkPw|80199
Still, it's not matching the Expected result. Is there a way to retain the delimiters and replace the values with just a space?
Solution
Testing your code Suggestion_2
from @EdMorton comment, in GNU awk
gets the expected result
:
awk 'BEGIN{FS=OFS="|"} NR>1{$1=$2=$5=""} 1' file id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl
||6ypXQwLw8H9svA|04271|
||7MFzlQriUjohVg|60475|
||8AtEVeaU3fiK5w|91305|
||AA3B5BNqNTokWw|87818|
||CG79LhAgIeBkPw|80199|
GNU awk version:
awk --version
GNU Awk 5.1.0, API: 3.0 (GNU MPFR 4.1.0, GNU MP 6.2.1)
....
and same result with the sed
from @jthill comment:
sed -E 's/(\\N|NULL|NoData)//g' file id|mdl_name|mdl_tagid|mdl_brnchcd|mdls_mktvl
||6ypXQwLw8H9svA|04271|
||7MFzlQriUjohVg|60475|
||8AtEVeaU3fiK5w|91305|
||AA3B5BNqNTokWw|87818|
||CG79LhAgIeBkPw|80199|
This can help: https://unix.stackexchange.com/questions/145402/regex-alternation-or-operator-foobar-in-gnu-or-bsd-sed
Answered By - Carlos Pascual Answer Checked By - Mary Flores (WPSolving Volunteer)