Python Forum
XLSX file with multiple sheets to josn file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
XLSX file with multiple sheets to josn file
#1
Hello

I want to import data in Arangodb so I need .json files. All of my data are in .xlsx file (big ones 10 to 20 MB each) with 25 to 35 sheets each. So I created a loop with this code:

#sheets is alist with all the sheet names from the file 
for i in sheets:
    df = pd.read_excel(file, sheet_name = i, index = None, header = 1)
    json_file = df.to_json(("{}.json").format(i))
I have the following questions:
1. The code works I have some of the .json files to prove it, but after a few of the sheets the program crashes. Can anyone help with why is this happening? I am using jupyter notebook and I got an error that thee kernel is dead.

2. Obviously when the loop is running it reads every time the .xlsx file and creates the new dataframe. Is there a way to load the file only once in the memory and get the dataframes from that instead of loading it every time ?
Reply
#2
For debugging I would comment out line 4 to see if the crash is happening on reading or writing. Then I would add "print(i)" between lines 3 and 4 to see if a particular sheet is causing the choke.
Reply
#3
(Apr-03-2020, 05:59 PM)jefsummers Wrote: For debugging I would comment out line 4 to see if the crash is happening on reading or writing. Then I would add "print(i)" between lines 3 and 4 to see if a particular sheet is causing the choke.

Thank you for your answer.

Unfortunately I don't need debugging. I already know that the crashing happens while reading the file for the n-th time when it starts to read a particularly big sheet. I know this because when I run the loop it always crashes at the 10th sheet (which is one of the biggest) after it has written in .json files the first nine sheets of the spreadsheet.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Export dataframe to xlsx - Error "zipfile.BadZipFile: File is not a zip file" Baggio 10 18,023 Mar-12-2021, 01:02 PM
Last Post: buran
  extracting sublist from a large multiple molecular file juliocollm 2 1,139 May-25-2020, 12:49 PM
Last Post: juliocollm
  How to copy a .csv worksheet into a .xlsx file without the number values turning into YoshikageKira 7 1,595 Mar-28-2020, 10:38 AM
Last Post: buran
  [split] Converting excel file to txt file unexceptionalhobby 2 2,783 Oct-16-2019, 06:34 PM
Last Post: unexceptionalhobby
  Reading Multiple Sheets using Pandas dhiliptcs 1 2,720 Sep-30-2019, 11:26 PM
Last Post: scidam
  large csv to many xlsx containing multiple tabs thatIsTheCase 3 3,038 Nov-27-2018, 02:34 PM
Last Post: thatIsTheCase
  copy one column from csv file and paste into xls file kprogrammer 0 3,151 Nov-03-2018, 04:03 PM
Last Post: kprogrammer
  Converting excel file to txt file muhsin 3 19,481 Oct-13-2017, 07:52 PM
Last Post: nilamo
  Panda Data Frame to Existing Multiple Sheets naveedraza 1 4,761 Jul-11-2017, 12:21 PM
Last Post: naveedraza
  read multiple .xlsx files and text files in a directory BNB 11 22,103 Jun-07-2017, 07:42 AM
Last Post: BNB

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020