1

I'm trying to copy over an excel document, however it only seems to copy over the first sheet and not copy over the sheet names either from the original file.

I've tried to copy it and when that failed I looked up how to do it, however, the issue is that I will not know what the sheet names are prior to use nor how many are there.

import pandas as pd
import numpy as np
from shutil import copyfile


copyfile(loc, tLoc)

wdf = pd.read_excel(tLoc , index_col=[0])
sheet_wdf_map = pd.read_excel(tLoc, sheet_name=None)

print(wdf.head())

wdf['Adequate'] = np.nan
wdf['Explanation'] = np.nan


wdf.to_excel(tLoc)

I'd expect it to copy over the entire file verbatim including all the sheets however it does not. If there is a solution involved in not copying it over into an empty preexisting file instead of a new file that would also work. Thanks!

Mitchell T
  • 33
  • 7
  • copyfile should copy complete contents except metadata. Can you show us how you know only the first sheet is copied? – Will Jun 04 '19 at 20:02
  • Because I have 2 sheets in the original, one named "testExcel" and the other "Sheet 1" but in the file I copy to, it only has "Sheet 1" with the content within testExcel – Mitchell T Jun 04 '19 at 20:09
  • Meaning you inspected the copied file using Excel? Weird. Have you tried using shutil.copy instead? – Will Jun 04 '19 at 20:12
  • No, but I have attempted to and it said "Unresolved reference to shutil" – Mitchell T Jun 04 '19 at 20:15
  • Hangon a minute, your posted question just changed significantly. Can you confirm where you are having the problem? Is it with copying the file using copyfile or is it the read/write excercise with pandas? – Will Jun 04 '19 at 20:15
  • You need to import `shutil` before running `shutil.copy` – roganjosh Jun 04 '19 at 20:18
  • 1
    @MitchellT you might want to read the first paragraph of the [pandas.to_excel docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html) – Will Jun 04 '19 at 20:22

2 Answers2

1

Check out Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

You can try pd.Excelfile and specify the sheets you want to turn into dataframes from an original file.

xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
aguay091
  • 52
  • 1
  • 8
0

The issue was that

wdf.to_excel(tLoc)

It wouldn't save the sheet names but it saved everything else, however executing the last command for some reason caused it to only save the first sheet. If anyone knows how to save it without using that command please let me know.

Mitchell T
  • 33
  • 7