If you use read_excel() to read a .csv you will get the error XLRDError: Unsupported format, or corrupt file: Expected BOF record; To read .csv one needs to use read_csv(), like this df1= pd.read_csv(“filename.csv”)
[Dis]claimer: I’m the author/maintainer of xlrd. The documentation references to formula text are about “name” formulas; read the section “Named references, constants, formulas, and macros” near the start of the docs. These formulas are associated sheet-wide or book-wide to a name; they are not associated with individual cells. Examples: PI maps to =22/7, SALES maps … Read more
A somewhat late answer, but with pandas, it is possible to get directly a column of an excel file: import pandas df = pandas.read_excel(‘sample.xls’) #print the column names print df.columns #get the values for a given column values = df[‘Arm_id’].values #get a data frame with selected columns FORMAT = [‘Arm_id’, ‘DSPName’, ‘Pincode’] df_selected = df[FORMAT] … Read more
Try to open it as an HTML with pandas: import pandas as pd data = pd.read_html(‘filename.xls’) Or try any other html python parser. That’s not a proper excel file, but an html readable with excel.
You can still use the ExcelFile class (and the sheet_names attribute): xl = pd.ExcelFile(‘foo.xls’) xl.sheet_names # see all sheet names xl.parse(sheet_name) # read a specific sheet to DataFrame see docs for parse for more options…
Here’s some sample code I used recently to do just that. It opens a workbook, goes down the rows, if a condition is met it writes some data in the row. Finally it saves the modified file. from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd START_ROW = 297 # 0 based … Read more
There are two parts to this. First, you must enable the reading of formatting info when opening the source workbook. The copy operation will then copy the formatting over. import xlrd import xlutils.copy inBook = xlrd.open_workbook(‘input.xls’, formatting_info=True) outBook = xlutils.copy.copy(inBook) Secondly, you must deal with the fact that changing a cell value resets the formatting … Read more
import csv with open(…, ‘wb’) as myfile: wr = csv.writer(myfile, quoting=csv.QUOTE_ALL) wr.writerow(mylist) Edit: this only works with python 2.x. To make it work with python 3.x replace wb with w (see this SO answer) with open(…, ‘w’, newline=””) as myfile: wr = csv.writer(myfile, quoting=csv.QUOTE_ALL) wr.writerow(mylist)
You could groupby on Column1 and then take Column3 to apply(list) and call to_dict? In [81]: df.groupby(‘Column1’)[‘Column3’].apply(list).to_dict() Out[81]: {0: [1], 1: [2, 3, 5], 2: [1, 2], 3: [4, 5], 4: [1], 5: [1, 2, 3]} Or, do In [433]: {k: list(v) for k, v in df.groupby(‘Column1’)[‘Column3’]} Out[433]: {0: [1], 1: [2, 3, 5], 2: … Read more