Data cleaning: Difference between revisions

== Check list ==

== Check if field value was not fulfilled  ==

</table>

==== VARCHAR and NOT allows NULL value ====

Using NULLIF() function<ref>[https://www.w3schools.com/sql/func_mysql_nullif.asp MySQL NULLIF() Function]</ref>

Validate the format of field value. Related page: [[Regular expression]]

* EXCEL: {{kbd | key =<nowiki>=IF(ISERR(FIND("@", A2, 1)), FALSE, TRUE)</nowiki>}} only check the field if contains @ symbol or not

** result: (1) normal condition: return TRUE; (2) exceptional condition: return '''FALSE''' if @ symbol was not found  

* PHP: [http://www.w3schools.com/php/filter_validate_email.asp PHP FILTER_VALIDATE_EMAIL Filter]

** "Returns the filtered data, or '''FALSE''' if the filter fails." quoted from [http://php.net/manual/en/function.filter-var.php PHP.net]

=== Number precision in Excel ===

* If the data was imported from Excel, you should notice the 15 digit precision issue.

<pre>

</pre>

MySQL:  

* Check if a value is integer e.g. 1234567

** Find the records which the value of `my_column` is numeric values entirely {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'}}<ref>[http://stackoverflow.com/questions/14343767/mysql-regexp-with-and-numbers-only regex - Mysql REGEXP with . and numbers only - Stack Overflow]</ref><ref>[https://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql How do I check to see if a value is an integer in MySQL? - Stack Overflow]</ref>

* Check if a value is integer which may contains comma and dot symbols e.g. 1,234.567 or 3.414

* The {{kbd | key=tax_id}} column is 8 digits only. Find the well-formatted {{kbd | key=tax_id}} records by using {{code | code = SELECT * FROM `tax_id` WHERE `tax_id` REGEXP '^[0-9]{8}$'}}

* [http://php.net/manual/en/function.is-numeric.php is_numeric] function

* [https://www.php.net/manual/en/function.is-int.php is_int] function

Excel & [https://www.google.com/sheets/about/ Google Sheets]:  

* Using [http://www.techonthenet.com/excel/formulas/isnumber.php ISNUMBER Function]: {{code | code = <nowiki>=INT(ISNUMBER(A1))</nowiki>}}

=== Time data: Data was generated in N years ===

Define the abnormal values of the time data ([http://en.wikipedia.org/wiki/Time_series time series])

** {{code | code = 1900/1/0}} (converted time formatted value from 0),  

** {{code | code = 1900/1/1}} (converted time formatted value from 1)

Find the normal values:  

** {{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >=  CURDATE() - INTERVAL 10 YEAR )  AND  ( `my_time_column` <= CURRENT_TIMESTAMP);}}  

*** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the abnormal data (timezone issue)

Check if the date valid

* [[Return symbol]]

* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]

== Find and remove duplicates ==

== Tools ==

== Further reading ==

== References ==

<references/>

[[Category:Spreadsheet]] [[Category:Excel]]