Link to home
Start Free TrialLog in
Avatar of mruff
mruff

asked on

replace column/record content in a *csv file based on a regular expression

Dear experts,
I have a csv file to process, for each column for each record there is to check if a certain pattern is found, if the pattern is found the pattern itself and the next n (staic value) characters following the pattern of the record has to be replaced by a defined string.
The sring sequence: £@£1£@£ is stable and is always the prefix of the string part to be replaced, to be replaced: £@£1£@£+ next 18 characters
The to be replaced pattern can occur in any column of the csv file and at any position within a column.
Excemple csv file
record1 £@£1£@£[234234-23423-234] column1, record1 column2
record2 column1, £@£1£@£[634567-56743-432] record2 column2

expected result
record1 REPLACEMENTSTRING column1, record1 column2
record2 column1, REPLACEMENTSTRING  record2 column2

The unix OS is AIX

Many thanks for your help
Avatar of Abhimanyu Suri
Abhimanyu Suri
Flag of United States of America image

/SURI>cat sed.txt
record1 #@#1#@#[234234-23423-234] column1, record1 column2
record2 column1, #@#1#@#[634567-56743-432] record2 column2

/SURI>sed -e 's/\(#@#1#@#\).*\{18\}/REPLACEMENT/g'  sed.txt
record1 REPLACEMENT column1, record1 column2
record2 column1, REPLACEMENT record2 column2
Avatar of mruff
mruff

ASKER

Dear Abhimanyu
Executing the command I get:
sed: -e expression #1, char 35: Invalid preceding regular expression
ASKER CERTIFIED SOLUTION
Avatar of Abhimanyu Suri
Abhimanyu Suri
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mruff

ASKER

Dear Abhimanyu,
many thanks this does the job, the os is AIX
now I have another pattern
sed1.txt
record1 $#$1$#$[234234-23423-234] column1, record1 column2
record2 column1, $#$1$#$[634567-56743-432] record2 column2
sed -r 's/($#$1$#$).{18}/REPLACEMENT/g'  sed1.txt >sed1_.txt

does not do the replacement, what am I missing?
many thanks!
Avatar of mruff

ASKER

got it had to excape the $ and # by \$\#
Avatar of mruff

ASKER

Many thanks works perfect