{"id":122,"date":"2025-11-05T21:53:47","date_gmt":"2025-11-05T21:53:47","guid":{"rendered":"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/?page_id=122"},"modified":"2025-11-05T21:53:47","modified_gmt":"2025-11-05T21:53:47","slug":"cleaning-tabular-data-using-talend","status":"publish","type":"page","link":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/cleaning-tabular-data-using-talend\/","title":{"rendered":"Cleaning Tabular Data using Talend"},"content":{"rendered":"\n

Talend Data Preparation Lab \u2014 Cleaning \u201ccustomers.xlsx\u201d<\/strong><\/p>\n\n\n\n

Duration: 60\u201390 minutes | Difficulty: Beginner to Intermediate<\/p>\n\n\n\n

Overview & Learning Outcomes<\/h1>\n\n\n\n

In this lab you will import an Excel file into Talend Data Preparation (TDP), profile its quality, add column metadata, build a reproducible cleaning recipe, and export a clean version of the dataset. An optional section shows how to implement the same logic in Talend Studio (Data Integration) as a batch job.<\/p>\n\n\n\n

Learning outcomes:<\/p>\n\n\n\n

    \n
  1. Import and profile a dataset in Talend Data Preparation (TDP).<\/li>\n\n\n\n
  2. Add and curate metadata (column names, types, semantic types).<\/li>\n\n\n\n
  3. Build a step-by-step cleaning recipe that fixes real data issues.<\/li>\n\n\n\n
  4. Export a clean dataset and summarize before\/after quality metrics.<\/li>\n\n\n\n
  5. Optionally, reproduce the logic in Talend Studio as a batch job.<\/li>\n<\/ol>\n\n\n\n

    Files & Setup<\/h1>\n\n\n\n

    You will use the provided file: customers.xlsx (sheet \u201csheet1\u201d, ~6,040 rows, 16 columns).<\/p>\n\n\n\n

    Open Talend Data Preparation (Talend Cloud or on\u2011premise): sign in \u2192 open the Data Preparation app.<\/p>\n\n\n\n

    Data costumers.xlsx : https:\/\/docs.google.com\/spreadsheets\/d\/1woGsb2ArNN0QSJUgklQRylfzz47E9HQ-\/edit?usp=sharing&ouid=106532920956410672496&rtpof=true&sd=true<\/a><\/p>\n\n\n\n

    Part A \u2014 Import & Profile the Dataset (TDP)<\/h1>\n\n\n\n
      \n
    • From the TDP home page, click \u201cDatasets\u201d in the left menu.<\/li>\n\n\n\n
    • Click \u201cAdd dataset\u201d (or \u201cAdd\u201d \u2192 \u201cFrom local file\u201d).<\/li>\n\n\n\n
    • Select the file customers.xlsx and upload it.<\/li>\n\n\n\n
    • If prompted for sheet selection, choose \u201csheet1\u201d. Keep \u201cFirst row is header\u201d enabled.<\/li>\n\n\n\n
    • Name the dataset customers_raw and click \u201cCreate\u201d (or \u201cAdd\u201d).<\/li>\n\n\n\n
    • Open the dataset by clicking its name. The grid view appears with column quality bars.<\/li>\n\n\n\n
    • Open the right\u2011hand \u201cStatistics\u201d \/ \u201cDetails\u201d panel (if hidden, use the panel toggle).<\/li>\n\n\n\n
    • Scroll columns and observe the quality bars (valid \/ empty \/ invalid) and top value distributions.<\/li>\n\n\n\n
    • Record short notes about issues you see (examples below).<\/li>\n<\/ul>\n\n\n\n

      Typical issues you will see in this file:<\/p>\n\n\n\n

        \n
      • Email has missing values (~5.9%) and duplicate emails (~25).<\/li>\n\n\n\n
      • Phone contains many entries that are not 10 digits after removing punctuation (~700 invalid).<\/li>\n\n\n\n
      • Zip requires leading\u2011zero padding for many rows (~950).<\/li>\n\n\n\n
      • State should be 2\u2011letter uppercase codes; verify and standardize.<\/li>\n\n\n\n
      • subscription_date is parseable but formats may be inconsistent.<\/li>\n\n\n\n
      • marital_status has ~24% missing values.<\/li>\n<\/ul>\n\n\n\n

        Deliverable A: 5\u20138 lines describing the main data quality issues you observed, with 1\u20132 screenshots.<\/p>\n\n\n\n

        Part B \u2014 Add Metadata & Semantic Types<\/h1>\n\n\n\n

        Rename columns, set data types, and assign semantic types.<\/p>\n\n\n\n

          \n
        1. 1) Rename columns (Right\u2011click column header \u2192 \u201cRename\u201d):<\/li>\n<\/ol>\n\n\n\n
          Original name<\/td>New name<\/td><\/tr>
          Id<\/td>id<\/td><\/tr>
          First_Name<\/td>first_name<\/td><\/tr>
          Last_Name<\/td>last_name<\/td><\/tr>
          Gender<\/td>gender<\/td><\/tr>
          Age<\/td>age<\/td><\/tr>
          Occupation<\/td>occupation<\/td><\/tr>
          MaritalStatus_Out<\/td>marital_status<\/td><\/tr>
          Salary_Out<\/td>salary_band<\/td><\/tr>
          Address<\/td>address<\/td><\/tr>
          City<\/td>city<\/td><\/tr>
          State<\/td>state<\/td><\/tr>
          Zip<\/td>zip<\/td><\/tr>
          Phone<\/td>phone<\/td><\/tr>
          Email<\/td>email<\/td><\/tr>
          SubDate<\/td>subscription_date<\/td><\/tr>
          Number_of_rentals<\/td>rentals_count<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
            \n
          1. 2) Set column data types:<\/li>\n\n\n\n
          2. – id \u2192 Integer<\/li>\n\n\n\n
          3. – first_name, last_name, full_name \u2192 Text<\/li>\n\n\n\n
          4. – gender, age, occupation, marital_status, salary_band, address, city, state, zip, email, phone \u2192 Text<\/li>\n\n\n\n
          5. – subscription_date \u2192 Date (set format: yyyy-MM-dd)<\/li>\n\n\n\n
          6. – rentals_count \u2192 Integer (non\u2011negative)<\/li>\n\n\n\n
          7. 3) Assign semantic types (Column menu \u2192 \u201cSemantic type\u201d):<\/li>\n\n\n\n
          8. – email \u2192 Email; state \u2192 US state code (or Code); zip \u2192 Zip\/Postal Code; subscription_date \u2192 Date.<\/li>\n<\/ol>\n\n\n\n

            Deliverable B: Screenshot of the schema panel and 1\u20132 lines justifying choices (e.g., zip stored as text to preserve leading zeros).<\/p>\n\n\n\n

            Part C \u2014 Build the Cleaning Recipe (TDP)<\/h1>\n\n\n\n

            Apply the following steps in order. Each action is recorded in the \u201cRecipe\u201d panel. You can reorder steps by dragging them if needed.<\/p>\n\n\n\n

            Step C1 \u2014 Trim & Normalize Text<\/h2>\n\n\n\n
              \n
            1. On columns first_name, last_name, city, occupation: Column \u25b8 Clean \u25b8 Trim.<\/li>\n\n\n\n
            2. Then Column \u25b8 Case \u25b8 Proper case (Title case) on first_name, last_name, city, occupation.<\/li>\n\n\n\n
            3. On email: Column \u25b8 Clean \u25b8 Trim, then Column \u25b8 Case \u25b8 Lowercase.<\/li>\n<\/ol>\n\n\n\n

              Step C2 \u2014 Standardize US State Codes<\/h2>\n\n\n\n
                \n
              • On state: Column \u25b8 Case \u25b8 Uppercase.<\/li>\n\n\n\n
              • Column \u25b8 Validate \u25b8 \u201cValue is in list\u201d (paste the 2\u2011letter codes from Appendix A). Mark non\u2011matches as Invalid.<\/li>\n\n\n\n
              • Optionally: Column \u25b8 Find & replace to fix frequent variants (e.g., \u2018Calif.\u2019\u2192\u2018CA\u2019, \u2018N.Y.\u2019\u2192\u2018NY\u2019). Add each replacement as a separate action so it is documented.<\/li>\n<\/ul>\n\n\n\n

                Step C3 \u2014 Fix ZIP Codes (Preserve Leading Zeros)<\/h2>\n\n\n\n
                  \n
                • On zip: Column \u25b8 Change type \u25b8 Text (String).<\/li>\n\n\n\n
                • Column \u25b8 Pad left \u25b8 Length = 5, Padding char = \u20180\u2019.<\/li>\n\n\n\n
                • Optional: Validate \u25b8 \u201cMatches pattern\u201d with regex ^\\d{5}$ to mark non\u20115\u2011digit ZIPs invalid.<\/li>\n<\/ul>\n\n\n\n

                  Step C4 \u2014 Normalize Phone Numbers<\/h2>\n\n\n\n

                  Goal: keep only digits, validate length = 10, and format as (XXX) XXX-XXXX.<\/p>\n\n\n\n

                    \n
                  • Create helper column phone_digits: Column \u25b8 Duplicate. Rename the duplicate to phone_digits.<\/li>\n\n\n\n
                  • On phone_digits: Column \u25b8 Replace with RegEx. Pattern: \\D\u00a0\u00a0Replacement: (empty). This removes all non\u2011digits.<\/li>\n\n\n\n
                  • Validate: Column \u25b8 Compute length (or Validate \u25b8 Matches pattern ^\\d{10}$). Mark non\u201110\u2011digit as Invalid.<\/li>\n\n\n\n
                  • Create formatted column phone_formatted: Add column \u25b8 Formula (or Derived column). Build:\u00a0\u00a0‘(‘ + SUBSTR(phone_digits,1,3) + ‘) ‘ + SUBSTR(phone_digits,4,3) + ‘-‘ + SUBSTR(phone_digits,7,4)\u00a0<\/li>\n\n\n\n
                  • Replace original phone with phone_formatted (Column \u25b8 Replace values from another column), or keep both and later delete helpers.<\/li>\n\n\n\n
                  • Delete helper columns phone_digits and phone_formatted after verifying results.<\/li>\n<\/ul>\n\n\n\n

                    Step C5 \u2014 Validate & De\u2011duplicate Emails<\/h2>\n\n\n\n