Data cleaning - Revision history

Planetoid: /* Verify if value is number in MySQL */

2025-11-25T10:57:09Z

Verify if value is number in MySQL

← Older revision		Revision as of 18:57, 25 November 2025
Line 315:		Line 315:
	* Check if a value is integer e.g. 1234567		* 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>		** 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>

			* Find the records which the value of `my_column` is not exactly 8 digits {{code \| code = SELECT * FROM my_table WHERE LENGTH(my_column) != 8 OR my_column NOT REGEXP '^[0-9]{8}$'}}
			** The `LENGTH()` function checks if the string length is not 8 characters
			** The `REGEXP '^[0-9]{8}$'` pattern validates that the value contains exactly 8 digits from start (^) to end ($)
			** Using both conditions ensures catching values with correct length but non-numeric characters, as well as incorrect lengths

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

Planetoid at 03:31, 15 July 2025

2025-07-15T03:31:10Z

← Older revision		Revision as of 11:31, 15 July 2025
Line 467:		Line 467:
	== References ==		== References ==
	<references/>		<references/>

			{{Template:Data factory flow}}

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

Planetoid: /* Time data: Data was generated in N years */

2024-04-08T05:39:40Z

Time data: Data was generated in N years

← Older revision		Revision as of 13:39, 8 April 2024
Line 366:		Line 366:

	Abnormal values		Abnormal values
	* {{code \| code = 1970-01-01 08:00:00}} (converted time formatted value from {{code \| code =August 3, 2017}}) caused by the string contains special ~~symbols~~		* {{code \| code = 1970-01-01 08:00:00}} (converted time formatted value from {{code \| code =August 3, 2017}}) caused by the string contains special characters e.g. [https://en.wikipedia.org/wiki/Left-to-right_mark left-to-right mark (LRM) ]

	Check if the date valid		Check if the date valid

Planetoid: /* Time data: Data was generated in N years */

2024-04-08T04:35:31Z

Time data: Data was generated in N years

← Older revision		Revision as of 12:35, 8 April 2024
Line 346:		Line 346:
	=== Time data: Data was generated in N years ===		=== 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])		Define the abnormal values of the time data ([http://en.wikipedia.org/wiki/Time_series time series])
	* ~~Verfiy~~ the data were generated in N years. Possible abnormal values: {{code \| code = 0001-01 00:00:00}} occurred in MySQL {{code \| code = datetime}} type. e.g.		* Verify the data were generated in N years. Possible abnormal values: {{code \| code = 0001-01 00:00:00}} occurred in MySQL {{code \| code = datetime}} type. e.g.

	* ~~Verfiy~~ the data were not newer than today		* Verify the data were not newer than today

	* ~~Verfiy~~ the year of data were not {{kbd \| key=1900}} if the data were imported from Microsoft Excel file. Datevalue<ref>[https://support.microsoft.com/zh-tw/office/datevalue-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE 函數 - Office 支援]</ref> was started from the year {{kbd \| key=1900}} e.g.		* Verify the year of data were not {{kbd \| key=1900}} if the data were imported from Microsoft Excel file. Datevalue<ref>[https://support.microsoft.com/zh-tw/office/datevalue-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE 函數 - Office 支援]</ref> was started from the year {{kbd \| key=1900}} e.g.
	** {{code \| code = 1900/1/0}} (converted time formatted value from 0),		** {{code \| code = 1900/1/0}} (converted time formatted value from 0),
	** {{code \| code = 1900/1/1}} (converted time formatted value from 1)		** {{code \| code = 1900/1/1}} (converted time formatted value from 1)

	* ~~Verfiy~~ the diversity of data values e.g. [https://en.wikipedia.org/wiki/Variance Variance]		* Verify the diversity of data values e.g. [https://en.wikipedia.org/wiki/Variance Variance]

	Find the normal values:		Find the normal values:
Line 364:		Line 364:
	** {{code \| code = SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` <= CURRENT_TIMESTAMP);}}		** {{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)		*** You need to check the {{code \| code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the abnormal data (timezone issue)

			Abnormal values
			* {{code \| code = 1970-01-01 08:00:00}} (converted time formatted value from {{code \| code =August 3, 2017}}) caused by the string contains special symbols

	Check if the date valid		Check if the date valid

Unknown user: /* Check list */

2023-09-26T06:04:24Z

Check list

← Older revision		Revision as of 14:04, 26 September 2023
Line 2:		Line 2:
	== Check list ==		== Check list ==

	* ~~row~~ count		* Row count: The number of data entries is a fundamental item for data verification and is easy to observe and check. For instance, one can compare the number of entries displayed on a webpage to the number of entries after exporting to a CSV file.
	* ~~duplicate~~ data		* Duplicate data

	== Check if field value was not fulfilled ==		== Check if field value was not fulfilled ==

Unknown user: /* Tools */

2023-06-25T09:33:46Z

Tools

← Older revision		Revision as of 17:33, 25 June 2023
Line 451:		Line 451:
	== Tools ==		== Tools ==
	* {{Mac}} [https://github.com/IvanMathy/Boop IvanMathy/Boop: A scriptable scratchpad for developers. In slow yet steady progress.] ([https://apps.apple.com/us/app/boop/id1518425043?mt=12 ‎Boop on the Mac App Store]) " ... to paste some plain text and run some basic text operations on it. "		* {{Mac}} [https://github.com/IvanMathy/Boop IvanMathy/Boop: A scriptable scratchpad for developers. In slow yet steady progress.] ([https://apps.apple.com/us/app/boop/id1518425043?mt=12 ‎Boop on the Mac App Store]) " ... to paste some plain text and run some basic text operations on it. "
	* [https://gchq.github.io/CyberChef/ CyberChef]		* [https://gchq.github.io/CyberChef/ CyberChef] (source code available on [https://github.com/gchq/CyberChef github]) The Cyber Swiss Army Knife - a web app for encryption, encoding, compression and data analysis

	== Further reading ==		== Further reading ==

Unknown user: /* String contains special characters */

2023-06-21T08:32:48Z

String contains special characters

← Older revision		Revision as of 16:32, 21 June 2023
Line 386:		Line 386:
	* [[Return symbol]]		* [[Return symbol]]
	* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]		* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]
			* [https://www.fileformat.info/info/unicode/char/200f/index.htm Unicode Character 'RIGHT-TO-LEFT MARK' (U+200F)]
			* [https://www.fileformat.info/info/unicode/char/200f/index.htm Unicode Character 'RIGHT-TO-LEFT MARK' (U+200F)]<ref>[https://stackoverflow.com/questions/1930009/how-to-strip-unicode-chars-left-to-right-mark-from-a-string-in-php regex - How to strip unicode chars (LEFT_TO_RIGHT_MARK) from a string in php - Stack Overflow]</ref>

	== File Validation ==		== File Validation ==

Unknown user: /* Data Validation */

2023-06-06T10:13:48Z

Data Validation

← Older revision		Revision as of 18:13, 6 June 2023
Line 340:		Line 340:
	** Return 1 if the cell value is (1) Numbers (2) Numbers that are stored as text e.g. {{code \| code = <nowiki>="5"</nowiki>}}		** Return 1 if the cell value is (1) Numbers (2) Numbers that are stored as text e.g. {{code \| code = <nowiki>="5"</nowiki>}}
	** Return 0 if the cell value is (1) Text (2) Numbers in scientific (exponential) notation e.g. {{code \| code = <nowiki>1.23E+16</nowiki>}} (3) Decimal numbers e.g. {{code \| code = <nowiki>3.141592654</nowiki>}} (4) Negative numbers		** Return 0 if the cell value is (1) Text (2) Numbers in scientific (exponential) notation e.g. {{code \| code = <nowiki>1.23E+16</nowiki>}} (3) Decimal numbers e.g. {{code \| code = <nowiki>3.141592654</nowiki>}} (4) Negative numbers

	~~=== Verify the file format of downloaded file ===~~
	* PDF file format: [https://stackoverflow.com/questions/16152583/tell-if-a-file-is-pdf-in-bash Tell if a file is PDF in bash - Stack Overflow]

	=== Time data: Validate the data format ===		=== Time data: Validate the data format ===
Line 389:		Line 386:
	* [[Return symbol]]		* [[Return symbol]]
	* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]		* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]

			== File Validation ==

			=== Verify the file format of downloaded file ===
			* PDF file format: [https://stackoverflow.com/questions/16152583/tell-if-a-file-is-pdf-in-bash Tell if a file is PDF in bash - Stack Overflow]

	== Find and remove duplicates ==		== Find and remove duplicates ==

Unknown user: /* Time data: Validate the data format */

2023-06-06T10:12:50Z

Time data: Validate the data format

← Older revision		Revision as of 18:12, 6 June 2023
Line 340:		Line 340:
	** Return 1 if the cell value is (1) Numbers (2) Numbers that are stored as text e.g. {{code \| code = <nowiki>="5"</nowiki>}}		** Return 1 if the cell value is (1) Numbers (2) Numbers that are stored as text e.g. {{code \| code = <nowiki>="5"</nowiki>}}
	** Return 0 if the cell value is (1) Text (2) Numbers in scientific (exponential) notation e.g. {{code \| code = <nowiki>1.23E+16</nowiki>}} (3) Decimal numbers e.g. {{code \| code = <nowiki>3.141592654</nowiki>}} (4) Negative numbers		** Return 0 if the cell value is (1) Text (2) Numbers in scientific (exponential) notation e.g. {{code \| code = <nowiki>1.23E+16</nowiki>}} (3) Decimal numbers e.g. {{code \| code = <nowiki>3.141592654</nowiki>}} (4) Negative numbers

			=== Verify the file format of downloaded file ===
			* PDF file format: [https://stackoverflow.com/questions/16152583/tell-if-a-file-is-pdf-in-bash Tell if a file is PDF in bash - Stack Overflow]

	=== Time data: Validate the data format ===		=== Time data: Validate the data format ===

Unknown user: /* Tools */ + [https://gchq.github.io/CyberChef/ CyberChef]

2023-03-04T07:06:02Z

Tools: + [https://gchq.github.io/CyberChef/ CyberChef]

← Older revision		Revision as of 15:06, 4 March 2023
Line 443:		Line 443:

	== Tools ==		== Tools ==
	* [https://github.com/IvanMathy/Boop IvanMathy/Boop: A scriptable scratchpad for developers. In slow yet steady progress.] ([https://apps.apple.com/us/app/boop/id1518425043?mt=12 ‎Boop on the Mac App Store]) " ... to paste some plain text and run some basic text operations on it. "		* {{Mac}} [https://github.com/IvanMathy/Boop IvanMathy/Boop: A scriptable scratchpad for developers. In slow yet steady progress.] ([https://apps.apple.com/us/app/boop/id1518425043?mt=12 ‎Boop on the Mac App Store]) " ... to paste some plain text and run some basic text operations on it. "
			* [https://gchq.github.io/CyberChef/ CyberChef]

	== Further reading ==		== Further reading ==