Solved

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

Posted on 2016-11-01
6
71 Views
Last Modified: 2016-11-02
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
0
Comment
Question by:mruff
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41868367
/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
0
 

Author Comment

by:mruff
ID: 41868495
Dear Abhimanyu
Executing the command I get:
sed: -e expression #1, char 35: Invalid preceding regular expression
0
 
LVL 4

Accepted Solution

by:
Abhimanyu Suri earned 500 total points
ID: 41868947
Try this please

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

What is your OS ?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:mruff
ID: 41869657
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!
0
 

Author Comment

by:mruff
ID: 41869664
got it had to excape the $ and # by \$\#
0
 

Author Closing Comment

by:mruff
ID: 41870599
Many thanks works perfect
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
EXCHANGE 2007, SCRIPTS 67 82
Sed question 2 101
Searching File Names and Eliminating Duplicates in Linux 4 75
How to clone solaris 10 machine 33 148
My previous tech tip, Installing the Solaris OS From the Flash Archive On a Tape (http://www.experts-exchange.com/articles/OS/Unix/Solaris/Installing-the-Solaris-OS-From-the-Flash-Archive-on-a-Tape.html), discussed installing the Solaris Operating S…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question