Converting XLS worksheets into separate (CSV) files

Today I had a large Excel file (workbook) with many tabs (worksheets), and I needed each tab in a separate file as they needed to be shared with different people (who should not be able to see the other tabs).

Yes, you could make X copies of the XLS file and then manually delete all sheets but one in each file, but hey, that is for people who enjoy pain. The much easier way is to do it with a simple macro. To split an XLS file into separate XLS files for each tab, simply open the XLS file you want to split. Then open the VBA editor (press ALT + F11) and copy the following VBA code into the a new module (go under "Insert" menu, and select "Module"). Close VBA Editor (no need to save), and back in XLS file, go under "View", select "Macros" and "View macros". Now simply run the new macro called ConvertTabsToFiles. No need to save the macro. Just run it.

Sub ConvertTabsToFiles()
  Dim currPath As String
  currPath = Application.ActiveWorkbook.Path
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".xlsx", CreateBackup:=False
    Application.ActiveWorkbook.Close False
  Next
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub


You should now have a new XLS file for each worksheet in the original XLS file.  If your XLS file is of extension .xls and not .xlsx, simply change the xlsx extension to xls in the code above @ line 8. Otherwise your new files will be of the wrong format (but still readable)

If you want to save several sheets to CSV, simply use this line instead of the one saving to xlsx
Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False

And if you run into issues that your file "cannot be saved in macro-free workbooks", simply save to the file format called xlsm (XLS macro-enabled workbook).

Nifty,
Thomas

Random date generation

Today I needed to generate a large (bulk) set of random dates for a project at work.  Probably a million ways to do this, but I turned to Excel. I've used the RAND function before and was expecting to use something ugly like this for month:
=INT(RAND()*12)+1

But as I'm always looking for new cool ways to be more efficient, I stumbled upon this cool function in XLS. Not sure when it was introduced, but it is cool. Simply use RANDBETWEEN in combination with the DATE function.  To generate a random date between 2 dates, simply use:
=RANDBETWEEN(DATE(2014,1,1),DATE(2014,7,15))

Note that new random data is calculated every time calculations are performed, like when you add a new formula. RAND works the same way.

Nifty,

Thomas

JSON file validator locally

JSON files are great for many things, but they can also be the root of very strange issues. Hence, you should always validate your JSON files whenever you update them to ensure you don't spend hours debugging issues that a really caused by a simple error in a JSON file (yes, I talk from experience).

Validating a JSON file is easy. There are many good online ones, like http://jsonlint.com/. However, if you want to validate many files, it is cumbersome, or if you have sensitive data in your JSON file, you may not want to copy/paste that to a public site.

The solution is simple. Simply install a JSON validator / checker locally. And they also allow for “pretty printing” of your JSON too.  There are a few ways to skin this cat.

Steps (jsonlint):

1. Install NPM. Part of nodejs. Get it from http://nodejs.org/download/
2. Get jsonlint from https://www.npmjs.org/package/jsonlint
3. Reboot (if on Windows)

To run simply open a prompt and type “jsonlint FILE” or "jsonlint -h" to see options.
For multiple files, script it. E.g. on Windows use “for %i in (content\*.json) do jsonlint -q %i”

Steps (Cygwin):

1. Make sure that Python is installed.

To run simply open a Cygwin prompt and type “cat FILE | python -mjson.tool”.

For multiple files, script it. E.g. in BASH “for FILE in content/*.json \n do \n cat $FILE | python -mjson.tool \n done”

Nifty,
Thomas

Cygwin DOS path names issue on Windows 8

I finally upgraded to a Windows 8 machine and noticed that the Cygwin command prompt gave me issues with DOS style path names. This is very easily fixable by using an environment variable called CYGWIN.

However, I already had the environment variable named CYGWIN set, but it still didn't work. Turns out I was using the incorrect case. CYGWIN MUST BE UPPERCASE. Hopefully this quick post can solve your headache too.

Issue:
Every time you use a DOS style path (backwards slash) you get an error like this
cygwin warning:
  MS-DOS style path detected: C:\Intel
  Preferred POSIX equivalent is: /cygdrive/c/Intel
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames

Fix:
In a DOS command prompt (cmd) type
set cygwin

I got the response
cygwin=nodosfilewarning

This is wrong. Not sure why the cygwin word is lowercase. Should be UPPERCASE CYGWIN. To fix, press WINDOWS keys, type "environment" to open the system environment variables window. Under system variables, create a new one called CYGWIN and set to nodosfilewarning. And delete the older "cygwin" (lowercase) one.

Next time you open a DOS prompt and type "set cygwin" you should see
CYGWIN=nodosfilewarning

Now all the DOS errors should go away.
Nifty,
Thomas

Setting function keys back to normal on HP Envy Notebook

Just got a new HP ENVY 15 and so far I love it. Well, except for one little thing that drove my crazy.

Not sure what those pesky engineers at HP were thinking when they decided to change the default way the function (fn) keys work on the newer HP ENVY laptops. By default you do not need to press the (fn) key to get function key functionality.

Example: If you press F5 (even in a browser window) it turns the backlit keyboard on and off, rather than refreshing the browser. Really???

This "amazing" technology is called "Action Keys Mode", and the good news is that it is very easily turned off to get your laptop back to working the standard way of having to press the fn key.

Steps to fix:
- Shut Down your computer completely. Don't put in Sleep or Hibernate
- Press Power button to start
- Immediately press F10 key a few times
- This will start the BIOS
- Go to 3rd page (System Configuration). Press right arrow key to go to next page
- Select the "Action Keys Mode". Press ENTER. Select DISABLE
- Save and Exit

Nifty,
Thomas

Useful RegEx expression for text manipulation

When doing text manipulation, there are a number of regular expressions (RegEx) that I tend to use over and over again. Maybe you'll find them useful too.

Problem	Find	Replace	Example: Before	Example: After
Move text in brackets from end of line to beginning of line	^(.*) (\[.*\])$	\2: \1	Yada yada [BC4] Yada yada [POS123]	[BC4]: Yada yada [POS123]: Yada yada
Convert dates from MM-DD-YYYY to YYYYMMDD	^(..)-(..)-(....)	\3\1\2	11-30-1998 01-23-2004	19981130 20040123
Clean phone numbers (remove space, paren, dash, period)	[ \-\.\(\)]	N/A	999.888.7777 (555) 444-3333	9998887777 5554443333

Nifty,
/T

Deleting multiple empty Google contact groups

My beloved Google Contacts suddenly have ~300 empty "Starred in Android" groups. Not sure how or why they showed up, but many people who have the very same problem are pointing fingers at Nook, and yes, I was using a Nook at the time it occurred.

Anyway, there does not seem to be an easy way to delete all these empty contact groups except manually one by one, which I simply refuse to do. So I wrote a simple script for Google Apps Script to solve the problem. The script is listed below.

Disclaimer: I have tested the script many ways, but please use at your own risk !

To run simply:
- Go to https://script.google.com,
- Start a blank project, and copy/paste the script below
- Run once with ContactsApp.deleteContactGroup commented out (as is)
- Press Ctrl-Enter to view logs to make sure everything looks fine.
- Uncomment the ContactsApp.deleteContactGroup line, and run again to delete empty groups.
- If you want to remove all empty groups independent of name, uncomment the line referencing "Starred in Android"

Nifty,
Thomas

// ============================================================
// What:  Deletes all empty "Starred in Android' contact groups from your Google contacts
// How:   1) Run once to verify that correct groups will be deleted.
//           Review log file (press ctrl-enter)
//        2) Uncomment deleteContactGroup line below and rerun.
//
// Jan 2014, Thomas Gemal
// ============================================================
function DeleteAllEmptyGroups() {
  // Grab all contact groups
  var myContactGroups = ContactsApp.getContactGroups();

  // Use this to limit number of groups processed if performance is an issue
  var MAXCOUNT = 1000;
  var count = 0;
  
  // For each contact group
  for(var i = 0; i < myContactGroups.length; i++) { 

    // Only delete MAXCOUNT groups at a time (since execution can take long)
    if (count == MAXCOUNT) {
      break;
    }
    
    // Get name of a contact group
    var group = myContactGroups[i];
    var group_name = group.getName();

    // Keep this code to only deal with "Starred in Android". 
    // To delete all empty groups, simply comment this 'if' out
    if (group_name != "Starred in Android") {
      Logger.log(Utilities.formatString("%s: Skipped. Not SiA", group_name));
      continue;
    }

    // Get number of contacts for a group
    var num_contacts = ContactsApp.getContactsByGroup(group).length;

    // We are only looking for empty groups
    if (num_contacts == 0) {
      // Ignore special groups
      if (group_name.substring(0,13) != "System Group:") {
        // Log that we want to delete
        Logger.log(Utilities.formatString("%s (%d):  DELETE",group_name,num_contacts));
        count++;
      
        // !!!!! Uncomment line below to physically delete the groups  !!!!!
        // Make sure you have first verified that list to delete is OK
        // ContactsApp.deleteContactGroup(group);
      } else {
        Logger.log(Utilities.formatString("%s (%d):  Skipped. System group",group_name,num_contacts));
      }   
    } else {
      Logger.log(Utilities.formatString("%s (%d):  Skipped. Not empty",group_name,num_contacts));
    }   
  }
  Logger.log(Utilities.formatString("Empty groups processed: %d", count));
}