You have a file ending in .json, perhaps because a client emailed it or your ERP asked for it, and someone wants a filterable sheet. Double-clicking it opens Notepad, not a table. JSON is plain text with nesting: suppliers sit inside sections, invoices inside suppliers, amounts inside invoices. Excel can flatten that, but only if you tell it which lists to unfold and which data types to keep.
Do you need to convert at all?
The portal lets you generate the same GSTR-2B as an Excel file or as a JSON file from one download screen. Our guides on downloading GSTR-2B from the portal and the step-by-step download guide cover the clicks, and the Excel download guide walks through the tabs. If you can log in for that GSTIN and month, the Excel file is less work. GSTN lists B2B, B2BA, CDNR and CDNRA among the tables of GSTR-2B; the Excel you download may have more tabs, so check the tab names in your own file.
Convert only when one of these is true:
- You were sent the JSON alone. A client, or an ERP export, gave you the file and you have no portal login for that GSTIN.
- You want every section in one table. A stacked table with a column saying which section each row came from is easier to filter and pivot than separate tabs.
- You are combining months. The full financial year guide explains how to gather twelve files, and the steps below work on each of them.
Convert the final file, not the first one. GSTR-2B for a month is generated on the 14th of the following month. If you then change IMS actions and recompute before filing GSTR-3B, the figures move. A JSON saved earlier no longer matches what the portal shows, so download afresh; see how to recompute GSTR-2B.
What is inside the JSON file
The names and formats below come from our own synthetic test files, not from an official GSTN specification or a real client file; confirm them against your file. Open it in Notepad and check this shape before you start.
| Section in the JSON | What it holds | What to remember |
|---|---|---|
| b2b | Invoices from registered suppliers | Supplier GSTIN is ctin, invoice number is inum |
| b2ba | Amendments to earlier B2B invoices | Amended records carry the original invoice number (oinum) beside the new one |
| cdnr | Credit and debit notes | The list under each supplier is called nt, not inv; note type is nttyp (C or D) |
| cdnra | Amendments to those notes | Same shape as cdnr |
Your file may hold further sections (for example ISD or imports). Open it, list every top-level name under docdata, and make sure each one ends up in your sheet.
Convert with Power Query, step by step
This uses only features built into Excel for Windows (2016 or later, and Microsoft 365). Menu names can differ slightly between versions.
- Prepare the file. If the portal gave you a zip, extract it first and keep the .json in a folder you will remember.
- Load it. In a blank workbook go to the Data tab, choose Get Data, then From File, then From JSON, and pick the file. The Power Query editor opens on the top level of the file.
- Drill down. Click Record next to data, then Record next to docdata. Click List next to b2b. You are now looking at one row per supplier, each still shown as Record.
- Unfold suppliers and invoices. On the Transform tab choose Into Table (accept the defaults). Click the expand icon (two arrows) in the column header and tick ctin, trdnm and inv, with the prefix box unticked. Expand inv again with Expand to New Rows, then expand the record to bring in inum, dt, val, itcavl and the amount fields. If you see an items list, expand that to new rows as well.
- Set the types, in this order. GSTIN and invoice number: Text. Date: check how it is written in your file (in our test files day comes first), then select the column, Transform tab, Data Type, Using Locale, then Date and English (India). Amounts: Decimal Number. If Power Query added a step called Changed Type that turned invoice numbers into numbers, delete that step.
- Label the section. Add a column named Section with the value B2B.
- Repeat for the other sections. Duplicate the query, change the b2b step to b2ba, cdnr or cdnra (use nt in place of inv for the two note sections), then use Home, Append Queries to stack them.
- Load and check. Choose Close & Load. Compare your sheet's tax total with the totals the portal shows for the same GSTR-2B before you trust it.
The columns you end up with
| Column | JSON name | Keep as | Watch for |
|---|---|---|---|
| Supplier GSTIN | ctin | Text | Trailing spaces and lower case break matching later; trim and capitalise |
| Supplier name | trdnm | Text | Not a matching key; names differ between books and portal |
| Invoice number | inum | Text | Never a number; see the pitfalls below |
| Invoice date | dt | Date | Check how the date is written in your own file |
| Invoice value | val | Decimal | Taxable value plus tax |
| Taxable value, IGST, CGST, SGST, cess | txval, igst, cgst, sgst, cess | Decimal | If the amounts sit in a nested list, expanding it gives one row per entry |
| ITC available flag | itcavl | Text | Keep as Text; check in your own file or GSTN documentation what each value means before you exclude rows from an ITC total |
Worked example: four invoices after conversion
These are made-up rows for August 2026 from three suppliers, as they would look in the loaded table. Check the arithmetic the way you should check your own: every row's tax is the rate times the taxable value, and the totals row equals the sum of the rows.
| Supplier GSTIN | Invoice no. | Date | Taxable | IGST | CGST | SGST | Total tax |
|---|---|---|---|---|---|---|---|
| 27AABCF1234A1ZP | INV-1000 | 04-08-2026 | ₹1,12,500 | Nil | ₹2,812.50 | ₹2,812.50 | ₹5,625.00 |
| 27AABCF1234A1ZP | INV-1001 | 08-08-2026 | ₹25,000 | Nil | ₹1,500.00 | ₹1,500.00 | ₹3,000.00 |
| 29AAECG5678C1ZD | 000452 | 11-08-2026 | ₹80,000 | ₹14,400.00 | Nil | Nil | ₹14,400.00 |
| 24AAFCM9012D1ZH | GT/26-27/089 | 19-08-2026 | ₹40,000 | Nil | ₹3,600.00 | ₹3,600.00 | ₹7,200.00 |
| Total | ₹2,57,500 | ₹14,400.00 | ₹7,912.50 | ₹7,912.50 | ₹30,225.00 |
Three things in this small table go wrong if you skip the type step. The invoice number 000452 becomes 452 and no longer equals what your books say. If your file writes dates day first, as ours do, 11-08-2026 is read as 8 November under a month-first setting, while 19-08-2026 cannot be a month, so it stays as text and your date column is half dates, half text. And GT/26-27/089 is a perfectly valid number: Rule 46(b) allows up to sixteen characters made of letters, digits, hyphen and slash, so a converter or formula that strips the punctuation is changing the record, not tidying it.
Pitfalls that survive a correct conversion
- Several rows per invoice. If your file lists the amounts in a nested list such as items, one invoice can become two or three rows. Use Group By on supplier GSTIN and invoice number and sum the amounts before you compare with your purchase register; otherwise the invoice looks duplicated or the tax looks short.
- Amended records. A B2BA row is a replacement for an earlier invoice, so it can sit in your sheet next to the original with a different number. Keep the original-number column; you will need it to explain a match. Our note on invoice number mismatches shows how the same invoice appears under two spellings.
- Copy-paste after loading. Power Query keeps types, but pasting the table into another workbook or saving as CSV and reopening lets Excel guess again. Keep the loaded table as your source and refresh it, rather than round-tripping through CSV.
- Totals that cover every row. The sheet holds every row in the file. Before comparing its total with an ITC figure on the portal, check what the ITC flag values mean in your file and whether the portal figure covers the same rows.
After the conversion: matching
A converted sheet only tells you what is in GSTR-2B. Reconciling means comparing it with your purchase register on supplier GSTIN and invoice number, then explaining every difference. If you plan to do that with formulas, read reconciling GSTR-2B in Excel without VLOOKUP pain first; the conversion above gives you the clean 2B side of that comparison.
Skip the conversion
Our reconciliation tool accepts the GSTR-2B JSON or the Excel file directly, so you can match against your purchase register without building a sheet first. Your GSTR-2B and purchase register files are read in your browser and never uploaded to any server.
Frequently asked questions
Can Excel open a GSTR-2B JSON file directly?
Not by double-clicking. Use the Data tab, then Get Data, then From File, then From JSON. That loads the file into Power Query, where you expand the nested lists into rows and load the result as a table.
Is the Excel file from the portal the same as a converted JSON?
Both come from the same GSTR-2B. The portal's Excel file is organised in tabs by table, and may have more tabs than B2B, B2BA, CDNR and CDNRA. A conversion contains only what you expanded, so check that every section of the JSON ended up in your sheet.
Why did my invoice dates change month after conversion?
In our test files, dates are text in day-month-year order. If Excel reads them under a month-day-year setting, 04-08-2026 becomes 8 April instead of 4 August. Convert the column with the English (India) locale, as shown in step 5 above.
Related guides
- GSTR-2B download: the portal route and the file options
- GSTR-2B JSON to Excel converter: whether to use an online converter
- GSTR-2B JSON to Excel conversion guide
- How to import the GSTR-2B JSON file into Tally
- GSTR-2B not generated or not showing: what to check and what to do
- How to Download GSTR-2B in PDF Format: step-by-Step Portal Guide & Limitations
- How to fix excel to json error in GSTR-1 offline tool: troubleshooting guide
Last checked: 30 Sep 2026. Sources: GSTN FAQ on GSTR-2B tables, GSTN return user guide (GSTR-2B generation), GSTN IMS FAQ, Rule 46, CGST Rules.