Remove specific column if exists in CSV file

Question

I've a CSV file that contains about 25 columns. Some rows of the file contain 26 columns, so that I want to search for the lines that contain that extra column and remove it to be able to use awk with the whole file.

Fields are separated by ; Semicolon. The extra column is in the format of VARNAME="Text is here" and the value "text is here" is arbitrary text.

I managed to remove the VARNAME from all lines but I can't explore a pattern that matches the arbitrary value (the quoted text).

My target is, find lines with that extra column (VARNAME="Text is here") and remove it.

Example:

Current file:

ROW1: VAR1:"Value 1";VAR2="Value 2";VAR3="Value 3"
ROW2: VAR1:"Value 4";VAR2="Value 5";VAREXT="Different Values";VAR3="Value 6"

Target File should be:

ROW1: VAR1:"Value 1";VAR2="Value 2";VAR3="Value 3"
ROW2: VAR1:"Value 4";VAR2="Value 5";VAR3="Value 6"

Answer 1

You can use something like:

sed 's/;VAREXT.[^;]*//' file  #combine with -i for in-place editing

Testing:

a=$'"ROW2: VAR1:"Value 4";VAR2="Value 5";VAREXT="Different Values";VAR3="Value 6"'
b=$'"ROW2: VAR1:"Value 4";VAR2="Value 5";VAREXT="1234567";VAR3="Value 6"'
c=$'"ROW2: VAR1:"Value 4";VAR2="Value 5";VAREXT="VAREXT";VAR3="Value 6"'

echo "$a" |sed 's/;VAREXT.[^;]*//'
echo "$b" |sed 's/;VAREXT.[^;]*//'
echo "$c" |sed 's/;VAREXT.[^;]*//'

"ROW2: VAR1:"Value 4";VAR2="Value 5";VAR3="Value 6"
"ROW2: VAR1:"Value 4";VAR2="Value 5";VAR3="Value 6"
"ROW2: VAR1:"Value 4";VAR2="Value 5";VAR3="Value 6"

Answer 2

Expecting your csv has no header, there are no spaces after semicolon and only one VAREXT... per line, then with respect to your sample try:

sed 's/;VAREXT=\"[A-Za-z0-9 ]*\"//' in.csv

Where the value of VAREXT could be a composite of letters, digits and spaces.