13

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 and notepad or word, it looks good. May I know where is the problem?

mhalshehri
  • 1,518
  • 12
  • 22
Shams
  • 143
  • 1
  • 1
  • 5
  • 2
    Tell Excel to open it with correct encoding – Sami Kuhmonen Feb 15 '20 at 13:14
  • 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. – Hotte Shen 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

4 Answers4

33

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:

  1. Open Excel on a blank workbook

  2. Within the Data tab, click on From Text button (if not activated, make sure an empty cell is selected)

  3. Browse and select the CSV file

  4. In the Text Import Wizard, change the File_origin to "Unicode (UTF-8)"

  5. Go next and from the Delimiters, select the delimiter used in your file e.g. comma

  6. Finish and select where to import the data

The Arabic characters should show correctly.

mhalshehri
  • 1,518
  • 12
  • 22
  • 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 – mhalshehri 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! – mhalshehri Mar 05 '21 at 21:48
11

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.

saifhassan
  • 196
  • 1
  • 11
1

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()
Kream
  • 131
  • 1
  • 3
  • 12
0

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.

Serge Ballesta
  • 136,215
  • 10
  • 111
  • 230