Working with Arabic Text in Python: Normalising, Searching, Exporting

Hello, tech enthusiasts! 👋

Imagine you have a customer list with Arabic names. You search for “احمد” and get nothing. But you know the customer is there. The problem? In the file, the name is written “أحمد”, with a hamza on the alef.

Arabic can write the same word in many small ways. Some people add tashkeel (the small vowel marks). Some use tatweel (the long line “ـ” that stretches a word). Some write “ة” and some write “ه” at the end. For a human, these are the same word. For a computer, they are completely different.

Today we will fix this. We will clean the text, search it, and export it so Excel shows Arabic correctly.

What you need

  • Python 3 installed (https://www.python.org)
  • The pandas and openpyxl libraries
  • A file with some Arabic text, for example customers.csv

Install the libraries with this command:

pip install pandas openpyxl

Step 1: Understand normalising

Normalising means making all the different ways of writing a word look the same. Think of it like making everyone in an office use the same date format. After that, comparing is easy.

We will make these changes:

  • All alef forms (أ إ آ ٱ) become a plain alef (ا).
  • Alef maqsura (ى) becomes yaa (ي).
  • Taa marbuta (ة) becomes haa (ه).
  • Tashkeel marks are removed.
  • Tatweel (ـ) is removed.

We only use the clean version for searching. You keep the original text for display.

Step 2: Write the normalise function

We use regex (regular expressions). A regex is like a search pattern with wildcards. It can find many characters at once.

import re

# Tashkeel: fathatan to sukun, plus superscript alef and Quranic marks
TASHKEEL = re.compile(r"[ؐ-ًؚ-ٰٟ]")
TATWEEL = "ـ"

def normalise(text):
    """Return a clean version of Arabic text for searching."""
    if not isinstance(text, str):
        return ""
    text = TASHKEEL.sub("", text)        # remove vowel marks
    text = text.replace(TATWEEL, "")     # remove the stretch line
    text = re.sub("[أإآٱ]", "ا", text)   # all alef forms -> ا
    text = text.replace("ى", "ي")        # alef maqsura -> yaa
    text = text.replace("ة", "ه")        # taa marbuta -> haa
    return text.strip()

# Quick test
print(normalise("مُحَمَّد"))     # محمد
print(normalise("أحـــمد"))     # احمد
print(normalise("فاطمة"))       # فاطمه

Run it. All three words now come out clean and simple.

Step 3: Search your data

Now let us load a file and search it. Our example customers.csv has the columns name and city.

import pandas as pd

df = pd.read_csv("customers.csv", encoding="utf-8")

# Add a hidden helper column with the clean text
df["name_clean"] = df["name"].apply(normalise)

def search(query):
    q = normalise(query)
    return df[df["name_clean"].str.contains(q, regex=False)]

results = search("احمد")
print(results[["name", "city"]])

Now “احمد” finds “أحمد”, “إحمد” and “أحـمد”. The trick is simple: you clean both sides, the data and the search word. Then they always speak the same language.

We use regex=False so that special characters in the search word, like a dot, are treated as normal text.

Step 4: Export to CSV so Excel shows Arabic

This part surprises many people. You save a CSV with Arabic text, open it in Excel, and you see strange symbols like “أحمد”.

The reason: Excel does not always know the file is UTF-8. UTF-8 is the standard way to store letters from every language. The fix is a small hidden mark at the start of the file, called a BOM. It tells Excel “this file is UTF-8”. In Python, you add it with the encoding utf-8-sig.

# Drop the helper column before saving
output = results.drop(columns=["name_clean"])

# utf-8-sig adds the BOM, so Excel reads Arabic correctly
output.to_csv("search_results.csv", index=False, encoding="utf-8-sig")

Open search_results.csv in Excel. The names now look perfect.

Step 5: Or export straight to Excel

An .xlsx file always stores text as Unicode, so you do not need any encoding trick.

output.to_excel("search_results.xlsx", index=False)

If you want the sheet to read from right to left, you can set it with openpyxl:

from openpyxl import load_workbook

wb = load_workbook("search_results.xlsx")
wb.active.sheet_view.rightToLeft = True   # show columns right to left
wb.save("search_results.xlsx")

Common problems

  • Excel still shows strange symbols → Check that you used utf-8-sig, not utf-8. Also open the file by double-clicking, not by pasting the text.
  • UnicodeDecodeError when reading a CSV → The file may be in an old Windows Arabic format. Try encoding="cp1256" in read_csv.
  • Search finds too much → Changing “ة” to “ه” can join a few different words. If that is a problem for your data, remove that line from normalise.
  • Empty cells break the code → Our function returns an empty string for empty cells, so this is already handled.

Wrap-up

You now have a small toolkit for Arabic text: clean it, search it, and export it without broken letters. These few lines can save you hours of manual checking in big customer or invoice lists.

Next idea: use the same normalise function to find duplicate customers. Group by name_clean and see which names appear more than once. You may be surprised! 😄

fsaeed

, , , ,