Jhanvi asked me to help with editing a text file recently. She had a file in the format
'512'
'345'
'876'
and needed to convert it into the format below
INSERT INTO BLAH VALUE ('512');
INSERT INTO BLAH VALUE ('345');
INSERT INTO BLAH VALUE ('876');
There are multiple ways, one can do this. Here is how I did this using Notepad++, an open source text editor. I used the regular expression capability of Notepad++ it’s search and replace function.
- Press “Ctrl + h” to bring up the search and replace window.
- Replace the single quote at the beginning of the line by using ” ^’ “
- Replace the single quote at the end of the line by using ” ‘$ “
Screenshots from the operation
The data in it’s original format
Replacing the first quote mark
Data after the first search and replace operation
Replacing the second quote mark
Data in the final format
Thanks for talking about this topic. I had been searching for good information about it.
how i can replace ip address. for examle 100.29.111.21,
into A.A.A.A?
Roman – I think this might be difficult to do in notepad++. You may be better off using a scripting language (perl, python). Check out this link http://www.regular-expressions.info/examples.html . Hope it helps you.
Difficult? What about using the following regular expression:
[0-9]+.[0-9]+.[0-9]+.[0-9]+
[0-9] means any number
+ means it has to appears at least once
This will find any series of 4 numbers separated by dots, from IP addresses to something like 0.23.1.2323523
Guillaume, thanks, I tried to use that regular expession to find IP-addresses in my log file, but your expression finds not only IP-adresses, it find any series of 4 numbers separated by any symbol (for example “22 09:24:24”), how can I change your expression to search only IP?
Ruslan – You can tweak Guillaume’s regex a bit to take care of this exception. You just need to search for . explicitly. See if this modified regex helps
[0-9]+.[0-9]+.[0-9]+.[0-9]+
I tried it with your test data and it did not match the time stamp.
Vinay, your expression works correctly.
Thank you!
Appreciate some Jedi help… I have the following string:
>gi|374638939|gb|AEZ55452.1| myosin light chain 2, partial [Batrachoseps major]
AAMGR
repeating sporadically throughout my document and want to remove everything from:
>gi|37463 to the AAMGR sequence
but, I want to keep the blocks where JQ250 appears:
>gi|374638935|gb|AEZ55450.1| myosin light chain 2, partial [Batrachoseps major]
AAMGR
>gi|374638936|gb|JQ250332.1| Batrachoseps major isolate b voucher DBW5974 myosin light chain 2 gene, partial cds
GCNGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCATGCAATGGGGGCGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACCTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
TCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAGAGACCCCAGTATGACGTCGTCATTGCTCC
CAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
and remove only the lines that have AEZ554:
>gi|374638939|gb|AEZ55452.1| myosin light chain 2, partial [Batrachoseps major]
AAMGR
……………………………….
So, ideally the following block:
>gi|374638934|gb|JQ250331.1| Batrachoseps major isolate a voucher DBW5974 myosin light chain 2 gene, partial cds
GCNGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCATGCAATGGGGGCGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACCTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
TCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAGAGACCCCAGTATGACGTCGTCATTGCTCC
CAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
>gi|374638935|gb|AEZ55450.1| myosin light chain 2, partial [Batrachoseps major]
AAMGR
>gi|374638936|gb|JQ250332.1| Batrachoseps major isolate b voucher DBW5974 myosin light chain 2 gene, partial cds
GCNGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCATGCAATGGGGGCGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACCTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
TCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAGAGACCCCAGTATGACGTCGTCATTGCTCC
CAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
>gi|374638937|gb|AEZ55451.1| myosin light chain 2, partial [Batrachoseps major]
AAMGR
>gi|374638938|gb|JQ250333.1| Batrachoseps major isolate a voucher MVZ:Herp:249023 myosin light chain 2 gene, partial cds
GCCGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCCTGCAATGGGGGTGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACTTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
CCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAAGAGACCCCAGTATGACGTCGTCATTGCTC
CCAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
>gi|374638939|gb|AEZ55452.1| myosin light chain 2, partial [Batrachoseps major]
AAMGR
Would be left as just:
>gi|374638934|gb|JQ250331.1| Batrachoseps major isolate a voucher DBW5974 myosin light chain 2 gene, partial cds
GCNGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCATGCAATGGGGGCGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACCTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
TCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAGAGACCCCAGTATGACGTCGTCATTGCTCC
CAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
>gi|374638936|gb|JQ250332.1| Batrachoseps major isolate b voucher DBW5974 myosin light chain 2 gene, partial cds
GCNGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCATGCAATGGGGGCGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACCTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
TCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAGAGACCCCAGTATGACGTCGTCATTGCTCC
CAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
>gi|374638938|gb|JQ250333.1| Batrachoseps major isolate a voucher MVZ:Herp:249023 myosin light chain 2 gene, partial cds
GCCGCCATGGGTAAGTGAACGCGCCGGACCAGACCATTCACTGCCTGCAATGGGGGTGTTTGTGGGTTGG
AAGGTGTGCCAAAGATCTAGGGAACCCCAACTCCTCAGGATACGGGTGGGAGCCCTAAAATATGTCCAGC
TATAAGGAGATGACCAATGGAAAAGGGGGTATCAGCAGTACTTTACTTGCTACTATAAGAGAATTGCATC
CTGGGAATAGCCTCTGAAAGGTCCCATTTTAGCGACACTGGTAGATGGACACTGGCCTTTGGACAGCACC
AGTAAGTAGAGCATTGCATCTTGGGATTCCTTTGCTGTTCACATGCCACTGAAAGCTCTCACCATAGCAG
ATTCAAAATGCCTACCCGGCAGGTTGCCAGAAAAGCACTGCATCATGGGAGAACCACTTTTAGTGACAAT
CCTAAGAGATGGGTGTCTCTCTGCCAGGCGCTATTATCCAAGAGACCCCAGTATGACGTCGTCATTGCTC
CCAGGTAACCATGTTCTCACCCCCTCTCCCACAGGCCGC
……………………………many thanks as I help a struggling Grad Student
Eric – You can do this a lot quicker (easier) in a scripting language like Perl/Python. Essentially ready the file into an array and then process each line
1) If it contains AEZ554 delete it
2) If it contains JQ250, truncate it
I can help put this script together.. but need a clean set of the data. Can you host the file in a site like pastebin (http://pastebin.com )?
– Vinay
Can I do the following in Notepad++? I need to replace Message in the name line with the data in between the quotation marks on the desc line. See below.
BEFORE
name = “Message”;
desc = “Message 11 from host to ABC.”;
AFTER
name = “Message 11 from host to ABC.”;
desc = “”;
The information in the desc line in between the quotation marks varies. Everything else is consistently the same throughout the document.
Tom – That would be a lot easier to do in a scripting language like perl/python instead of notepad++. Let me know if you need help with that.