What?
This is a simple article to demonstrate how to type special characters not found on your keyboard or on a Qwerty UK/US keyboard layout.

Why?
Being able to type international characters from other alphabets is necessary when dealing with languages other than English. Preserving files in unicode or utf-8 encoding will help but there will be times when you have lost the formatting and get weird question mark characters instead such as: .

How?
Ensure you are using the numbers on your numeric keypad and NOT the numbers at the top of your keyboard for the following steps. Also check that you have "Num Lock" on/enabled. Typing a special character is 3 easy steps:

  1. Hold down the ALT key (preferably the one on the left of the spacebar and not the Alt Gr often found on the right of the spacebar)
  2. then press the numbers in the numeric pad (while still holding down the ALT key),
  3. then let go of the ALT key.
Remember: This will not work with the numbers at the top of your keyboard unless you are on a laptop and can apply a function lock (fn) and the numbers on the right of your keyboard are enabled.


Applies To:
  • Microsoft Windows 10 Pro v10.0.18362
What?
This is a list of apps that if run via the run command, or used in a desktop shortcut, or that you want to include in automation software such as AutoHotkey, opens any specific Windows OS settings page. This does not make the change to the setting itself, just displays it to the user for them to make the choice.

Why?
I needed a command that can be run from the command-line in an AutoHotkey app to open the notifications page of the Windows 10 Settings panel to allow users to enable/disable notifications. Why write this article when there's a perfectly ok post on the Microsoft website? Well I could bookmark that page or save myself a click...

How?
To test any of the following, type the windows key and select "Run..." ( + R). Then type the value in the 2nd column "App to Run" then OK to run it:

What?
This is a quick reminder for myself on opening a CSV in MS Excel but without converting long numbers into exponential notations (which are incorrectly rounded anyway).

My Data:
copyraw
Account ID,Name,Phone,Record ID
1457102000000135533,Joel,00441234567890,1457102000000165989
1457102000000135566,Stanley,01709123456,1457102000000167543
1457102000000135608,David,00331234567890,1457102000000166795
  1.  Account ID,Name,Phone,Record ID 
  2.  1457102000000135533,Joel,00441234567890,1457102000000165989 
  3.  1457102000000135566,Stanley,01709123456,1457102000000167543 
  4.  1457102000000135608,David,00331234567890,1457102000000166795 
My Excel:
copyraw
Account ID	Name	Phone	Record ID
1.4571E+18	Joel	4.41235E+11	1.4571E+18
1.4571E+18	Stanley	1709123456	1.4571E+18
1.4571E+18	David	3.31235E+11	1.4571E+18
  1.  Account ID    Name    Phone    Record ID 
  2.  1.4571E+18    Joel    4.41235E+11    1.4571E+18 
  3.  1.4571E+18    Stanley    1709123456    1.4571E+18 
  4.  1.4571E+18    David    3.31235E+11    1.4571E+18 

How?
Firstly, save the CSV file as text file (*.TXT).
Category: Excel :: Article: 689

What?
This is an article to remind me how to search a column in an Excel file for values found in another column (in this example, on another worksheet in the same workbook).

How?
So for demonstration purposes, I'm using a new Excel file with two worksheets called "Sheet1" and "Sheet2" respectively.

What?
This article serves to explain how to split a spreadsheet consisting of multiple sheets into separate files per sheet.

Why?
The Excel file in question was about 36000 rows and had a file size of about 11Mb. In order for an import process to work, the import would only accept XLS files no greater than 1Mb. So our script has to split a single spreadsheet into multiple worksheets of 3000 rows each, and then output each sheet to a separate file that is formatted as XLS (Not *.xlsx).

How?
In summary, we are going to move the Excel file to a folder of its own. We're going to run two VBScripts in two stages, firstly to split the specified rows into sheets, then each sheet into a file each. And we want all the files generated to be created in the same folder.

Applies to:
  • Windows Live Mail 2012
What?
A quick article on how to update the Microsoft Live Server URL for Hotmail using Windows Live Mail. Well how to deactivate an account and set up a new one in Windows Live Mail. If you used IMAP all along then the new account will have all your mail.

The error
copyraw
Unable to send or receive messages for the Hotmail (someone) account.

Subject 'your Microsoft Issue'
Server Error: 3219
Windows Live Mail Error ID: 0x8DE00005
Unable to send or receive messages for the Hotmail (someone) account.

Server Error: 3219
Server: 'https://mail.services.live.com/DeltaSync_v2.0.0/Sync.aspx'
Windows Live Mail Error ID: 0x8DE00005
  1.  Unable to send or receive messages for the Hotmail (someone) account. 
  2.   
  3.  Subject 'your Microsoft Issue' 
  4.  Server Error: 3219 
  5.  Windows Live Mail Error ID: 0x8DE00005 
  6.  Unable to send or receive messages for the Hotmail (someone) account. 
  7.   
  8.  Server Error: 3219 
  9.  Server: 'https://mail.services.live.com/DeltaSync_v2.0.0/Sync.aspx' 
  10.  Windows Live Mail Error ID: 0x8DE00005 

How?
I don't remember the time when the Microsoft website became helpful but as they already have an article on this, I'm going to do a bunch of screenshots:
Category: Windows OS :: Article: 643

What I have:
A CSV file exported from Excel along with double-quotes
copyraw
label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b
  1.  label1,label2 
  2.  item1a,item2a 
  3.  item1c,"item2c,c" 
  4.  item1b,item2b 

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects
copyraw
var my_object_array = [
     { my_col1_val: 'item1a', my_col2_val: 'item2a' },
     { my_col1_val: 'item1b', my_col2_val: 'item2b' },
     { my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
];
  1.  var my_object_array = [ 
  2.       { my_col1_val: 'item1a', my_col2_val: 'item2a' }, 
  3.       { my_col1_val: 'item1b', my_col2_val: 'item2b' }, 
  4.       { my_col1_val: 'item1c', my_col2_val: 'item2c,c' } 
  5.  ]
What I want again:
  • Read a CSV file already uploaded with JavaScript
  • Populate a JS array with each row
  • Account for strings containing double-quotes (and commas to ignore)
  • Sort the resulting object array

How?
Category: Excel :: Article: 635

What?
A quick article with the code to retrieve your product key in Windows 7 with a small VB script file. There are other articles on the web about this but the ones I found returned errors such as WshShell not valid. This article has a working example applicable to Windows 7 Professional.

Why?
With the Windows 10 operating system offered as a free upgrade (for Windows 7 or later at time of print), I needed the serial number / product key from my Windows 7 Professional 32-bit operating system. I didn't want to dig through my CD/DVDs to find my original windows 7 disc and no longer have access to the email account when I purchased Windows 7 (my university one).

How?
I'm going to use my trusty notepad program, copy the following code to it, and run it:

What?
A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112.

How?
Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:
copyraw
A                                       B
-------------------------------------   ----------------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E
  1.  A                                       B 
  2.  -------------------------------------   ---------------------- 
  3.  Beijing Capital International Airport   40°420″N 116°3551″E 
  4.  Beijing Shahezhen Air Base              40°857″N 116°1917″E 
  5.  Beijing Tongxian Air Base               39°4840″N 116°4230″E 

Note that I tend to copy the coordinates off a website which has strange apostrophes and double-quotes. You can change this but remember to put two double-quotes if you are searching on it, eg:
copyraw
FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work
  1.  FIND(""",B1)   // will NOT work! 
  2.  FIND("""",B1)  // will work 
  3.  FIND("″",B1)   // will work 

Category: Excel :: Article: 596

What?
A quick article on if you want to extract the link from some text you have copied off the web and into an MS Excel document.

How?
  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
    copyraw
    Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
    Next
    End Sub
    1.  Sub ExtractHL() 
    2.  Dim HL As Hyperlink 
    3.  For Each HL In ActiveSheet.Hyperlinks 
    4.  HL.Range.Offset(0, 1).Value = HL.Address 
    5.  Next 
    6.  End Sub 
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)
Category: Excel :: Article: 595

What?
A quick article on an alternative to multiple lookups in SSIS. For any SSIS developer trying to create packages that need to decode a number of values into their full name/description (eg. "M" to "Male", "Prof" to "Professor").

Why?
Our extract phase in the SSIS package takes data from a source database and puts it in staging in the exact form it came through. Our transform package will convert the data and output it to the target system. For our package to decode what "M" means, it needs to look this up in a table on the original server. We had around 12 lookups to do, so you could do this:
But we didn't want to do this.

How?

Applies To:
  • MS SQL Server 2008 R2
  • MS Windows 7 Enterprise (Client)
  • MS Excel 2010

What?
A really quick note on how to insert a carriage return or new line into the column name/alias (the header). It might seem trivial but these little aesthetic changes done at the database level can save some time.

Why?
I have an Excel report which dynamically gets its content from a data source located on a database on the other side of the world. I want the header in the column "Academic Week" to break across two lines so that the column doesn't expand to the width of "Academic Week" and instead expands to the width of the word "Academic".

What I have:
copyraw
Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday
---------------- ----------- ----------- ----------- ----------- -----------
1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...
  1.  Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday 
  2.  ---------------- ----------- ----------- ----------- ----------- ----------- 
  3.  1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
  4.  2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014 
  5.  ... 
What I want:
copyraw
Academic 
Week      Monday      Tuesday     Wednesday   Thursday    Friday
--------- ----------- ----------- ----------- ----------- -----------
1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...
  1.  Academic 
  2.  Week      Monday      Tuesday     Wednesday   Thursday    Friday 
  3.  --------- ----------- ----------- ----------- ----------- ----------- 
  4.  1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
  5.  2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014 
  6.  ... 


How?
To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in the name of the column heading, the answer is a much simpler one,
Category: SQL Server :: Article: 568

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

report   mysql   code   zoho   time   find   data   version   following   table   list   creator   work   deluge   google   display   uploaded   file   client   function   user   website   joomla   would   need   first   added   script   note   parameter   where   error   create   date   field   name   using   windows   database   files   value   system   source   used   server   license   form   page   order   case   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.