Data cleaning: Difference between revisions

Data cleaning (edit)

20 bytes removed , 9 February 2015

m

14,970

edits

@@ Line 18: / Line 18: @@
 #* MySQL solution: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}}
 #* EXCEL: {{kbd | key =<nowiki>=EXACT(A2, "NULL")</nowiki>}}
-# empty value:
+# find records with empty value:
-#* find records with empty value:
+#* MySQL: {{kbd | key = SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0;}} {{Exclaim}} SQL query with the condition {{kbd | key =SELECT * FROM table_name WHERE column_name IS NOT NULL}} includes empty value
-#** MySQL: {{kbd | key = SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0;}} {{Exclaim}} SQL query with the condition {{kbd | key =SELECT * FROM table_name WHERE column_name IS NOT NULL}} includes empty value
+# find records without NULL or empty value:
-#* find records without NULL or empty value:
+#* MySQL: {{kbd | key =<nowiki>SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0;</nowiki>}}
-#** MySQL: {{kbd | key =<nowiki>SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0;</nowiki>}}
 #  Excel starting date: 1900/1/0 (converted time formatted value from 0), 1900/1/1 (converted time formatted value from 1), 1900/1/2 ...
 #* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format