import pandas as pd
from dateutil.parser import parse
from datetime import datetime

df = pd.read_excel("2012.xls", skiprows=6, header=None)

#print(df)
with open("hunters2012.json", "w", encoding="utf-8") as f:
    f.write("[")
    i=1
    for index, row in df.iterrows():
        # Преобразуем каждую строку в строку словаря и записываем
        #f.write(str(row.to_dict()) + "\n")
        
        full = str(row[1])
        parts = full.split()
        
        last = parts[0]
        first = parts[1] if len(parts) > 1 else ""
        middle = parts[2] if len(parts) > 2 else ""

        
        if isinstance(row[14], datetime):
            date_entry= str(row[14].date())          
        else:
            date_entry="2012-01-01"
        if isinstance(row[2], datetime):
            birth_date= str(row[2].date())          
        else:
            birth_date="1984-01-01"
        if isinstance(row[6], datetime):
            p_date= str(row[6].date())          
        else:
            p_date="2000-01-01"
        pasport=str(row[5])
        b = pasport.split()
        b = ''.join(b)
        p_ser=b[:4]
        p_num=b[4:]
        birth_place=str(row[3])
        if birth_place=="nan":
            birth_place=""
        birth_place=birth_place.replace('\\','/')
        birth_place=birth_place.replace('"','')
        address=str(row[4])
        if address=="nan":
            address=""
        address=address.replace('\\','/')
        address=address.replace('"','')
        issued_by=str(row[7])
        if issued_by=="nan":
            issued_by=""
        issued_by=issued_by.replace('"','')
        f.write('{"date_entry": "'+date_entry+'", ')
        f.write('"municipality": {"code": "69000000","name": "69000000 - Муниципальные образования Томской области"},')
        f.write(' "surname": "'+last+'", "hunter_name": "'+first+'", "patronymic": "'+middle+'",')
        f.write(' "birth_date": "'+birth_date+'", "birth_place": "'+birth_place+'", "postal_address": "'+address+'", "postal_code": "", "phone": "", "email": "", "address": "'+address+'", "snils_code": "",')
        f.write(' "identity_type": { "code": "29", "name": "Паспорт гражданина РФ" }, "series_passport": "'+p_ser+'", "number_passport": "'+p_num+'",  "date_issue": "'+p_date+'", "issued_by": "'+issued_by+'"},\n')
       # f.write(' "nationality": { "code": "", "name": "" }, "link": "",  "traditional_residence_places": [ { "municipal_district": { "code": "", "name": "" }}], "organization_id": { "organizations_type": { "name": ""}, "unique_inn": "" }},\n')
        i=i+1
    #    if i>5:
     #       break
    f.write("]")
  

print("Данные успешно записаны в output.txt!")