Kill NULLs – a Simple macro to save time when importing data from SQL Server

Posted on March 6th, 2017 in VBA Macros - 10 comments

As part of a my ongoing consulting gig, I often run painfully long queries on SQL Server to fetch data. This data obviously ends up in Excel for further analysis. Now, some of these queries return NULL values in several columns (did I tell you that the queries have a gazillion left joins on them, oh yeah, they do). Although technically NULL is nothing, when you import this data to Excel, we get the text value NULL in the cells. And I don’t need these NULL values messing up all the calculations and pivots.

SNAG-0077

Of course, we can go ahead and use the isnull() SQL function to deal with them at the query level. But since the queries have 100s of columns and used by various teams for different purposes, changing them causes a lot of pain. So I did what any sensible Excel user would do. Just kill those NULLs mercilessly once they are in Excel.

How to get rid of all NULLs?

Simple. Find replace. Just press CTRL+H and enter NULL as find value, replace with nothing, check “Match entire cell contents” option and viola. NULLs are gone.

Of course, doing this NULL Kill find replace can quickly get tiring and dull. So I went ahead and wrote a one line macro that does this and stuck this macro on the quick access toolbar. Now, whenever there is some new query data, I just press this button, play swoooosh sound in my mind and smile.

Here is the macro, incase you deal with the same problem everyday.


Sub killNull()
    Dim rng As Range
    
    Set rng = ActiveCell.CurrentRegion
    
    rng.Replace "NULL", "", LookAt:=xlWhole
End Sub

Here is instruction on how to add this macro to your personal macros workbook and how to add it to QAT or Ribbon.

Check out more short & sweet macros to save time.

Dirty data distressing daily?

If you deal with dirty data, please share examples of your problems in the comments. I am always looking for new material / ideas to discuss on the blog. Alternatively, if you have a smart way to deal with dirty data, post it in the comments. I am always looking for things to learn.

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

10 Responses to “Kill NULLs – a Simple macro to save time when importing data from SQL Server”

  1. Duncan says:

    I know this doesn't apply to your SQL story but for dirty data Flash Fill is a gift for 2016. Try it: it even learns from its own mistakrs.

  2. james says:

    it would be nice if it also created a mapped matrix of null value coordate locations within the dataset so as not to lose the information which could also be revelant to some forms of analysis

  3. Sarah says:

    A common dirty data issue I have is a Crystal format report. It looks pretty, with data grouped by person, location, etc. But it is nowhere near the flat file format I need for analytics.

    Luckily excel can help here to! Indirect, Offset, and Vlookups are helpful here.

  4. Thomas Wäsbom says:

    Two things i always have to do when importing data from our sql server to Excel:
    Adjusting the date format which comes as yyyy-mm-dd hh-mm-ss.xxx
    Have to remove the .xxx part in order to work with dates.
    Number two is that Excel treat our unit serial number as number, (ex 54025552 00256). Need to have it as text.

  5. Jason Owens says:

    Why not simply ISNULL on the server side?

  6. Amarnath says:

    Simple Yet Powerful Weapon to Kill all the Dirty Stuffs...

  7. minimalist says:

    I want to replace all "*" charcacters with "s" and all ".." with "dd" in my worksheet "PCR Plate" and include this code in an existing macro.

    I tried:

    Sheets("PCR Plate").Select

    Dim rng As Range
    Set rng = activeCell.CurrentRegion

    rng.replace "~*", "s", LookAt:=xlWhole
    rng.replace "..", "dd", LookAt:=xlWhole

    This does not replace anything. What is the proper syntax?

    Thank you!

    • Kuldeep Mishra says:

      Hi Mini,

      Use below macro..

      Sub test()
      Dim rng As Range
      Set rng = ActiveSheet.UsedRange
      With rng
      .Replace "~*", ""
      .Replace "..", "dd"
      End With

      End Sub
      In your case activesheet is PCR PLATE.

  8. Walt says:

    If you looking for a non VBA solution, connect power query to database then filter out or replace null values before loading the data into Excel.

Leave a Reply