T-SQL SSIS Data Cleansing AdventureWorks data -- 2
Budget: $10 – $30 USD
Add data cleansing to T-SQL scripts for peoples demographic data. Some examples to check for are format for zip code, email address, and proper capitalization. For records with errors add those to the table for InValid records with a comment of what the error is. You can add a column to that table to add the error type. For records that are valid add those to a table for valid records. I have created the tables and have the create scripts for those. I have also created some data cleansing like checking for duplicates. Make recommendations on what further data cleansing should be added. There might be a data cleansing project for AdventureWorks on the web or github you could use that and make any comments on it you think wold be better. Message me and I will send you the scripts I have so far. I'm using AdventureWorks2008R2 and AdventureWorksDW2008R2 from the Microsoft downloads. I will send you the download link if you message me. I need the project complete in 24 hours.
I have some web site links that show code for some data cleansing as listed below.
Remove irrelevant data
Standardize capitalization
Convert data type
Clear formatting
Fix errors
Handle missing values
Remove irrelevant data
Remove duplicate data
Fix structural errors
Do type conversion
Handle missing data
Deal with outliers
Standardize/Normalize data
Validate data
Column operations (Select columns, Sort columns, Drop columns, Rename columns)
Handle duplicates (Distinct, Drop duplicate)
Remove spaces (Trim, Left trim, Right trim)
Cleaning Functions for Number fields (Is numeric, Convert, Round, Trunc)
Cleaning Functions for Text fields (Capitalize, Lower, Upper, Sub strings, Reverse, Split string)
Cleaning Functions for Date fields (Cast to date, Convert date, Extract)
Handle missing values (Fill NA, Drop NA, If null)
Coding verification (Encode, Decode)
Any data type (Left pad, Right pad, Length)
Advanced functions to normalize data: JSON Normalize, Pivot, Unpivot
Please reply with:
The time of day that you are available. I am available 7am to 10pm UTC-06:00 Central Time US & Canada. Do you speak English? Are you available to login remote and work on my remote machine. What remote login app do you want to use to work remote. When you can complete the project? I need the project complete in 48 hours.
Confidentiality.
a) No Use. Recipient agrees not to use the Confidential Information in any way.
b) No Disclosure. Recipient agrees to use its best efforts to prevent and protect the Confidential Information, or any part thereof.
c) Protection of Secrecy. Recipient agrees to take all steps reasonably necessary to protect the secrecy of the Confidential Information, and to prevent the Confidential Information from falling into the public domain or into the possession of unauthorized persons.
d) Scope. The scope of Confidentiality is deemed to be in all contracts present past and future with the Parties to this request
I have some web site links that show code for some data cleansing as listed below.
Remove irrelevant data
Standardize capitalization
Convert data type
Clear formatting
Fix errors
Handle missing values
Remove irrelevant data
Remove duplicate data
Fix structural errors
Do type conversion
Handle missing data
Deal with outliers
Standardize/Normalize data
Validate data
Column operations (Select columns, Sort columns, Drop columns, Rename columns)
Handle duplicates (Distinct, Drop duplicate)
Remove spaces (Trim, Left trim, Right trim)
Cleaning Functions for Number fields (Is numeric, Convert, Round, Trunc)
Cleaning Functions for Text fields (Capitalize, Lower, Upper, Sub strings, Reverse, Split string)
Cleaning Functions for Date fields (Cast to date, Convert date, Extract)
Handle missing values (Fill NA, Drop NA, If null)
Coding verification (Encode, Decode)
Any data type (Left pad, Right pad, Length)
Advanced functions to normalize data: JSON Normalize, Pivot, Unpivot
Please reply with:
The time of day that you are available. I am available 7am to 10pm UTC-06:00 Central Time US & Canada. Do you speak English? Are you available to login remote and work on my remote machine. What remote login app do you want to use to work remote. When you can complete the project? I need the project complete in 48 hours.
Confidentiality.
a) No Use. Recipient agrees not to use the Confidential Information in any way.
b) No Disclosure. Recipient agrees to use its best efforts to prevent and protect the Confidential Information, or any part thereof.
c) Protection of Secrecy. Recipient agrees to take all steps reasonably necessary to protect the secrecy of the Confidential Information, and to prevent the Confidential Information from falling into the public domain or into the possession of unauthorized persons.
d) Scope. The scope of Confidentiality is deemed to be in all contracts present past and future with the Parties to this request