• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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
  • 4
  • 2
1 Solution
Abhimanyu SuriSr Database EngineerCommented:
/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
mruffAuthor Commented:
Dear Abhimanyu
Executing the command I get:
sed: -e expression #1, char 35: Invalid preceding regular expression
Abhimanyu SuriSr Database EngineerCommented:
Try this please

sed -r 's/(#@#1#@#).{18}/REPLACEMENT/g'  sed.txt

What is your OS ?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

mruffAuthor Commented:
Dear Abhimanyu,
many thanks this does the job, the os is AIX
now I have another pattern
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!
mruffAuthor Commented:
got it had to excape the $ and # by \$\#
mruffAuthor Commented:
Many thanks works perfect
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now