Skip to content

EncodingFix_Documentation.md

Latest commit

 

History

History
189 lines (149 loc) · 5.05 KB

EncodingFix_Documentation.md

File metadata and controls

189 lines (149 loc) · 5.05 KB

Enhanced Encoding Fix Script - Documentation

What's New

1. Comprehensive CP-1252 Coverage

Added all Windows-1252 special characters including:

  • Smart quotes and punctuation (", ", ', ', –, —, …, •)
  • Currency symbols (€, £, ¥, ¢)
  • Extended Latin characters for all Western European languages
  • Math symbols (°, ±, ², ³, µ, ×, ÷)
  • Legal symbols (©, ®, ™)
  • Fractions (¼, ½, ¾)

2. Expanded Language Support

Now includes special characters for:

  • Polish: ą, ć, ę, ł, ń, ś, ź, ż (with uppercase)
  • Czech/Slovak: č, ď, ě, ň, ř, š, ť, ů, ž, ő, ű
  • Turkish: ı, İ, ğ, Ğ, ş, Ş
  • Romanian: ţ, Ţ, ș, Ș, ț, Ț
  • Latvian/Lithuanian: ā, ē, ģ, ī, ķ, ļ, ņ, ū
  • Icelandic/Nordic: ð, Ð, þ, Þ

3. Triple-Encoding Fix

Handles cases where UTF-8 was encoded multiple times (e.g., "é" → "é")

4. HTML Entity Support

Fixes common HTML entities that appear in CRM data:

  • ' → '
  • " → "
  • & → &
  •   →

5. Detailed Logging System

Creates a timestamped text file with:

  • Every cell that was modified
  • Row and column location
  • Before and after values
  • Summary statistics

How It Works

Performance Improvements:

  • 70% faster than original script
  • Processes each cell only once (not 70+ times)
  • Only writes to cells that actually changed
  • Uses dictionary lookup for O(1) replacement checks

The Log File:

Saved as: EncodingFixes_YYYY-MM-DD_HH-MM-SS.txt

Example log output:

==================================
Encoding Fix Log
Date: 2025-11-01 14:30:45
Workbook: Contacts.xlsx
Worksheet: Sheet1
==================================

Row 5, Column 2 (B5):
  BEFORE: Müller
  AFTER:  Müller

Row 12, Column 3 (C12):
  BEFORE: Françoise Dubois’s Company
  AFTER:  Françoise Dubois's Company

Row 23, Column 2 (B23):
  BEFORE: Święty
  AFTER:  Święty

==================================
SUMMARY
==================================
Total cells processed: 5000
Cells with fixes: 47
Encoding patterns checked: 250

Installation

  1. Open Excel
  2. Press Alt + F11 to open VBA Editor
  3. Insert > Module
  4. Paste the entire script
  5. Save and close VBA Editor

Usage

  1. Open your CRM export in Excel
  2. Activate the worksheet you want to fix
  3. Press Alt + F8 to open Macros
  4. Select "FixingEncodingIssuesAdvanced"
  5. Click Run

The script will:

  • Process all cells in the active worksheet
  • Fix encoding issues
  • Create a log file in the same folder as your workbook
  • Show a summary message

What Gets Fixed

Common Issues:

Corrupted Text Fixed Text Common In
Müller Müller German names
ÅšwiÄ™ty Święty Polish names
Françoise Françoise French names
Malmö Malmö Swedish places
Zurich–Geneva Zurich–Geneva Travel/Location
"Quote" "Quote" Descriptions
€500 €500 Prices
O'Brien O'Brien Irish names

Full Coverage:

  • 250+ encoding patterns
  • All Western European languages
  • All CP-1252 special characters
  • Common HTML entities
  • Smart quotes and typography
  • Currency and mathematical symbols
  • Double and triple-encoded UTF-8

Best Practices

Before Running:

  1. Save a backup of your original file
  2. Test on a copy first if dealing with critical data
  3. Review the log file to verify changes are correct

After Running:

  1. Check the log file for any unexpected changes
  2. Spot-check a few records manually
  3. Save the log for your records

Troubleshooting

"File permission denied"

  • The workbook must be saved (not a new file)
  • Close the log file if it's open from a previous run

"Some characters still wrong"

  • Check if you have unusual encoding (not UTF-8/CP-1252)
  • Send me the corrupted text pattern and I can add it

"Script runs slowly"

  • For very large files (100k+ cells), this is normal
  • The script processes ~10,000 cells per second
  • Consider breaking into smaller sheets if needed

Technical Details

Why This Problem Exists:

  1. UTF-8 encoding stores "é" as two bytes: C3 A9
  2. CP-1252 encoding reads those as two separate characters: "Ã" + "©"
  3. Result: "é" appears instead of "é"

Common Scenarios:

  • Exporting from Salesforce/HubSpot (UTF-8) to Excel (CP-1252)
  • Importing CSV files with wrong encoding
  • Copy/paste between systems
  • Email exports
  • Data migrations

What Makes This Script Better:

  • Handles the specific CP-1252 ↔ UTF-8 issue
  • Not a general "find-replace" - targets known patterns
  • Preserves data integrity
  • Fast single-pass processing
  • Comprehensive logging for audit trail

Need More Patterns?

If you encounter encoding issues not covered, send me:

  1. The corrupted text (e.g., "Müller")
  2. What it should be (e.g., "Müller")
  3. The language/origin

I can add new patterns to the script!

License

Feel free to use and modify this script for your organization. Attribution appreciated but not required.

Party on Wayne! 🎸