Python program to read the excel file which contains more than 65K records

Question

I am using this code to convert excel to csv file:

import glob 
path_to_excel_files = glob.glob('path/to/excel/files/*.xlsx')
for excel in path_to_excel_files:
 out = excel.split('.')[0]+'.csv'
 df = pd.read_excel(excel)
 df.to_csv(out)

But the problem is that it is converting only 65,000 records. How to get more than 65,000 records?

Answer 1

Hi @Rashmi!

I read your code and there is no such logic to read only 65,000 records. And the modules you are using also does not restrict the records. There is no problem in the code. So, I did a little research about this and found that 1 sheet in excel can hold only 65,000 records. Possibly, you have records in different sheets and those are not getting converted. To read records from different sheets, you will have to parse through the sheets. You can refer to the following code to do it:

import pandas as pd

df = pd.DataFrame()
xlfname = 'Productivity Report.xlsx'
xl = pd.ExcelFile(xlfname)

for sheet in xl.sheet_names:
df_tmp = xl.parse(sheet)
df = df.append(df_tmp, ignore_index=True,sort=False)

print(len(df))

csvfile = 'sample.csv'
df.to_csv(csvfile, index=False)