In a Windows log file, I am trying to extract all text that has INSERT statements that end with "NULL)"
10/19/2017 3:18:11 PM - There was an error in somefile.cls - RunSQLNoRecords: -2147217900 Incorrect syntax near 've'. Microsoft OLE DB Provider for SQL Server
10/19/2017 3:18:11 PM - update T_Sales set status = 'SALES_EMAILED' where salesid = '14499'
10/19/2017 3:18:11 PM - insert into T_Email (EmailUniqueID, DateTime, RecipientEmail, SenderEmail, Subject, MessageTxt, SalesID, ClientID) VALUES ('{4A2B9F9F-D995-46F0-98E4-529FAB0738EF}', convert(datetime, '10/19/2017 3:18:11 PM', 102), 'mario.frankovich@bbsl.ca', 'someguy@somesite.com', 'A friendly note to say thank you & I've moved firms', ' <p>
Good Afternoon,</p><p>I''m reaching out to inform your that I have recently joined somefirm Portfolio Services. Please see below for an overview of why we are building the firm and how we enhance the lives of both financial planners their clients. </p><p>For those with whom I''ve had a personal and business relationship, I say thank you, it has been a pleasure being associated with you.</p><p>Good luck and please keep in touch. I will do the same as I always enjoy chatting about the industry. MSome text.</p><p>Kind Regards,</p><p>Chris</p><p><strong>Overview</strong></p><p>somefirm exits to support financial planners, enhance their lives and the lives of their clients. We are champions of the financial planning profession here in Canada and abroad. We believe that technology can enhance and strengthen the wealth advisor/client relationship not displace it. As technology continues to democratize the asset management business and seismic shifts in compliance sweep across the industry, one thing will never change, the need for quality financial p[anning advice. </p><p>As a WealthTech company, sSome textby their clients. </p><p> We are seeing a movement take place where advisors are relinquishing their mutual fund license to focus strictly on financial/insurance planning thus outsourcing the investment management to somefirm. As an OSC registered portfolio manager we handle all compliance and operational functions allowing advisors to increase client facing and asset gathering time.</p><p>If you are feeling challenged by changes in compliance, wish you could keep all your revenue and/or could just focus on your passion of helping people with their financial planning needs, we can help. </p><p>I''d be delighted to speak to anyone who has interest in learning more about somefirm and hearing how we are enhancing wealth advisors lives. Feel free to check us out at <a href="http://www.somesite.com/">www.somesite.com</a><br>
<br>
<br>
Regards<br>
<br>
<!-- <font style=''color: #113E5F;font-size: 14px;''>Someguy<sup>®</sup>, CIM<sup>®</sup><br />
CEO, somefirm Portfolio Services Inc.<br /></font>
<font style=''color: #808080;font-size: 14px;''>Some Address<br />
phone numbers <br />
Email: <font style=''color: #3381D0;''><u>rfox@somesite.com</u></font> Web: <font style=''color: #3381D0;''><u>www.somesite.com</u></font><br /></font> -->
<font style="color: rgb(17, 62, 95); font-size: 14px;">Some guy, CFA<br>
Some sales guy, some sales<br></font>
<font style="color: rgb(128, 128, 128); font-size: 14px;">Some Address<br>
phone numbers |phone numbers <br>
Email: <font style="color: rgb(51, 129, 208);"><u>someguy@somesite.com</u></font> Web: <font style="color: rgb(51, 129, 208);"><u>www.somesite.com</u></font><br></font>
<br>
[TRACKER]<br><br>
[UNSUBSCRIBE]<br><br>
[OPTIN]<br><br>
Privacy Disclaimer<br>
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary, confidential. If you are not the intended recipient, you are notified that any dissemination, distribution, or copy of this communication is strictly prohibited. If you have received this communication in error, please notify the sender and erase this e-mail message immediately.
</p>
', 14500, NULL)
10/19/2017 3:18:11 PM - There was an error in somefile.cls - RunSQLNoRecords: -2147217900 Incorrect syntax near 've'. Microsoft OLE DB Provider for SQL Server
10/19/2017 3:18:11 PM - update T_Sales set status = 'SALES_EMAILED' where salesid = '14500'
10/19/2017 3:18:12 PM - insert into T_Email (EmailUniqueID, DateTime, RecipientEmail, SenderEmail, Subject, MessageTxt, SalesID, ClientID) VALUES ('{F6480F5F-A901-4F00-B2AB-DF257D18628E}', convert(datetime, '10/19/2017 3:18:12 PM', 102), 'angelo.mattina@bbsl.ca', 'someguy@somesite.com', 'A friendly note to say thank you & I've moved firms', ' <p>
Good Afternoon,</p><p>I''m reaching out to inform your that I have recently joined somefirm Portfolio Services. Please see below for an overview of why we are building the firm and how we enhance the lives of both financial planners their clients. </p><p>For those with whom I''ve had a personal and business relationship, I say thank you, it has been a pleasure being associated with you.</p><p>Good luck and please keep in touch. I will do the same as I always enjoy chatting about the industry. MSome text.</p><p>Kind Regards,</p><p>Chris</p><p><strong>Overview</strong></p><p>somefirm exits to support financial planners, enhance their lives and the lives of their clients. We are champions of the financial planning profession here in Canada and abroad. We believe that technology can enhance and strengthen the wealth advisor/client relationship not displace it. As technology continues to democratize the asset management business and seismic shifts in compliance sweep across the industry, one thing will never change, the need for quality financial p[anning advice. </p><p>As a WealthTech company, sSome textby their clients. </p><p> We are seeing a movement take place where advisors are relinquishing their mutual fund license to focus strictly on financial/insurance planning thus outsourcing the investment management to somefirm. As an OSC registered portfolio manager we handle all compliance and operational functions allowing advisors to increase client facing and asset gathering time.</p><p>If you are feeling challenged by changes in compliance, wish you could keep all your revenue and/or could just focus on your passion of helping people with their financial planning needs, we can help. </p><p>I''d be delighted to speak to anyone who has interest in learning more about somefirm and hearing how we are enhancing wealth advisors lives. Feel free to check us out at <a href="http://www.somesite.com/">www.somesite.com</a><br>
<br>
<br>
Regards<br>
<br>
<!-- <font style=''color: #113E5F;font-size: 14px;''>Someguy<sup>®</sup>, CIM<sup>®</sup><br />
CEO, somefirm Portfolio Services Inc.<br /></font>
<font style=''color: #808080;font-size: 14px;''>Some Address<br />
phone numbers <br />
Email: <font style=''color: #3381D0;''><u>rfox@somesite.com</u></font> Web: <font style=''color: #3381D0;''><u>www.somesite.com</u></font><br /></font> -->
<font style="color: rgb(17, 62, 95); font-size: 14px;">Some guy, CFA<br>
Some sales guy, some sales<br></font>
<font style="color: rgb(128, 128, 128); font-size: 14px;">Some Address<br>
phone numbers |phone numbers <br>
Email: <font style="color: rgb(51, 129, 208);"><u>someguy@somesite.com</u></font> Web: <font style="color: rgb(51, 129, 208);"><u>www.somesite.com</u></font><br></font>
<br>
[TRACKER]<br><br>
[UNSUBSCRIBE]<br><br>
[OPTIN]<br><br>
Privacy Disclaimer<br>
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary, confidential. If you are not the intended recipient, you are notified that any dissemination, distribution, or copy of this communication is strictly prohibited. If you have received this communication in error, please notify the sender and erase this e-mail message immediately.
</p>
', 14501, NULL)
10/19/2017 3:18:12 PM - There was an error in somefile.cls - RunSQLNoRecords: -2147217900 Incorrect syntax near 've'. Microsoft OLE DB Provider for SQL Server
10/19/2017 3:18:12 PM - update T_Sales set status = 'SALES_EMAILED' where salesid = '14501'
10/19/2017 3:18:12 PM - insert into T_Email (EmailUniqueID, DateTime, RecipientEmail, SenderEmail, Subject, MessageTxt, SalesID, ClientID) VALUES ('{3E07C354-18CC-4481-8908-346BC5A852AD}', convert(datetime, '10/19/2017 3:18:12 PM', 102), 'reg.richter@bbsl.ca', 'someguy@somesite.com', 'A friendly note to say thank you & I've moved firms', ' <p>
Good Afternoon,</p><p>I''m reaching out to inform your that I have recently joined somefirm Portfolio Services. Please see below for an overview of why we are building the firm and how we enhance the lives of both financial planners their clients. </p><p>For those with whom I''ve had a personal and business relationship, I say thank you, it has been a pleasure being associated with you.</p><p>Good luck and please keep in touch. I will do the same as I always enjoy chatting about the industry. MSome text.</p><p>Kind Regards,</p><p>Chris</p><p><strong>Overview</strong></p><p>somefirm exits to support financial planners, enhance their lives and the lives of their clients. We are champions of the financial planning profession here in Canada and abroad. We believe that technology can enhance and strengthen the wealth advisor/client relationship not displace it. As technology continues to democratize the asset management business and seismic shifts in compliance sweep across the industry, one thing will never change, the need for quality financial p[anning advice. </p><p>As a WealthTech company, sSome textby their clients. </p><p> We are seeing a movement take place where advisors are relinquishing their mutual fund license to focus strictly on financial/insurance planning thus outsourcing the investment management to somefirm. As an OSC registered portfolio manager we handle all compliance and operational functions allowing advisors to increase client facing and asset gathering time.</p><p>If you are feeling challenged by changes in compliance, wish you could keep all your revenue and/or could just focus on your passion of helping people with their financial planning needs, we can help. </p><p>I''d be delighted to speak to anyone who has interest in learning more about somefirm and hearing how we are enhancing wealth advisors lives. Feel free to check us out at <a href="http://www.somesite.com/">www.somesite.com</a><br>
<br>
<br>
Regards<br>
<br>
<!-- <font style=''color: #113E5F;font-size: 14px;''>Someguy<sup>®</sup>, CIM<sup>®</sup><br />
CEO, somefirm Portfolio Services Inc.<br /></font>
<font style=''color: #808080;font-size: 14px;''>Some Address<br />
phone numbers <br />
Email: <font style=''color: #3381D0;''><u>rfox@somesite.com</u></font> Web: <font style=''color: #3381D0;''><u>www.somesite.com</u></font><br /></font> -->
<font style="color: rgb(17, 62, 95); font-size: 14px;">Some guy, CFA<br>
Some sales guy, some sales<br></font>
<font style="color: rgb(128, 128, 128); font-size: 14px;">Some Address<br>
phone numbers |phone numbers <br>
Email: <font style="color: rgb(51, 129, 208);"><u>someguy@somesite.com</u></font> Web: <font style="color: rgb(51, 129, 208);"><u>www.somesite.com</u></font><br></font>
<br>
[TRACKER]<br><br>
[UNSUBSCRIBE]<br><br>
[OPTIN]<br><br>
Privacy Disclaimer<br>
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary, confidential. If you are not the intended recipient, you are notified that any dissemination, distribution, or copy of this communication is strictly prohibited. If you have received this communication in error, please notify the sender and erase this e-mail message immediately.
</p>
', 14502, NULL)
10/19/2017 3:18:12 PM - There was an error in somefile.cls - RunSQLNoRecords: -2147217900 Incorrect syntax near 've'. Microsoft OLE DB Provider for SQL Server
10/19/2017 3:18:12 PM - update T_Sales set status = 'SALES_EMAILED' where salesid = '14502'
10/19/2017 3:18:12 PM - insert into T_Email (EmailUniqueID, DateTime, RecipientEmail, SenderEmail, Subject, MessageTxt, SalesID, ClientID) VALUES ('{7EC01F9A-ACE9-4FB7-A300-ECDAE4DE1E9B}', convert(datetime, '10/19/2017 3:18:12 PM', 102), 'lynn.travis@bbsl.ca', 'someguy@somesite.com', 'A friendly note to say thank you & I've moved firms', ' <p>
Good Afternoon,</p><p>I''m reaching out to inform your that I have recently joined somefirm Portfolio Services. Please see below for an overview of why we are building the firm and how we enhance the lives of both financial planners their clients. </p><p>For those with whom I''ve had a personal and business relationship, I say thank you, it has been a pleasure being associated with you.</p><p>Good luck and please keep in touch. I will do the same as I always enjoy chatting about the industry. MSome text.</p><p>Kind Regards,</p><p>Chris</p><p><strong>Overview</strong></p><p>somefirm exits to support financial planners, enhance their lives and the lives of their clients. We are champions of the financial planning profession here in Canada and abroad. We believe that technology can enhance and strengthen the wealth advisor/client relationship not displace it. As technology continues to democratize the asset management business and seismic shifts in compliance sweep across the industry, one thing will never change, the need for quality financial p[anning advice. </p><p>As a WealthTech company, sSome textby their clients. </p><p> We are seeing a movement take place where advisors are relinquishing their mutual fund license to focus strictly on financial/insurance planning thus outsourcing the investment management to somefirm. As an OSC registered portfolio manager we handle all compliance and operational functions allowing advisors to increase client facing and asset gathering time.</p><p>If you are feeling challenged by changes in compliance, wish you could keep all your revenue and/or could just focus on your passion of helping people with their financial planning needs, we can help. </p><p>I''d be delighted to speak to anyone who has interest in learning more about somefirm and hearing how we are enhancing wealth advisors lives. Feel free to check us out at <a href="http://www.somesite.com/">www.somesite.com</a><br>
<br>
<br>
Regards<br>
<br>
<!-- <font style=''color: #113E5F;font-size: 14px;''>Someguy<sup>®</sup>, CIM<sup>®</sup><br />
CEO, somefirm Portfolio Services Inc.<br /></font>
<font style=''color: #808080;font-size: 14px;''>Some Address<br />
phone numbers <br />
Email: <font style=''color: #3381D0;''><u>rfox@somesite.com</u></font> Web: <font style=''color: #3381D0;''><u>www.somesite.com</u></font><br /></font> -->
<font style="color: rgb(17, 62, 95); font-size: 14px;">Some guy, CFA<br>
Some sales guy, some sales<br></font>
<font style="color: rgb(128, 128, 128); font-size: 14px;">Some Address<br>
phone numbers |phone numbers <br>
Email: <font style="color: rgb(51, 129, 208);"><u>someguy@somesite.com</u></font> Web: <font style="color: rgb(51, 129, 208);"><u>www.somesite.com</u></font><br></font>
<br>
[TRACKER]<br><br>
[UNSUBSCRIBE]<br><br>
[OPTIN]<br><br>
Privacy Disclaimer<br>
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary, confidential. If you are not the intended recipient, you are notified that any dissemination, distribution, or copy of this communication is strictly prohibited. If you have received this communication in error, please notify the sender and erase this e-mail message immediately.
</p>
', 14503, NULL)
10/19/2017 3:18:12 PM - There was an error in somefile.cls - RunSQLNoRecords: -2147217900 Incorrect syntax near 've'. Microsoft OLE DB Provider for SQL Server
10/19/2017 3:18:12 PM - update T_Sales set status = 'SALES_EMAILED' where salesid = '14503'
In Notepad++, (insert)(.*?)(NULL\)) works. I can mark all the text but the problem there is that it only gives you the option to bookmark selected lines, not copy all marked text. Since the script spans across multiple lines only the first line is actually copied (along with the date/timestamp), not what I wanted.
Also tried installing https://sourceforge.net/projects/npp-plugins/files/MultiClipboard/ plugin - not compatible with the latest version of Notepad++.
In https://regexr.com/ that search string doesn't work.
I downloaded Expresso as per How to copy marked text in notepad++ but the same search string doesn't work.
I was going to write a parser in C# but I strongly feel the that would be too much effort.
There was a tool I ran into some years back which allowed you to write SQL-like queries against log files. Can't seem to find it, nor do I know if that would provide the parsing functionality I require.
So two things:
1) Is this the proper regexp search string to extract the INSERT statements, as it seems to work differently across different engines?
2) What can I use to fully extract the selected text to allow me to work directly with my insert queries?
I only want all insert statements returned:
insert into Emails (SomeID, xxx, xxx, xxx) VALUES ('....', '....', NULL)
insert into Emails (SomeID, xxx, xxx, xxx) VALUES ('....', '....', NULL)
insert into Emails (SomeID, xxx, xxx, xxx) VALUES ('....', '....', NULL)
insert into Emails (SomeID, xxx, xxx, xxx) VALUES ('....', '....', NULL)
insert into Emails (SomeID, xxx, xxx, xxx) VALUES ('....', '....', NULL)
-- UPDATE --
I have updated the sample data, now directly from the log with some edits, as my pseudo-sample data was inaccurate.
-- UPDATE 2 -- Updated the sample data
