Search the docs, videos, blog, apps, teams, skills and release notes.
At a glance
What it is
Quick techniques for cleaning CSV files - deduplication, normalization, encoding fixes, merging, and common data quality repairs using command-line tools and scripting.
A skill is a written procedure a bot loads when a job calls for it. This one is a single file, SKILL.md, and the whole file is on this page.
The whole file, exactly as it downloads. Read it before you rely on it.
SKILL.md
336 lines · 1,307 words
You are a data cleaning specialist. Help the user fix messy CSV files quickly using the most appropriate tool. Provide exact commands and scripts. Prioritize one-liners for simple tasks, scripts for complex ones.
Use this skill when:
User asks about csv data cleaner techniques or best practices
User needs guidance on csv data cleaner concepts
Quick techniques for cleaning CSV files - deduplication, normalization, encoding fixes, merging, and common data quality repairs using command-line tools and scripting.
Nothing. It ships with Murage, which is open source. Your bots run on the AI plan you already have.
Yes. Open it in Settings → Skills to read, edit or duplicate it. Skill Guard scans it again after every edit.
Download the app, connect the AI you already pay for, and tell your Chief of Staff what needs doing. Plan on about ten minutes from install to a working team.
No account needed · Runs on the AI plan you already pay for
Paste it into your agent's instructions (CLAUDE.md, AGENTS.md or a custom GPT), or add it to a bot in Murage.
Ask Ember for the bot, then switch the skill on in its settings.
Step 1
Send your Chief of Staff this message. It proposes the bot and its role first, and creates it when you say go.
Send this to Ember
“Create a bot that uses the CSV Data Cleaner skill, and show me the plan first.”
You're done when your Chief proposes the bot and its role, and the bot appears in your sidebar once you say go.
Step 2
You're done when you see the bot’s skills and the library.
Step 3
You're done when the skill shows as on for that bot.
Step 4
You're done when the bot takes the job and opens the skill when it needs it.
Prefer to do it yourself? Choose + in the sidebar, then New Bot.Read the full guide to skills and Skill Guard
What it teaches a bot, what it covers, and the whole file to read.
The principle
You are a data cleaning specialist. Help the user fix messy CSV files quickly using the most appropriate tool. Provide exact commands and scripts. Prioritize one-liners for simple tasks, scripts for complex ones.
What it covers
When to Use
Quick Diagnosis
Encoding Fixes
Deduplication
Normalization
Merging CSV Files
Common Data Quality Fixes
Quick Validation Checks
Tool Recommendations
Process
Output Format
Edge Cases
Example
User wants to implement or improve their approach to csv data cleaner
Do NOT use when:
The request falls outside the scope of csv data cleaner
User needs a different specialized skill for their specific situation
The topic requires professional consultation beyond general guidance
# Preview file structure
head -5 data.csv
# Count rows (excluding header)
wc -l data.csv
# Check encoding
file -i data.csv # Linux/Mac
# Windows PowerShell:
[System.IO.File]::ReadAllBytes("data.csv")[0..2] # check BOM
# Count columns (assuming comma delimiter)
head -1 data.csv | awk -F',' '{print NF}'
# Check for inconsistent column counts
awk -F',' '{print NF}' data.csv | sort | uniq -c# Convert to UTF-8 from unknown encoding
iconv -f ISO-8859-1 -t UTF-8 input.csv > output.csv
# Remove BOM (byte order mark)
sed '1s/^\xEF\xBB\xBF//' input.csv > output.csv
# Fix Windows line endings (CRLF -> LF)
sed 's/\r$//' input.csv > output.csv
# Or:
dos2unix input.csv
# Python (handles any encoding)
python3 -c "
import csv, codecs
with codecs.open('input.csv','r','latin-1') as f:
data = f.read()
with codecs.open('output.csv','w','utf-8') as f:
f.write(data)
"# Remove exact duplicate rows (keeps first occurrence)
awk '!seen[$0]++' data.csv > deduped.csv
# Remove duplicates based on specific column (column 1)
awk -F',' '!seen[$1]++' data.csv > deduped.csv
# Python: deduplicate with more control
python3 << 'EOF'
import csv
seen = set()
with open('data.csv') as f, open('deduped.csv', 'w', newline='') as out:
reader = csv.reader(f)
writer = csv.writer(out)
header = next(reader)
writer.writerow(header)
key_col = 0 # column index to deduplicate on
for row in reader:
key = row[key_col].strip().lower() # normalize before checking
if key not in seen:
seen.add(key)
writer.writerow(row)
EOF# Trim whitespace from all fields
python3 -c "
import csv, sys
reader = csv.reader(open('data.csv'))
writer = csv.writer(sys.stdout)
for row in reader:
writer.writerow([cell.strip() for cell in row])
" > cleaned.csv# Python: normalize specific columns
import csv
with open('data.csv') as f, open('out.csv', 'w', newline='') as out:
reader = csv.DictReader(f)
writer = csv.DictWriter(out, fieldnames=reader.fieldnames)
writer.writeheader()
for row in reader:
row['email'] = row['email'].strip().lower()
row['name'] = row['name'].strip().title()
row['state'] = row['state'].strip().upper()
writer.writerow(row)from datetime import datetime
import csv
formats_to_try = ['%m/%d/%Y', '%d-%m-%Y', '%Y-%m-%d', '%B %d, %Y', '%m/%d/%y']
def normalize_date(value, target_format='%Y-%m-%d'):
for fmt in formats_to_try:
try:
return datetime.strptime(value.strip(), fmt).strftime(target_format)
except ValueError:
continue
return value # return original if no format matches
# Apply to column index 3
with open('data.csv') as f, open('out.csv', 'w', newline='') as out:
reader = csv.reader(f)
writer = csv.writer(out)
writer.writerow(next(reader)) # header
for row in reader:
row[3] = normalize_date(row[3])
writer.writerow(row)import re, csv
def normalize_phone(phone):
digits = re.sub(r'\D', '', phone)
if len(digits) == 11 and digits[0] == '1':
digits = digits[1:]
if len(digits) == 10:
return f"({digits[:3]}) {digits[3:6]}-{digits[6:]}"
return phone # return original if unexpected format# Stack files with same columns (skip header on 2nd+ files)
head -1 file1.csv > merged.csv
tail -n +2 -q file1.csv file2.csv file3.csv >> merged.csv
# Python: merge/join on a key column
python3 << 'EOF'
import csv
# Load lookup data
lookup = {}
with open('lookup.csv') as f:
for row in csv.DictReader(f):
lookup[row['id']] = row
# Merge with main data
with open('main.csv') as f, open('merged.csv', 'w', newline='') as out:
reader = csv.DictReader(f)
extra_fields = ['extra_col1', 'extra_col2'] # fields from lookup
writer = csv.DictWriter(out, fieldnames=reader.fieldnames + extra_fields)
writer.writeheader()
for row in reader:
match = lookup.get(row['id'], {})
for field in extra_fields:
row[field] = match.get(field, '')
writer.writerow(row)
EOFawk -F',' 'NF && $0 !~ /^[,\s]*$/' data.csv > cleaned.csvimport csv
default_values = {'status': 'unknown', 'count': '0', 'category': 'other'}
with open('data.csv') as f, open('filled.csv', 'w', newline='') as out:
reader = csv.DictReader(f)
writer = csv.DictWriter(out, fieldnames=reader.fieldnames)
writer.writeheader()
for row in reader:
for field, default in default_values.items():
if not row.get(field, '').strip():
row[field] = default
writer.writerow(row)# Split "Full Name" into "First" and "Last"
import csv
with open('data.csv') as f, open('out.csv', 'w', newline='') as out:
reader = csv.DictReader(f)
fields = [fn for fn in reader.fieldnames if fn != 'full_name'] + ['first_name', 'last_name']
writer = csv.DictWriter(out, fieldnames=fields)
writer.writeheader()
for row in reader:
parts = row.pop('full_name', '').strip().split(None, 1)
row['first_name'] = parts[0] if parts else ''
row['last_name'] = parts[1] if len(parts) > 1 else ''
writer.writerow(row)# Find rows with wrong column count
expected=5
awk -F',' -v exp="$expected" 'NF != exp {print NR": "NF" cols - "$0}' data.csv
# Find rows with empty required fields (column 1 and 3)
awk -F',' '$1=="" || $3=="" {print NR": "$0}' data.csv
# Summary statistics for a numeric column (column 2)
awk -F',' 'NR>1 {sum+=$2; count++; if($2>max||NR==2)max=$2; if($2<min||NR==2)min=$2} END {print "count:"count, "sum:"sum, "avg:"sum/count, "min:"min, "max:"max}' data.csv| Task | Best Tool |
|---|---|
| Simple column extraction | cut -d',' -f1,3 data.csv |
| Complex transforms | Python csv module |
| Large files (GB+) | csvkit, xsv, or miller |
| Quick exploration | csvlook (from csvkit) |
| SQL on CSV | csvsql or q |
| Excel interop | pandas or openpyxl |
# csvkit essentials
install the package via pip csvkit
csvlook data.csv # pretty print
csvstat data.csv # column statistics
csvsort -c 2 data.csv # sort by column 2
csvgrep -c 3 -m "value" data.csv # filter rows
csvjoin -c id file1.csv file2.csv # join filesGather information. Ask the user clarifying questions to understand their specific situation, goals, and constraints
Analyze context. Review the information provided and identify key factors relevant to csv data cleaner
Develop recommendations. Apply domain expertise to create actionable guidance tailored to the user's needs
Present structured output. Deliver findings in the output format below with clear next steps
Address follow-ups. Answer additional questions and refine recommendations based on feedback
## Csv Data Cleaner Analysis
### Assessment
[Key findings and observations]
### Recommendations
1. [Primary recommendation]
2. [Secondary recommendation]
3. [Additional suggestions]
### Action Items
- [ ] [First action step]
- [ ] [Second action step]
- [ ] [Follow-up task]Incomplete information: Ask clarifying questions before proceeding with recommendations
Conflicting requirements: Prioritize the most critical constraint and note trade-offs
Out of scope requests: Redirect to appropriate specialized skill or professional resource
Beginner vs advanced: Adjust depth and terminology based on user's experience level
Input: "Help me with csv data cleaner for my current situation"
Output:
Based on your situation, here is a structured approach to csv data cleaner:
Assessment: Evaluate your current state and identify key areas for improvement
Strategy: Develop a targeted plan based on best practices
Implementation: Execute the plan with specific, measurable steps
Review: Monitor progress and adjust as needed
SKILL.md
336 lines · 1,307 words
The text of SKILL.md is shown as it downloads, under the Apache-2.0 license, by Ferrox Labs. The license is the one in the file's own frontmatter.
Your new bot
CSV Data Cleaner bot
Waiting in your sidebar once you say go.
No account needed · Runs on the AI plan you already pay for