format file to remove " characters

Question

I have a file with the following data

"MG1507XXXXXX|" "|020000XXXXXX" "20261031|"     "|3,827.92"     "|3,581.41"     "|542,729.62"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20261130|"     "|3,680.15"     "|3,729.18"     "|539,000.44"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20261231|"     "|3,776.70"     "|3,632.63"     "|535,367.81"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270131|"     "|3,751.24"     "|3,658.09"     "|531,709.72"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270228|"     "|3,365.07"     "|4,044.26"     "|527,665.46"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270331|"     "|3,697.28"     "|3,712.05"     "|523,953.41"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270430|"     "|3,552.84"     "|3,856.49"     "|520,096.92"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270531|"     "|3,644.24"     "|3,765.09"     "|516,331.83"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270630|"     "|3,501.16"     "|3,908.17"     "|512,423.66"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270731|"     "|3,590.47"     "|3,818.86"     "|508,604.80"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270831|"     "|3,563.72"     "|3,845.61"     "|504,759.19"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20270930|"     "|3,422.68"     "|3,986.65"     "|500,772.54"   "MBA"
"MG1507XXXXXX|" "|020000XXXXXX" "20271031|"     "|3,508.84"     "|3,900.49"     "|496,872.05"   "MBA"

How ever i want to change it so it will look like

MG1507XXXXXX|020000XXXXXX|20261031|3,827.92|3,581.41|542,729.62|MBA|
MG1507XXXXXX|020000XXXXXX|20261130|3,680.15|3,729.18|539,000.44|MBA|
MG1507XXXXXX|020000XXXXXX|20261231|3,776.70|3,632.63|535,367.81|MBA|
MG1507XXXXXX|020000XXXXXX|20270131|3,751.24|3,658.09|531,709.72|MBA|
MG1507XXXXXX|020000XXXXXX|20270228|3,365.07|4,044.26|527,665.46|MBA|
MG1507XXXXXX|020000XXXXXX|20270331|3,697.28|3,712.05|523,953.41|MBA|
MG1507XXXXXX|020000XXXXXX|20270430|3,552.84|3,856.49|520,096.92|MBA|
MG1507XXXXXX|020000XXXXXX|20270531|3,644.24|3,765.09|516,331.83|MBA|
MG1507XXXXXX|020000XXXXXX|20270630|3,501.16|3,908.17|512,423.66|MBA|
MG1507XXXXXX|020000XXXXXX|20270731|3,590.47|3,818.86|508,604.80|MBA|
MG1507XXXXXX|020000XXXXXX|20270831|3,563.72|3,845.61|504,759.19|MBA|
MG1507XXXXXX|020000XXXXXX|20270930|3,422.68|3,986.65|500,772.54|MBA|
MG1507XXXXXX|020000XXXXXX|20271031|3,508.84|3,900.49|496,872.05|MBA|

I am not sure what to use to achieve this. Any ideas ?

Answer 1

You could translate all spaces and double quotes to | (and squeeze) then cut from the 2nd character to the end of line:

tr -s '[[:blank:]"]' \| <infile | cut -c2-

Answer 2

Assuming your data is in a file named 'data':

sed -e s'/^"//' -e 's/|" "|/|/g' -e 's/" "|/|/g' -e 's/" "/|/g' -e s'/"$/|/' data

Answer 3

sed -i 's/\"//g' filename

You can escape the " character by putting a \. In case you want to remove all spaces as well, do the following:

sed -i 's/[" ]//g' filename

Answer 4

Try this:

sed -e 's/["| ]\+/|/g' -e 's/^|//' < file

The first expression will replace each block containing one or more |, ", or spaces with a single |. The second will remove the | at the start of each line.

Answer 5

using awk

awk ' BEGIN { FS="[|\" ]+" ; OFS="|" } { print $2,$3,$4,$5,$6,$7,$8"|" } ' file

Explanation:

BEGIN { FS="[|\" ]+" ; OFS="|" } first set the following:

FS="[|\" ]+": fields are separated by any combination (+) (zero or more of any) of the set ([]) pipe, double quotes (need to be escaped) and space |\".

OFS="|" separate the output fields with pipes.

print $2,$3,$4,$5,$6,$7,$8"|" print columns 1 through 8 and a pipe at the end (note that it is shifted by one as the lines start with double quotes making the first field an empty string and thus shifting the position of all others).