Simplifying Access to SBI Codes with a Custom JSON Solution

Streamlining Access to SBI Codes: A JSON Conversion Tool

Managing SBI (Standard Business Identifier) codes from the traditional Excel files provided by KvK (Kamer van Koophandel) can be cumbersome, especially for developers needing to integrate this data into applications. To resolve this, I've developed a Python script that converts these Excel files into a more manageable JSON format.

The Python Script Explained

The script utilizes the pandas and json libraries to process the Excel file from the KVK website and convert it into a structured JSON file. Here's a breakdown of how it works:

1. Loading the Excel File
The script begins by loading the Excel file that contains the SBI codes. We use pandas for its powerful data handling capabilities.

import pandas as pd 

df = pd.read_excel('./sbi.xlsx')

2. Preparing for Data Extraction
We prepare a list to store the hierarchical data structure of SBI codes. We also set up dictionaries to keep track of current chapters and paragraphs.

data = [] current_chapter = {} current_paragraph = {} dictlist = [];

3. Iterating Through the DataThe script iterates through each row of the Excel file, identifying chapters, paragraphs, and subparagraphs based on specific criteria:

• Chapter Detection: Chapters are identified by a single alphabetical character.
• Paragraph Detection: Paragraphs are noted by two digits.
• Detailed Entries: Any entry longer than two characters that isn't 'nan' is considered a detailed entry.

for index, row in df.iterrows():
    cell_value = str(row.iloc[0]).strip()
    if cell_value.isalpha() and len(cell_value) == 1:
        print("Chapter detected: " + row.iloc[0] + " " + row.iloc[1])
        current_chapter = {
            'code': cell_value,
            'name': row.iloc[1].strip().replace("’","'").replace("'", "''")
        }
    elif cell_value.isdigit() and len(cell_value) == 2:
        print("Paragraph detected " + cell_value + " " + row.iloc[1])
        current_paragraph = {
            'code': cell_value,
            'name': row.iloc[1].strip().replace("’","'").replace("'", "''")
        }
    elif len(cell_value) > 2 and cell_value != 'nan':
        safe_cell_value = cell_value.replace("'", "''")  # Escapes single quotes by doubling them
        safe_iloc_value = row.iloc[1].replace("’","'").replace("'", "''")

        dictlist.append({
            'sbi': cell_value,
            'code': cell_value.replace('.', ''),
            'name': safe_iloc_value.strip(),
            'paragraph': current_paragraph,
            'chapter': current_chapter
        })

4. Creating the JSON File
After collecting all the data, we convert the list into a JSON string and write it to a file.

# Convert the list to JSON
json_data = json.dumps(dictlist, indent=4)

# Write the JSON data to a file
with open('./output.json', 'w') as json_file:
    json_file.write(json_data)

Conclusion

This script dramatically simplifies the task of utilizing SBI codes in software applications by converting the data into a structured, easily accessible JSON format. The final JSON file provides a robust solution for integrating SBI codes with modern web applications and services, enhancing accessibility and ease of use.