Replace a string pattern

Shell Programming and Scripting
1

Hi,

I have a CSV with following type of data and would like to replace the timestamp information with 'null' string. Can you please suggest me on same?

8,1,'1','1',11,'2013-08-12 18:34:17.0','null',1,'2013-08-12 18:34:17.0','null','PROMOTIONAL','12','1','11','11',11,'0'

Thanks for your time.

2

try bit lengthy

sed 's/....-..-.. ..:..:..../null/g' your_filename

$ echo "8,1,'1','1',11,'2013-08-12 18:34:17.0','null',1,'2013-08-12 18:34:17.0','null','PROMOTIONAL','12','1','11','11',11,'0'"  | sed 's/....-..-.. ..:..:..../null/g'

resulting

8,1,'1','1',11,'null','null',1,'null','null','PROMOTIONAL','12','1','11','11',11,'0'
3

Perl.

echo "8,1,'1','1',11,'2013-08-12 18:34:17.0','null',1,'2013-08-12 18:34:17.0','null','PROMOTIONAL','12','1','11','11',11,'0'" | perl -nle '{s/\d{4}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[012345][0-9]:[012345][0-9]\.\d?/NULL/g; print}'
4

Thanks Akshay for replying.

Can you please suggest how can I include the "(" and ")" brackets inside this replacement string as some of the lines in CSV have date value inside braces.

Eg.
('2013-08-12 18:34:17.0') should be set to 'NULL'

5

try

$ echo "('2013-08-12 18:34:17.0')" | sed 's/[()]//g; s/....-..-.. ..:..:..../null/g'
'null'
6

The "[()]" is replacing all braces in CSV. The negative values are mentioned within () and it must be retained.

echo "(1), ('2013-08-12 18:34:17.0')" | sed 's/[()]//g; s/....-..-.. ..:..:..../null/g'
1, 'null'

Sorry for posting multiple queries. I am totally new to this project and had to try with uploading CSV to find out possible errors. I hope you will understand.

7

You can split file and then upload

8

Seems bit difficult as the () braces are used at multiple places in CSV and the file has ~0.1M lines to upload.

Is there any way I can replace

"('*')"

with null string since numeric data does not have single quotes.

9

oppss!! sorry

10

Hi Renuk,

Do not put your question on any existing thread. Create a new thread and post your question and use code as well.

Thanks
Pravin

11

At least post of few lines of input file...representative of the data sample that you are talking about and also the output desired from those posted sample lines...