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.
