I have data that is in a big text file that looks like this:
{"id": 111, "role": "manager", "name": "John doe"} with every "row" of info separated by swirly brackets. How do I get this to be a table in excel? Thanks in advance!
Asked
Active
Viewed 69 times
-1
Abhishek Aryan
- 19,936
- 8
- 46
- 65
user7607346
- 1
- 1
-
It is all in one cell? – ToMakPo Feb 22 '17 at 20:53
-
It's just one big text file, it's not even in excel. So yes ultimately in one cell. What would be rows are delimited by the {} – user7607346 Feb 22 '17 at 20:55
-
It looks like you have row and column info. What symbol is each of the rows separated by? Can you give a larger sample of the data? – ToMakPo Feb 22 '17 at 20:56
-
Never mind. I just re-read the question. – ToMakPo Feb 22 '17 at 20:57
-
When you copy and paste it into excel is it all in one row or multiple rows within one column? – ToMakPo Feb 22 '17 at 20:58
-
For Excel versions that support Power Query, there are few ways to read JSON http://superuser.com/questions/977249/loading-a-json-file-into-power-query/977376#977376 – Slai Feb 22 '17 at 21:10
-
Possible duplicate of [Import Json in Excel](http://stackoverflow.com/questions/22237345/import-json-in-excel) – stenci Feb 22 '17 at 21:29
-
It is called JSON file, just look on Google how to open it and manage data with Excel. – Nicolaesse Feb 22 '17 at 22:53
2 Answers
1
You have what is called a JSON object in the text file and it needs to be converted to a CSV file to be opened in Excel. I would use this tool to convert it to CSV and from there you can start modifying data in Excel. Hope it helps :)
EDIT: There is a PayPal after 1mb file size so use https://konklone.io/json/ to convert larger files for free.
Teyler Halama
- 103
- 9
-
If this answer works out, please set it as the correct answer :) – Teyler Halama Feb 22 '17 at 20:58
-
-
Click the check to the left of my answer and press the up arrow :) – Teyler Halama Feb 22 '17 at 21:18
-
Also I used a different converter as the one you linked had a paywall after 1mb file size - I used https://konklone.io/json/ – user7607346 Feb 22 '17 at 21:19
-
-
I can't believe nobody mentioned Notepad and a bit of old skool Find and Replace. Don't even have to save as a CSV file if you put in your own TABs just copy and paste into Excel. Whatever they're charging, I'll do it for half the price ;) – Gordon Feb 22 '17 at 23:14
1
Here is how I would do it:
1>> copy the information into cell A1. (if it puts it into multiple rows then skip to step 8.)
2>> on the top there are a few tabs. go to the one that says Data. Then go to Text to Columns
3>> then click on the button that says Delimited and then click next.
4>> then check the box that says Other and in the text box put a { and hit finish
5>> next, highlight and copy all of the cells.
6>> click on cell A2. Right click and select Transpose.
7>> now clear the top row.
8>> next, select column A and repeat the steps 2 and 3. Go to Data > Text to Columns > Delimited > Next.
9>> check the box named Comma, change the input for other to :, and click next.
10>> for each of the columns with the column names, select the Do not import column (skip) option
11>> hit Ctrl+F and select the Replace tab.
12>> in the Find what: input, put " and leave the Replace with: input blank. Click Replace All and okay.
13>> repeat step 12 replacing the Find what: input with "}.
14>> next, add the column headers in the first row.
15>> with any of the data cells selected, hit Ctrl+L and click OK.
16>> now all of your data is in a nice organized table.
ToMakPo
- 855
- 7
- 27