AWS CLI Result TO Excel (AWS Resource Inventory)
Tags: aws, productivity, devops, tutorial
Published: 2022-08-17 05:43:00

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**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2maypjb1v329zvhsf722.png)
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`
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u9wt5jmqv3cjblizl6wn.png)
4. Make sure that a file named `list-instances.json` was created by running a simple linux command `ls`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ievtppol27e825oyhdhd.png)
5. Download the created file from **cloudshell** to your **local machine**. On the upper right of **cloudshell** click **Actions** then click **Download file**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cxmx5ix1onxleayryt6z.png)
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**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ktoff5v2zj063rhsjy89.png)

## 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`
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hoa1jjo12hjfrsnlm8fv.png)
4. A `dialog` box will popup and put the absolute path or the location of the download json file.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zn6wm7pknqrdofz3ii8y.png)
5. Next, it will now open a new **excel** window and click **Into Table Convert**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvk7unjpnbjtf6td15ac.png)
6. Next, expand all the value of the columns to a new rows.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pnsg5tcsnptomptocy36.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0tq0zgtmg65crnradnag.png)
7. Confirm the action by clicking **Insert** button.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j2usafrlauwjuuoi9yxv.png)
8. This action will now expand the **value** of the list from **step 6** of this section.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0c76tsbaidircplpl2v2.png)
9. Expands all the `Record` / `List` by repeating **steps 6**
and **7** of this section.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/242dwcaivifdoegovqn2.png)
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**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ler665bys0lomgncmnfu.png)
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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/67vxa2587uzqubj6e84s.png)
12. And your data may be look like this.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p6rbmpmziyfbr76u6zrt.png)
  
# There you have it! You have a report! :)