AITT.TECHCA.APP

Code: Select all

import pandas as pd
import json

# Load the JSON file
json_file_path = 'C:\\GSTR2A\\33AAYCS3291C2Z3_R2A_042022_20221216\\returns_16122022_R2A_22BAYCM3291C4Z1_R2A_others_0.json'

# Read the JSON file
with open(json_file_path, 'r') as file:
    data = json.load(file)

# Initialize an empty list to hold the flattened invoice data
invoices = []

# Iterate over each record in the 'b2b' key of the JSON data
for record in data['b2b']:
    # Iterate over each invoice in the 'inv' key of each record
    for inv in record['inv']:
        # Create a dictionary to store the flattened invoice data
        inv_record = {
            'ctin': record.get('ctin'),             # Counterparty's GSTIN
            'cfs': record.get('cfs'),               # Counterparty Filing Status
            'cfs3b': record.get('cfs3b'),           # Counterparty 3B Filing Status
            'val': inv.get('val'),                  # Invoice Value
            'inv_typ': inv.get('inv_typ'),          # Invoice Type
            'pos': inv.get('pos'),                  # Place of Supply
            'idt': inv.get('idt'),                  # Invoice Date
            'rchrg': inv.get('rchrg'),              # Reverse Charge
            'inum': inv.get('inum'),                # Invoice Number
            'chksum': inv.get('chksum')             # Checksum
        }
        # Iterate over each item in the 'itms' key of each invoice
        for item in inv.get('itms', []):
            # Copy the invoice record dictionary and update with item details
            item_details = inv_record.copy()
            item_details.update(item.get('itm_det', {}))
            # Append the combined invoice and item details to the invoices list
            invoices.append(item_details)

# Convert the list of invoices to a pandas DataFrame
df = pd.DataFrame(invoices)

# Generate the output Excel file path by replacing the .json extension with .xlsx
excel_file_path = json_file_path.replace('.json', '.xlsx')

# Save the DataFrame to an Excel file
df.to_excel(excel_file_path, index=False)

print(f"Excel file has been saved to {excel_file_path}")