无所谓，趁机来这里 update 一下，
Last time I receive a list of mobile number in .csv file, I need to insert those mobile numbers into temporary table in SQL Server.
The data in .csv file looks like below:
There are few ways to do that, one of the method is using DTS (Or SSIS in MSSQL 2005) to transfer data. However I need to login to Remote Desktop (RD), transfer the .csv file to remote server to do that.
And that day the only two slots for RD already used by my colleagues. So I need to figure out other way.
Ok, I was thinking this way:
- First, since I can connect to remote SQL Server from my local PC, so I can create a temp table from my PC using create table script.
- After I got temp table, I can run the INSERT INTO [TEMP_TABLE] (mobile) VALUES (‘601XXXXXXXX’) statement from local PC.
- Duplicate the insert statement in # 2 for all mobile numbers, and run it from SQL Query.
- Now I will have a temp table with all mobile numbers inside, no need DTS or SSIS.
Step 1 is not an issue for me, but when come to step 2, I need to append the word INSERT INTO [TEMP_TABLE](mobile) VALUES (‘<mobile-number-in-each-row>‘) to every rows in the .csv file.
So the best way is to replace mobile numbers in each row with the BOLD words above, ok, replace … , replace … , replace function in Notepad++ (Editor I using now), then I press CTRL + H after open Notepad++.
Ehh~~?!, why the Notepad++ search box has one option to pick the search mode for “Regular Expression”? Arrrhh~~~~, I knew it, maybe the Notepad++ author already foresee the situation I facing now.
That is great, I make a quick search on http://notepad-plus.sourceforge.net/uk/regExpList.php, then give a try to my .csv file, haha, it work now.
- Open Notepad++, then press CTRL + H.
- Key in “(601[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9])” in “Find What” textbox.
- Key in “INSERT INTO [0_TEMP] VALUES(‘\1’)” in “Replace With” textbox.
- Choose “Regular Expression” in “Search Mode”.
- Click “Replace All” button.
- Done, yeah.
Below is how the search box should look like (may vary on different version):
The final outcome will look like this:
INSERT INTO [0_TEMP] VALUES(‘60121234567’)
INSERT INTO [0_TEMP] VALUES(‘60122345678’)
INSERT INTO [0_TEMP] VALUES(‘60133456789’)
INSERT INTO [0_TEMP] VALUES(‘60164567890’)
INSERT INTO [0_TEMP] VALUES(‘60195678901’)
INSERT INTO [0_TEMP] VALUES(‘60140011223’)
Just a brief explanation to the regexp pattern above (easy for me for future reference):
- The pattern I using now only support by Notepad++, it is not global regexp pattern.
- Pattern enclose in “(” and “)” is consider as single parameter that going to be replace later.
- Notepad++ can recognize the target parameter by using \1, \2, \3 until \9. In my case I only use “\1”.
== [ End ] ==
Words after end: If notepad++ has the features: 1) Column edit, and 2) Hex Edit, then I will vote Notepad++ as the best editor, currently I will say UltraEdit is the best although the interface is qute ugly.
Search Keyword: Tips, Tricks, Notepad++, Tools, Regular expression, regexp, text editor.