14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→verify the format of field value
| Line 133: | Line 133: | ||
== verify the format of field value == | == verify the format of field value == | ||
=== | === email contains @ symbol === | ||
* EXCEL: {{kbd | key =<nowiki>=IF(ISERR(FIND("@", A2, 1)), FALSE, TRUE)</nowiki>}} only check the field if contains @ symbol or not | * 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 | ** result: (1) normal condition: return TRUE; (2) exceptional condition: return '''FALSE''' if @ symbol was not found | ||
| Line 142: | Line 142: | ||
** "Returns the filtered data, or '''FALSE''' if the filter fails." quoted from [http://php.net/manual/en/function.filter-var.php PHP.net] | ** "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 of Excel === | ||
Number precision: 15 digits (Excel中最多的有效位數為15位)<ref>[https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Excel specifications and limits]</ref><ref>[http://isvincent.pixnet.net/blog/post/30267208-excel-%E9%A1%AF%E7%A4%BApi%E8%BC%83%E5%A4%9A%E7%9A%84%E6%9C%89%E6%95%88%E4%BD%8D%E6%95%B8 A2]</ref> | Number precision: 15 digits (Excel中最多的有效位數為15位)<ref>[https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Excel specifications and limits]</ref><ref>[http://isvincent.pixnet.net/blog/post/30267208-excel-%E9%A1%AF%E7%A4%BApi%E8%BC%83%E5%A4%9A%E7%9A%84%E6%9C%89%E6%95%88%E4%BD%8D%E6%95%B8 A2]</ref> | ||
| Line 150: | Line 150: | ||
* (general format 通用格式) 1.23457E+15 {{exclaim}} losing precision | * (general format 通用格式) 1.23457E+15 {{exclaim}} losing precision | ||
* (text format 文字格式) 1234567890123456 | * (text format 文字格式) 1234567890123456 | ||
=== numeric only === | |||
* PHP: [http://php.net/manual/en/function.is-numeric.php is_numeric] | |||
* MySQL: {{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> | |||
* Excel: [http://www.techonthenet.com/excel/formulas/isnumber.php ISNUMBER Function] | |||
== duplicate data == | == duplicate data == | ||