We help IT Professionals succeed at work.

Direction on training to cleanse data from excel into a pilot sql database

Patricia Timm
New to data cleansing. I would like to get training on how to best cleanse data coming from an excel sheet into an sql database (placed in a pilot table first). What courses would you recommend to take in order to develop this skill? IS most of the work performed using an application like Tableau or another popular application? Thanks in advance for direciton.
Watch Question

Distinguished Expert 2017
I'm not sure why you would need additional software.  Queries should be all you need.  Maybe something complex might need some procedural code.

Cleansing data is always a custom operation.  What are you looking for? duplication, invalid values, missing values?  I'm sure there are books out there. There are books on everything but start by defining what your objective is and write a few queries to see if you can identify problems.

Keep in mind that you should save your work at every stage so if what you are doing with the current query doesn't work as expected, you can go back to a clean, intermediate set of data and move forward from there.

Document the steps as you take them.

Remember, cleansing is a process. You need to start with a good sample of data and work with it until you are happy.  By that time, the data will almost certainly be old so at a minimum, you wll need to go through the current set of data using the steps you performed on the first set one at a time until you get a clean result.  If you are lucky, you can ultimately automate the cleaning process.  Remember, it is not likely that you will be able to accomplish everything in one query so don't worry about efficiency until you get to the point of automation.


Distinguished Expert 2017
You're welcome.  Good luck.