CODEWITHSUNDEEP
pythonexcelpandasdataframe
kundan
kundan

Reputation: 11

How to export to multiple excel sheet of a single csv file through pandas in python

I have imported a large txt file in python pandas. Now I want to export the csv file to multiple excel as data is too large to fit in a single excel sheet.

I use the following commands:

import pandas as pd
df = pd.read_csv('basel.txt',delimiter='|')
df.to_excel('basel.txt')

Unfortunately I got the following error:

****ValueError: This sheet is too large! Your sheet size is: 1158008, 18 Max sheet size is: 1048576, 16384****

Upvotes: 1

Views: 2203

Answers (3)

Razmik Melikbekyan
Razmik Melikbekyan

Reputation: 758

You can split into chunks and write each chunk in one sheet. np.array_split splits into number of chunks np.split requires an equal division.

import numpy as np

nsheets = 10  # you may change it
for i, temp in enumerate(np.array_split(df, nsheets)):
    temp.to_excel('basel.xls', sheet_name=f'sheet_{i}')

Upvotes: 1

ibarrond
ibarrond

Reputation: 7581

You can write half of the dataset into a different excel sheet:

import pandas as pd
df = pd.read_csv('basel.txt',delimiter='|')
df.iloc[:df.shape[0]//2,:].to_excel('basel.xls', sheet_name='First Sheet')
df.iloc[df.shape[0]//2:,:].to_excel('basel.xls', sheet_name='Second Sheet')

Upvotes: 0

manoj yadav
manoj yadav

Reputation: 347

import pandas as pd
chunksize = 10 ** 6
for chunk in pd.read_csv('basel.txt', chunksize=chunksize):
    chunk.to_excel('basel_'+str(chunk)+'.excel')

you may read the pandas file in chunks and save each chunk in excel file

Upvotes: 1

Related Questions