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.
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.
11 Responses to “Kill NULLs – a Simple macro to save time when importing data from SQL Server”
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.
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
Hi James..
Check out http://chandoo.org/wp/2017/02/10/find-and-extract-results/ and customize the code for your needs.
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.
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.
Why not simply ISNULL on the server side?
Simple Yet Powerful Weapon to Kill all the Dirty Stuffs...
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!
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.
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.
I have a mastersheet which is CSV file having 1000000 of records and I want to split it into multiple workbook (xlsx) so that I can share it with customers separately. Records must be 50000 per workbook and all workbooks must be saved at the same path where the original master sheet is present.
It would be a great help if you can provide a solution for this.
Thanks in advance!