CSV file with Arabic characters is displayed as symbols in Excel

Question

I am using python to extract Arabic tweets from twitter and save it as a CSV file, but when I open the saved file in excel the Arabic language displays as symbols. However, inside python, notepad, or word, it looks good.

May I know where is the problem?

Exporting CSV from Python(or any other language, I guess) gives a UTF-8 formatted file. But Excel tries to read in a different format ISO-8859-1. So the opening format must be manually selected. To avoid this, use some Excel-specific libs such as XLSX. — DumTux, Feb 15 '20 at 13:23
It could help if you provided some details. What version of python/excel are you using? What operating system? Could you provide the code you are using? — JerodG, Feb 15 '20 at 13:28

mohd4482 · Accepted Answer · 2020-05-16T19:00:58.637

47

This is a problem I face frequently with Microsoft Excel when opening CSV files that contain Arabic characters. Try the following workaround that I tested on latest versions of Microsoft Excel on both Windows and MacOS:

Open Excel on a blank workbook
Within the Data tab, click on From Text button (if not activated, make sure an empty cell is selected)
Browse and select the CSV file
In the Text Import Wizard, change the File_origin to "Unicode (UTF-8)"
Go next and from the Delimiters, select the delimiter used in your file e.g. comma
Finish and select where to import the data

The Arabic characters should show correctly.

edited May 16 '20 at 19:00

answered Feb 15 '20 at 21:19

mohd4482

1,788
14
25

2

tnx, it works but, now there is another problem all the text is in one column. :( – Shams Feb 16 '20 at 17:30
that means the appropriate delimiter was not selected. Check step #5 and select the delimiter used in your file e.g comma, semicolon, tab, ... etc – mohd4482 Feb 16 '20 at 17:33
For old MS Excel, there is no option to customize file options when saving as "CSV". Simply, the solution is to save the "XLSX" file as "TXT (Unicode UTF-8)". The generated file will be "TAB" separator. If you want it comma separated, open the file in Notepads (open large files in VS Code) and replace "TAB" occurrences with "," and then save as "CSV". – Ahmed El-Atab Mar 05 '21 at 20:36
How relevant your comment is? The question was about opening CSV files not saving them. Good comment though. Thanks! – mohd4482 Mar 05 '21 at 21:48

score 14 · Answer 2 · answered Oct 25 '20 at 21:05

14

Just use encoding='utf-8-sig' instead of encoding='utf-8' as follows:

import csv

data = u"اردو"

with(open('example.csv', 'w', encoding='utf-8-sig')) as fh:
    writer = csv.writer(fh)
    writer.writerow([data])

It worked on my machine.

answered Oct 25 '20 at 21:05

saifhassan

346
2
11

It works perfectly – Nemra Khalil Jul 26 '23 at 19:15

Abdulrahman Osama · Answer 3 · 2023-01-09T10:45:21.857

2

Fastest way is after saving the file into .csv from python:

open the .csv file using Notepad++
from Encoding drop-down menu choose UTF-8-BOM
click save as and save at with same name with .csv extension (e.g. data.csv) and keep the file type as it is .txt
re-open the file again with Microsoft Excel.

edited Jan 09 '23 at 10:45

answered Sep 16 '22 at 12:14

Abdulrahman Osama

71
4

score 1 · Answer 4 · answered Jul 22 '20 at 12:15

The only solution that i've found to save arabic into an excel file from python is to use pandas and to save into the xlsx extension instead of csv, xlsx seems a million times better here's the code i've put together which worked for me

import pandas as pd
def turn_into_csv(data, csver):
    ids = []
    texts = []
    for each in data:
        texts.append(each["full_text"])
        ids.append(str(each["id"]))

    df = pd.DataFrame({'ID': ids, 'FULL_TEXT': texts})
    writer = pd.ExcelWriter(csver + '.xlsx', engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1', encoding="utf-8-sig")

    # Close the Pandas Excel writer and output the Excel file.
    writer.save()

score 1 · Answer 5 · answered May 17 '23 at 08:11

1

I'm not sure it's the solution, but it might help.

answered May 17 '23 at 08:11

Peter

162
3
11

score 0 · Answer 6 · answered Feb 15 '20 at 13:47

Excel is known to have an awful csv import sytem. Long story short if on same system you import a csv file that you have just exported, it will work smoothly. Else, the csv file is expected to use the Windows system encoding and delimiter.

A rather awkward but robust system is to use LibreOffice or Oracle OpenOffice. Both are far beyond Excel on any feature but the csv module: they will allow you to specify the delimiters and optional quoting characters along with the encoding of the csv file and you will be able to save the resulting file in xslx.

score 0 · Answer 7 · answered Jun 30 '22 at 20:14

Although my CSV file encoding was UTF-8; but explicitly redoing it again using the Notepad resolved it.

Steps:

Open your CSV file in Notepad.
Click File --> Save as...
In the "Encoding" drop-down, select UTF-8.
Rename your file using the .csv extension.
Click Save.
Reopen the file with Excel.

CSV file with Arabic characters is displayed as symbols in Excel

7 Answers7

Linked