import requests, base64, xmltodict, json

username = "xxxx"
pw = "xxxx"
host = "xxxx.com"
db_name = "mydb"
layout_name = "mylayout"

credentials = f'{username}:{pw}'
encoded_credentials = base64.b64encode(credentials.encode()).decode()

def sanitize_id(project_id):
    sanitized_id = str(project_id).replace("/", "").strip()
    if sanitized_id.isdigit():
        return int(sanitized_id)
    elif sanitized_id:
        return sanitized_id
    else:
        return None

def sanitize_data(data):
    """Remove or replace problematic characters in the data."""
    if isinstance(data, str):
        # Remove null bytes
        data = data.replace('\x00', '')
        # Ensure the string is properly encoded in UTF-8
        data = data.encode('utf-8', 'ignore').decode('utf-8')
    return data


url = f"https://{host}/fmi/data/vLatest/databases/{db_name}/layouts/{layout_name}/records?_limit=500&_offset=0"
cleandata = []

# Make the GET request
response = requests.get(url, headers=headers)
st.write(response)

if response.status_code == 200:

    data = response.json()
    records = data.get('response', {}).get('data', [])
    
    for record in records:
        field_data = record.get('fieldData', {})
        field_data = {key: sanitize_data(value) for key, value in field_data.items()}        
        record_id = field_data.get('id', None)        # Ensure 'id' is present in the field_data
        
        if record_id is not None:
            sanitized_id = sanitize_id(record_id)
            if sanitized_id is not None:
                invoiceline = {
                    'Id': record_id,
                    'Created by': field_data.get('creation_user'),
                    'Created on': field_data.get('creation_date'),
                    # all fields mapped here...
                }
                cleandata.append(invoiceline)
    
		result = dbconn.write('filemaker', 'some_table', cleandata, pk="Id")