0

I have an excel workbook with 36 sheets (not my fault). The code below 'works', reads in the file but only the first sheet. I concat and send to csv and the result is only 'Sheet1". Thoughts?

import pandas as pd

Data = pd.read_excel(open("P://XXDrives//Folder_1//Folder_2//XX.xlsx", "rb"), sheet_name = None)

pd.concat([Data], axis=0, ignore_index=True)

Data.to_csv('C://Users//MyName//Desktop//test.csv')
RR33
  • 139
  • 2
  • 12
  • 3
    Reassign the concat.. `Data = pd.concat([Data], axis=0, ignore_index=True)` – rafaelc Sep 16 '18 at 06:40
  • 1
    @pyd Take a look [here](https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook) (its been asked a couple of times) ;) – rafaelc Sep 16 '18 at 06:52
  • @RR33 Are you trying to append all 35 sheets together? – Karvy1 Sep 16 '18 at 06:57
  • Still returns only one sheet of data per the reassign. Not sure what you mean about the second question. I tried pd.read_excel(path only) and got the same result. – RR33 Sep 16 '18 at 06:58
  • @RR33 Just posted my solution to this. Let me know if that helps. If you trying to append all 35 sheets together, I will update the solution accordingly. – Karvy1 Sep 16 '18 at 07:00
  • All sheets together vertically. Each sheet is a different month formatted the same way. – RR33 Sep 16 '18 at 07:04
  • 1
    Upgrade to 0.23.4. There was a bug regarding this parameter. – ayhan Sep 16 '18 at 07:04
  • @RR33 Just updated my answer. I was in the same shoes as yours few days back :) Let me know if the answer helps. – Karvy1 Sep 16 '18 at 07:18
  • Upgrade Completed along with Anoconda up grade. Ran same script and got this error: TypeError: cannot concatenate object of type ""; only pd.Series, pd.DataFrame, and pd.Panel (deprecated) objs are valid – RR33 Sep 16 '18 at 07:32
  • 1
    Yeah you need to remove brackets now that you have a dictionary. It should be `pd.concat(data, axis=0, ignore_index=True)` – ayhan Sep 16 '18 at 07:38
  • @user2285236: good god almighty, that took awhile. Thanks. Works great. Now have to add a column in each sheet that takes the sheet 'name' and returns a date for all rows in the sheets. So, there may be another post. Cheers – RR33 Sep 16 '18 at 07:57

1 Answers1

-1

The code is reading only one sheet because you have specified sheet_name = None. If you want it to read specific sheet (lets say a sheet named - 'my_sheet_name'), use sheet_name = 'my_sheet_name'

Update: If you want to append all 36 sheets vertically. 

import pandas as pd
output_df = pd.read_excel(open("P://XXDrives//Folder_1//Folder_2//XX.xlsx", "rb"), sheet_name = 'sheet_1') # Use 1st sheet to populate your output_df
sheets = ['sheet_2', 'sheet_3', 'sheet_4'] #include the names of all the sheets here except the first one.

for i in sheets:
     Data = pd.read_excel(open("P://XXDrives//Folder_1//Folder_2//XX.xlsx", "rb"), sheet_name = i)
     output_df = pd.concat([output_df, Data], axis=0, ignore_index=True)

output_df.to_csv('C://Users//MyName//Desktop//test.csv')
Karvy1
  • 959
  • 6
  • 14
  • 25