AWS CLI Result TO Excel (AWS Resource Inventory)

Sharing this tutorial on how can we easily convert the aws cli JSON response to a more presentable format.

AWS CLI Response To JSON file

1. Login to your AWS account.
2. Open cloudshell.
3. Run the aws command line list ec2 instances and save the result to a json file by running the following command: aws cli ec2 describe-instances > list-instances.json
4. Make sure that a file named list-instances.json was created by running a simple linux command ls.
5. Download the created file from cloudshell to your local machine. On the upper right of cloudshell click Actions then click Download file.
6. A dialog box will pop up asking for the path of the file. Since the file was created in the user home directory, you can simply put the filename. Then click Download.

Converting JSON Response To Excel (Office 2021)

1. Open Microsoft Excel.
2. Create a blank workbook.
3. Click Data > New Query > From Other Sources > From Web
4. A dialog box will popup and put the absolute path or the location of the download json file.
5. Next, it will now open a new excel window and click Into Table Convert.
6. Next, expand all the value of the columns to a new rows.
7. Confirm the action by clicking Insert button.
8. This action will now expand the value of the list from step 6 of this section.
9. Expands all the Record / List by repeating steps 6 and 7 of this section.
10. Now this is the most repetitive part of this tutorial, you need to expand all the values of each column with a list as value. Repeat steps 6 and 7
11. Once all the values has been expanded, you can now click the Close and Load button on the upper left of the window to load this in a workbook.
12. And your data may be look like this.

There you have it! You have a report! :)