How to use Python 3 to import Excel files with CSV extensions saved as Unicode Text?

Question

As output from another service, I have received many Excel files that have a

".csv" filename extension but are saved as "Unicode Text (*.txt)".

To my knowledge they don't have an Unicode characters so I am not worried about data loss and if I was producing the data I would not have saved it this way. However, I need to process 100s of these files and I have been unable to import them using Python (specifically Python 3).

I have tried many different options such as the

csv module, pandas.read_csv(), and pyexcel.get_sheet()

to import the Excel file directly without any sucess. Often with errors such as

"... can't decode byte 0xff in position 0: invalid start byte".

I can manually save the file in Excel with a ".csv" extension and a "CSV (Comma delimited)(*.csv)" file type which can then be imported (e.g. pyexcel.get_sheet() ), but can't figure out how to do this programmatically.

I can also manually open the original file in Notepad and save it as a text file with a ".txt" extension and ANSI encoding, which allows me to import the data using numpy.loadtxt(). This isn't ideal because it is also manual. Additionally, I don't know why I need to convert to ANSI encoding and can't use UTF-8 for it to be read using

numpy.loadtxt(file_name, encoding="UTF-8"),

which results in an error such as

"... ValueError: could not convert string to float: '\ufeff ..."

and the following error using just numpy.loadtxt(file_name)

"... ValueError: could not convert string to float: ' ..."

In summary, my main goal is to find a programmatic way to change the Excel file type/encoding to something I can import into Python 3 using existing packages with CSV support. Additionally, if someone has any idea why numpy.loadtxt can't import a text file with a UTF-8 encoding (but can for ANSI encoding) that would be great too! Any knowledge to help me understand the problem (or my misconception of the problem) is appreciated.

Answer 1

Your file has a UTF-8 BOM at the front. Python can strip this automatically with the utf_8_sig codec:

numpy.loadtxt(file_name, encoding="utf_8_sig"),

Answer 2

Excel's "Unicode Text" is UTF-16-encoded and tab-delimited. With the csv module, use:

import csv
with open('book1.txt',encoding='utf16') as f:
    r = csv.reader(f,delimiter='\t')
    for row in r:
        print(row)

With the following Excel sheet saved as "Unicode Text" (Excel 2016):

This produces (Python 3.6):

['English', 'Chinese']
['Mark', '马克']
['American', '美国人']

Pandas also works (but not with utf16...it oddly needed the hyphen):

>>> import pandas as pd
>>> pd.read_csv('book1.txt',delimiter='\t',encoding='utf-16')
    English Chinese
0      Mark      马克
1  American     美国人

Answer 3

Default dtype is float. genfromtxt uses nan when it can't parse the string:

In [8]: np.genfromtxt(['one, 2, 3'], delimiter=',',encoding=None)
Out[8]: array([nan,  2.,  3.])

loadtxt raises an error:

In [9]: np.loadtxt(['one, 2, 3'], delimiter=',',encoding=None)   
....
ValueError: could not convert string to float: 'one'
In [10]: np.loadtxt(['1, 2, 3'], delimiter=',',encoding=None)
Out[10]: array([1., 2., 3.])

genfromtxt with dtype=None:

In [12]: np.genfromtxt(['one, 2, 3'], delimiter=',',encoding=None,dtype=None)
Out[12]: array(('one', 2, 3), dtype=[('f0', '<U3'), ('f1', '<i8'), ('f2', '<i8')])

from your comment:

In [13]: np.genfromtxt('1\t2\n3\t4\n'.splitlines(), delimiter='\t',encoding=None
    ...: )
Out[13]: 
array([[1., 2.],
       [3., 4.]])

In [21]: print('1\t2\n3\t4\n')
1   2
3   4

Using encoding='utf8' works as well.