Solved

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

Posted on 2016-11-01
6
52 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Required to Read Linux Security Settings and User Permissions 13 113
Unix Question 19 44
unix example issues 18 73
Call Shell Script from Perl Script 6 75
Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now