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
  Errors if an xlsx file has blank rows in the beginning…. tester_V 1 790 Aug-28-2023, 06:22 PM
Last Post: deanhystad
  Creating a Dataframe from Zenodo zip file with multiple CSVs about Spotify man0s 0 1,326 Apr-26-2022, 01:45 PM
Last Post: man0s
  Export dataframe to xlsx - Error "zipfile.BadZipFile: File is not a zip file" Baggio 10 61,399 Mar-12-2021, 01:02 PM
Last Post: buran
  extracting sublist from a large multiple molecular file juliocollm 2 2,262 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 3,466 Mar-28-2020, 10:38 AM
Last Post: buran
  [split] Converting excel file to txt file unexceptionalhobby 2 4,310 Oct-16-2019, 06:34 PM
Last Post: unexceptionalhobby
  Reading Multiple Sheets using Pandas dhiliptcs 1 4,011 Sep-30-2019, 11:26 PM
Last Post: scidam
  large csv to many xlsx containing multiple tabs thatIsTheCase 3 4,379 Nov-27-2018, 02:34 PM
Last Post: thatIsTheCase
  copy one column from csv file and paste into xls file kprogrammer 0 4,345 Nov-03-2018, 04:03 PM
Last Post: kprogrammer
  Converting excel file to txt file muhsin 3 21,595 Oct-13-2017, 07:52 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

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