Excel json Import Error The Type of Current Preview Value is Too Complex to Display

Question

Main Problem: I'm trying to import .json to excel for pivot table. The data contains 4.000.000 rows and i have to create pivot table for summarize the data (My company uses excel because of company structure, so i have to). When i import the json file, excel shows this error: "The type of current preview value is too complex to display".

Statement:

I'm taking the data from excel (5 sheets, because of excel data limitation i have to merge the data). I use these codes for reading excel datasets:

import numpy as pd
df1 = pd.read_excel(r'C:/Users/...Data Location 1')
df2 = pd.read_excel(r'C:/Users/...Data Location 2')
df3 = pd.read_excel(r'C:/Users/...Data Location 3')
df4 = pd.read_excel(r'C:/Users/...Data Location 4')
df5 = pd.read_excel(r'C:/Users/...Data Location 5')

And i merge and export the data with these codes:

import json
frames = [df1,df2,df3,df4,df5]
data = pd.concat(frames,ignore_index=True)
data.to_json(r'C:/Users/.../data.json')

And finally i am trying to import data.json to excel for pivot table (as query, so data limitation problem in excel will not be a problem anymore). In this way i will be combine the dataset only with one file. But when i import the data "The type of current preview value is too complex to display" error is rising. What should i do? Is there any alternative method?

Thanks for your answer in advance.

Comments

Hey,

Can you provide some code that you have written?