Ciaren

Clean Messy Text in a CSV

An email such as " [email protected] " will not match [email protected] in a join. Trim the spaces, then make the casing consistent before matching records.

You'll use: File Input → String Transform → String Transform → File Output.

Steps

  1. Upload your CSV on Datasets, then select it in File Input.
  2. Add String Transform with column: email and operation: strip.
  3. Connect a second String Transform with column: email and operation: lower.
  4. Preview the second node. The column should contain trimmed, lowercase text.
  5. Connect File Output, choose CSV, and run the flow to download the result.

Before / after

Original emailCleaned email
" [email protected] "[email protected]
[email protected][email protected]
nullnull

Exported Python

These runnable examples use the same input and node configuration. The transformation lines come from Ciaren's code generators; replace the example dataframe with your file input when running them on your data.

pandas

python
import pandas as pd

df_1 = pd.DataFrame({'email': ['  [email protected] ', '[email protected]', None]})
df_2 = df_1.assign(email=lambda _d: _d['email'].astype('string').str.strip())
df_3 = df_2.assign(email=lambda _d: _d['email'].astype('string').str.lower())
print(df_3)

Polars

python
import polars as pl

df_1 = pl.DataFrame({'email': ['  [email protected] ', '[email protected]', None]})
df_2 = df_1.with_columns(pl.col('email').cast(pl.Utf8).str.strip_chars())
df_3 = df_2.with_columns(pl.col('email').cast(pl.Utf8).str.to_lowercase())
print(df_3)

Tips

  • Clean both inputs before a Join, so the keys use the same format. Keep a copy of the original field if you need to audit it.
  • Empty text is still text; trimming does not turn an empty string into null.
  • Use lower only where casing is irrelevant. Passwords and case-sensitive identifiers should keep their original values.
  • Cleaning an email does not validate its address or guarantee delivery.

See also