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

0 votes

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)

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

Feb 8, 2019 in Python by Rashmi

1 answer to this question.

0 votes

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)


csvfile = 'sample.csv'
df.to_csv(csvfile, index=False)
answered Feb 8, 2019 by Omkar
• 69,170 points

Related Questions In Python

0 votes
1 answer

How to read Excel File in Python

With pandas it is possible to get ...READ MORE

answered Oct 22, 2018 in Python by Priyaj
• 58,140 points
0 votes
1 answer

How to use read a WSDL file from the file system using Python suds?

Hi, good question. It is a very simple ...READ MORE

answered Jan 21, 2019 in Python by Nymeria
• 3,520 points
0 votes
1 answer

Need help to open excel file and read in Python

You can use pandas module to do ...READ MORE

answered Jul 22, 2019 in Python by Tina
0 votes
2 answers

How to split excel field data which contains the multiple delimiter values?

Try the following: new = data["Filed_data"].str.split(";", n = ...READ MORE

answered Jun 25, 2020 in Python by Shivam Dutt Sharma
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 6, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 2,364 views
0 votes
0 answers
+5 votes
6 answers

Lowercase in Python

You can simply the built-in function in ...READ MORE

answered Apr 11, 2018 in Python by hemant
• 5,810 points
0 votes
2 answers
+1 vote
1 answer

How to read hdfs file using python?

subprocess.Popen(["hadoop", "fs", "-cat", "/path/to/myfile"], stdou ...READ MORE

answered Dec 6, 2018 in Python by Omkar
• 69,170 points