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}")