How to access and process data using the Toad Data Point transformation and cleanse utility

In Toad Data Point 3.7 we've rewritten the transformation and cleanse window. This new window now includes data profiling information, many more transformation rules, and a robust engine for processing larger amounts of data. The transformation and cleanse utility is acceptable from a right click menu from the Object Explorer, or from the Tools menu where you can enter your own SQL statement to generate the initial data sets, or from a right click from any Query Builder or SQL editor results grid. Now the transformation window will take the first thousand rows as a sample. And then it will generate profiling information. The statistics on the right hand panel are related to the focus row. So when you change that focus, it will change the statistics. And this data is going to help you quickly explore your data. Now you can change the sample set size by clicking on this link over here, and change the number of rows. Or request that they be random rows or all rows. Now there's eight different transformation rules that can be accessed at the top of the toolbar. You can also access these from a right click on the grid in a contextual manner. When you select the rule, the editor for that rule will show up at the bottom. And you can enter in the information that you want for the rule. In this case I'm going to find all the null values and put in NA for not applicable. Now when you click apply, it executes a rule against the sample set, it updates the profiling information over here, and it adds the rule to the Steps tab. Now the Rule tab will show you the rows affected and the sequence of the rules. Now if you find use a particular rule often then you can add that rule to the library and apply that rule to future transformation sessions. Now this rule here happens to be changing a country column from US to USA. So I can drag that over and apply it to this particular session also. When you finish defining your rules, you can generate SQL by clicking this action here. Or it's also available from the Tools bar. This will generate a single SQL statement that includes all of your rules. You can also click the Export button down here. And you can export to CSV or Excel. You can also save it to local storage by entering a name for the table. Or you can publish in Toad Intelligence Central so that others can use this new data set. It can be a snapshot or view also. Or you can send this file to automation and have it be a reoccurring action. Now the Transformation And Cleanse window does not update the original source data. Instead, it offers a flexible variety of output of your cleanse data which I have just shown you. See part two of this next series to learn about the individual rules of transformation and cleanse.