Це відео не доступне.
Перепрошуємо.
How to Convert JSON to Excel, CSV and Google Sheets - Google Colab
Вставка
- Опубліковано 10 лют 2021
- Learn how to open a JSON file in Excel , import in Google Sheets or convert to a CSV file using Google Colab.
#josn #excel #csv #googlesheets
Best channel there is on Google Sheets. In depth and consise explanations on all sorts of challenges one faces with Google Sheets.
Thank You!
This is an amazing video that simplifies jason to spreadsheet conversion. All that you need is a little experience with Python. I spent 4 hours watching other videos on the built in conversion in Excel and it never worked. I downloaded my google location data for the last year, in order to report my mileage for tax purposes. The IRS would have laughed at me if I gave them a bunch of JSON. Thank you.
It would be a travesty of justice if I went away, having gleaned the information you have shared, WITHOUT SAYING, THANK YOU! Man, thanks for sharing....you just have no idea!😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁😁
This the best explanation I found on UA-cam. Thank you!
My MANNNN!!! You saved me 9 ZILLION hours! You win ALL THE THINGS!!! Well at least you won a subscribe and a like from me. Holy smoke, you are my favorite person this year.
Thanks a lot for this great great video.
Extremely helpful. Worked on the first try. I tried another video by someone else and it was confusing and did not end up working. Thank you for being so clear!!
👍
Thank you
You are number one
Nice! You saved a lot of my time!
Thanks, very useful
Great video, thanks!
Very clear even for a first-timer in python like me. Is there a way to import the csv to google sheets without the index column?
Tried using the df.drop method but it isn't working.
Thank you so much!
It is not working this message appears after following exactly your tutorial NameError Traceback (most recent call last)
same problem, did you find a solution?
Thank you very much!
как же круто ты рассказываешь!
What all changes can i make (code) in my json file to convert it into csv rather than using this colab feature. Is there a video or something that can tell me which all objects and line of code do i have to add to get the desired result. It will be very helpful if you address my question. Thank you!!
Teacher...I got it! 🙌 I got the Formula for the C4:C15 Range to Work the way I wanted. So, here is what I have so far:
Formula Cell: B4 Range: B4:B15 =ARRAYFORMULA(IF((A2:A13)="",""(A4:A15)-OFFSET(A4:A15,-2,0)))
Note: I get 'N/A' if A2:A13 is 15
Formula Cell: C4 Range: C4:C15
=IF(ISEVEN(ROW(C4)),A4-A$2,A4-A$3)
Now all I have to do is figure out how to make it all Dynamic without any ERRORS or N/A's. 🤔
Nothing I do seems to play well with Column 'A' being 14 Rows and Columns 'B' & 'C' 6th 12 Rows!?!?
Great video, Do you know if this will work with a live Json file? Im getting Json data from a scoring app that keeps updating and need to pull that info into an xcel sheet but need it to keep updating the live info
Great! Is there a way of accomplishing the same with apps script?
Yes, but it will be a lot more complicated.
nice fine like best and love
Thanks! is posible merge four or more .JSON files ? and next convert to a one google sheet file?
Yes, you can use Pandas concat to combine them .
combinedData = pd.concat([df1,df2,df3])
@@ExcelGoogleSheets Thanks!
Excellent video !!! Any chance you could share the source files or raw data for us to study and practice in the future? many thanks
Hi, my json data has so many levels. It shows up as having only one column when I import it. What do I do?
Great video, thank you. I wrote same code, unfortunately didn't work for me!
Great video for the conversion and Python learning. Is there any way though to automate the process so a JSON file stored in Google Drive (without it being made publicly accessible!) can be converted, re-stored in Google Drive and imported into Sheets. What I need is a system where I can over-write an existing file with a new version from time to time, and ideally have the new data automatically imported into a Sheet, or at the most, running a script like this once each time without needing to load files, export files, then import files each time just to get a refresh.
Possible?
Yes, possible. but not with Colab/Python. You would just have to use Apps Script/JavaScript DriveApp and scheduled triggers.
how do i save my json file from Mozilla fox in that format. mine keeps failing
I'm sorry, is Pandas your business? Thank you for the video.
Do you have a video that explains how to join several .json files and export it as a single file .json?
I try to merge this files and working when Print, but when y try to export have a error
import pandas as pd
import json
Nomi1 = pd.read_json("Nomi1.json")["data"]
Nomi2 = pd.read_json("Nomi2.json")["data"]
Nomi3 = pd.read_json("Nomi3.json")["data"]
Nomi4 = pd.read_json("Nomi4.json")["data"]
combi = pd.concat([Nomi1,Nomi2,Nomi3,Nomi4])
combi.to_json('combi.json')
ValueError: DataFrame index must be unique for orient='columns'.
maybe you know what i should add to my code?
Before the last line try
combi.reset_index(inplace=True)
@@ExcelGoogleSheets Thanks! my friend now have all data in one file :D
PLEASE PLEASE include the source code to your projects like for app script & crud projects
It will be really really helpful as its really tedious to scrub through the entire video just to clear a small doubt or a mistake.. it will be much much easier to learn that way
I know some people might miss use it like copy paste the code but those people do that anyway
So please include source code files
Teacher, what i am working on is 3 Columns. A,B, and C.
A1 is: Blank, B1 is: "Each Way" C1 is: "Round Trip"
A2 is: 178777.7
B2 is: Blank C2 is: Blank
A3 is: 9668.6
B3 is: "Card #" C3 is: "1"
A4 is:178815.0 B4 is: C4 is:
A5iIs: 9706.0
B5 is: C4 is:
A6 is: 178819.0
B6 is: C4 is:
A7 is: 710.0
B7 is: C4 is:
A8 is: 178826.5 B98 is C4 is: :
A9 Is: 9717.4
B9 is: C4 is:
A10 is:178827.8
B10 is: C4 is:
A11 Is:19718.8
B11 is: C4 is:
A12 is:178833.6
B13 is: C4 is:
A13 Is: 9724.5
B14 is: C4 is:
A14 Is: 178834.0
B15 is: C4 is:
A15 is: 9724.9 B16 is: C4 is:
I am trying to get this to Auto-Expand with an Array Formula without Subtraction Errors or Blank Cell Errors if Data in any Cells are Blank In Column 'A' .Using your Trickery from your INVOICE Video and OFFSET Video, plus your ITERATIVE Video and the ISEVEN, ISODD Videos, along with your MOD Video and others.
Cell B4 should have the Difference between: A4 and A2. ie: =A4-A2
Cell B5 should have the Difference between: A5 and A3. ie: =A5-A3
Cell B6 should have the Difference between: A6 and A4. ie: =A6-A4
Cell B7 should have the Difference between: A7 and A5. ie: =A7-A5 and so on.
Cell C4 should have the Difference between: A4 and A2. ie: =A4-$A$2
Cell C5 should have the Difference between: A5 and A2. ie: =A5-$A$2
Cell C6 should have the Difference between: A6 and A2. ie: =A6-$A$2
Cell C7 should have the Difference between: A7 and A2. ie: =A7-$A$2 and so on.
JR
So what does this =ARRAYFORMULA((--(OFFSET(C2,0,-1):OFFSET(C2,13,-1)"")*(--(OFFSET(C2,2,-1):OFFSET(C2,15,-1)"")))). have to do with that?
@@ExcelGoogleSheets
Teacher...Great Question! I got that Formula from your Video that Teaches one cannot use an OR Function with an Arrayformula Function in Google Sheets. And I agree it's a Beautiful Bit of Trickery! :) However, it does not Work in this case. Where one should get a '1' in whatever Cell of the Array 'A2:A15' that has a Number in it, one gets one less than one should. Anyway, this is my Secondary Problem. My Primary Problem is: I cannot figure out a Formula that will perform the Math for Column 'C'. The Formula: =IF(ISEVEN($A$2)+1,A4-A2,"") in Cell 'B4' (B4:B15) works, as does the Formula: =ARRAYFORMULA(IF((A2:A13)="",""(A4:A15)-OFFSET(A4:A15,-2,0))) in Cell 'B4' (B4:B15).
Where one really runs into a Snag is the Formula for Column 'C'! The Formula needs to Jump Back and Forth through the Range 'C4:C15' and do it Dynamically!?!? 🤣
Does one need a MOD Function and/or an INDEX & MATCH FUNCTION in addition to the ISEVEN Function and OFFSET Function ???
I have one more level which is called "data"
Example:
data:
0:
name: louis
age: 20
city: peru
1:
2:
...
And when I try to export it, it brings me the header of each of the data and does not separate it by commas as shown in the video, maybe you have an email to which I can ask you?
See the first answer here
stackoverflow.com/questions/40588852/pandas-read-nested-json
Sir good sock itams multiple selection posting google app script vedio send me
Im getting an error: All arrays must be of the same length
What's your code?
@@ExcelGoogleSheets Same as yours. Must be something to do with my JSON. Thanks though. Not your fault.
you know not everyone on YT is a developer
nice fine like best and love
nice fine like best and love