How to check encoding of a CSV file

Question

I have a CSV file and I wish to understand its encoding. Is there a menu option in Microsoft Excel that can help me detect it

OR do I need to make use of programming languages like C# or PHP to deduce it.

Answer 1

You can use Notepad++ to evaluate a file's encoding without needing to write code. The evaluated encoding of the open file will display on the bottom bar, far right side. The encodings supported can be seen by going to Settings -> Preferences -> New Document/Default Directory and looking in the drop down.

Answer 2

In Linux systems, you can use file command. It will give the correct encoding

Sample:

file blah.csv

Output:

blah.csv: ISO-8859 text, with very long lines

Answer 3

If you use Python, just use a print() function to check the encoding of a csv file. For example:

with open('file_name.csv') as f:
    print(f)

The output is something like this:

<_io.TextIOWrapper name='file_name.csv' mode='r' encoding='utf8'>

Answer 4

You can also use python chardet library

# install the chardet library
!pip install chardet

# import the chardet library
import chardet 

# use the detect method to find the encoding
# 'rb' means read in the file as binary
with open("test.csv", 'rb') as file:
    print(chardet.detect(file.read()))

Answer 5

Use chardet https://github.com/chardet/chardet （documentation is short and easy to read).

Install python, then pip install chardet, at last use the command line command.

I tested under GB2312 and it's pretty accurate. (Make sure you have at least a few characters, sample with only 1 character may fail easily).

file is not reliable as you can see.

Answer 6

Or you can execute in python console or in Jupyter Notebook:

import csv
data = open("file.csv","r") 
data

You will see information about the data object like this:

<_io.TextIOWrapper name='arch.csv' mode='r' encoding='cp1250'>

As you can see it contains encoding infotmation.

Answer 7

CSV files have no headers indicating the encoding.

You can only guess by looking at:

• the platform / application the file was created on
• the bytes in the file

In 2021, emoticons are widely used, but many import tools fail to import them. The chardet library is often recommended in the answers above, but the lib does not handle emoticons well.

icecream = ''

import csv

with open('test.csv', 'w') as f:
    wf = csv.writer(f)
    wf.writerow(['ice cream', icecream])


import chardet
with open('test.csv', 'rb') as f:
    print(chardet.detect(f.read()))

{'encoding': 'Windows-1254', 'confidence': 0.3864823918622268, 'language': 'Turkish'}

This gives UnicodeDecodeError while trying to read the file with this encoding.

The default encoding on Mac is UTF-8. It's included explicitly here but that wasn't even necessary... but on Windows it might be.

with open('test.csv', 'r', encoding='utf-8') as f:
    print(f.read())

ice cream,

The file command also picked this up

file test.csv
test.csv: UTF-8 Unicode text, with CRLF line terminators

My advice in 2021, if the automatic detection goes wrong: try UTF-8 before resorting to chardet.

Answer 8

In Python, You can Try...

from encodings.aliases import aliases
alias_values = set(aliases.values())

for encoding in set(aliases.values()):
    try:
        df=pd.read_csv("test.csv", encoding=encoding)
        print('successful', encoding)
    except:
        pass

Answer 9

As it is mentioned by @3724913 (Jitender Kumar) to use file command (it also works in WSL on Windows), I was able to get encoding information of a csv file by executing file --exclude encoding blah.csv using info available on man file as file blah.csv won't show the encoding info on my system.

Answer 10

import pandas as pd
import chardet
def read_csv(path: str, size: float = 0.10) -> pd.DataFrame:
 """
 Reads a CSV file located at path and returns it as a Pandas DataFrame. If 
 nrows is provided, only the first nrows rows of the CSV file will be 
 read. Otherwise, all rows will be read.

 Args:
    path (str): The path to the CSV file.
    size (float): The fraction of the file to be used for detecting the 
    encoding. Defaults to 0.10.

 Returns:
    pd.DataFrame: The CSV file as a Pandas DataFrame.

 Raises:
    UnicodeError: If the encoding of the file cannot be detected with the 
    initial size, the function will retry with a larger size (increased by 
    0.20) until the encoding can be detected or an error is raised.
 """
 try:
    byte_size = int(os.path.getsize(path) * size)

    with open(path, "rb") as rawdata:
        result = chardet.detect(rawdata.read(byte_size))

    return pd.read_csv(path, encoding=result["encoding"])

 except UnicodeError:
    return read_csv(path=path, size=size + 0.20)

Hi, I just added a function to find the correct encoding and read the csv in the given file path. Thought it would be useful

Answer 11

Just add the encoding argument that matches the file you`re trying to upload.

open('example.csv', encoding='UTF8')