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)
 df.to_csv(out)

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

Feb 8 in Python by Rashmi
19 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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)

print(len(df))

csvfile = 'sample.csv'
df.to_csv(csvfile, index=False)
answered Feb 8 by Omkar
• 65,810 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
• 56,100 points
54 views
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 in Python by Nymeria
• 3,500 points
149 views
0 votes
1 answer

How to run the Python program forever?

Yes, you can use a while True: ...READ MORE

answered Jul 27, 2018 in Python by Priyaj
• 56,100 points
309 views
+2 votes
1 answer

“OSError: Unable to open file (bad superblock version number)" in python h5py file read

File could indeed be opened on Linux ...READ MORE

answered Sep 26, 2018 in Python by Priyaj
• 56,100 points
533 views
0 votes
1 answer

how can i count the items in a list?

suppose you have a list a = [0,1,2,3,4,5,6,7,8,9,10] now ...READ MORE

answered May 2 in Python by Mohammad
• 920 points
19 views
+4 votes
6 answers
+1 vote
1 answer

How to read hdfs file using python?

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

answered Dec 6, 2018 in Python by Omkar
• 65,810 points
106 views
0 votes
1 answer

Python convert excel file to csv

Here you go: import glob path_to_excel_files = glob.glob('path/to/excel/files/*.xlsx') for ...READ MORE

answered Feb 8 in Python by Omkar
• 65,810 points
17 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.