Data Transformation

Overview

The invoice processing system transforms Excel spreadsheet rows into structured JSON invoices using the transformar_fila() function. Each row represents one customer invoice with columns for customer data, amounts, dates, and service details.

Transformation Flow

The transformation process follows this pipeline:

Step 1: Reading Excel Data

The API endpoint receives an Excel file and processes it:

main.py:252-255

@app.post("/process_excel")
async def process_excel(file: UploadFile = File(...)):
    contents = await file.read()
    df = pd.read_excel(io.BytesIO(contents)).fillna("")

The system:

Accepts Excel files via HTTP POST
Reads all rows into a pandas DataFrame
Fills empty cells with empty strings to avoid null-handling issues

Step 2: Row Iteration and Transformation

Each row is transformed individually:

main.py:262-263

for idx, row in df.iterrows():
    data = transformar_fila(row)

The transformar_fila() Function

This is the core transformation function that maps Excel columns to the invoice JSON schema.

Date Formatting

The function starts by parsing and formatting three date fields:

main.py:75-77

fecha_emision = fmt_fecha(row["Fecha Emision"])
fecha_vencimiento = fmt_fecha(row["Fecha Vencimiento"])
fecha_pago = fmt_fecha(row["Fecha Pago"], "%d/%m/%Y")

The fmt_fecha() helper function:

Handles missing/empty dates gracefully
Converts various date formats to DD/MM/YYYY
Returns None for invalid dates

def fmt_fecha(valor, formato="%d/%m/%Y"):
    if pd.isna(valor) or valor == "":
        return None
    try:
        fecha = pd.to_datetime(valor, dayfirst=True, errors="coerce")
        if pd.isna(fecha):
            return valor
        return fecha.strftime(formato)
    except:
        return valor

Currency Calculations

The function calculates amounts in both currencies (BSD and USD):

main.py:79-84

monto_bs = round(float(row["bolivares"]), 2)
monto_usd = round(float(row["Total"]), 0)
bolivares_sin_iva = round(float(row["bolivares sin iva"]), 2)
precio_sin_iva = round(float(row["precio sin iva"]), 2)
iva_bs = round(monto_bs - bolivares_sin_iva, 2)
iva_usd = round(monto_usd - precio_sin_iva, 2)

Calculation Logic:

IVA (tax) is calculated by subtracting the base amount from the total
BSD amounts are rounded to 2 decimal places
USD amounts are rounded to 0 decimal places (whole numbers)
All conversions use explicit float() casting to handle numeric strings

Field Mappings

Excel Column	Calculation	JSON Field (BSD)	JSON Field (USD)
`bolivares`	Round to 2 decimals	`Totales.TotalAPagar`	-
`Total`	Round to 0 decimals	-	`TotalesOtraMoneda.TotalAPagar`
`bolivares sin iva`	Round to 2 decimals	`Totales.Subtotal`	-
`precio sin iva`	Round to 2 decimals	-	`TotalesOtraMoneda.Subtotal`
Calculated	`monto_bs - bolivares_sin_iva`	`Totales.TotalIVA`	-
Calculated	`monto_usd - precio_sin_iva`	-	`TotalesOtraMoneda.TotalIVA`

Random navigation/usage data is generated for each invoice:

main.py:86-92

navegacion1 = random.randint(1000, 6000)
navegacion2 = random.randint(1000, 6000)
navegacion3 = random.randint(1000, 6000)
navegacion4 = random.randint(1000, 6000)
navegacion5 = random.randint(1000, 6000)
promedio = round((navegacion1 + navegacion2 + navegacion3 + navegacion4 + navegacion5) / 5, 0)

These values are generated randomly (1000-6000 range) for demonstration purposes. In production, these should come from actual usage tracking data.

The navigation data appears in the InfoAdicional section:

main.py:236-242

{"Campo": "Cmes1", "Valor": str(navegacion1)},
{"Campo": "Cmes2", "Valor": str(navegacion2)},
{"Campo": "Cmes3", "Valor": str(navegacion3)},
{"Campo": "Cmes4", "Valor": str(navegacion4)},
{"Campo": "Cmes5", "Valor": str(navegacion5)},
{"Campo": "Promedio", "Valor": str(int(promedio))}

Complete Field Mappings

Document Identification Fields

main.py:97-120

"IdentificacionDocumento": {
    "TipoDocumento": "01",
    "NumeroDocumento": str(row["Correlativo"]),
    "FechaEmision": fecha_emision,
    "FechaVencimiento": fecha_vencimiento,
    "HoraEmision": "10:00:00 am",
    "Anulado": False,
    "TipoDePago": "Inmediato",
    "Serie": "",
    "Sucursal": "002",
    "TipoDeVenta": "Interna",
    "Moneda": "BSD",
    "TransaccionId": f"FA{row['Correlativo']}",
    ...
}

Excel to JSON Mapping Table

Excel Column	Type	Transformation	JSON Path
`Correlativo`	String	Convert to string	`IdentificacionDocumento.NumeroDocumento`
`Correlativo`	String	Format as `"FA{value}"`	`IdentificacionDocumento.TransaccionId`
`Fecha Emision`	Date	Format as DD/MM/YYYY	`IdentificacionDocumento.FechaEmision`
`Fecha Vencimiento`	Date	Format as DD/MM/YYYY	`IdentificacionDocumento.FechaVencimiento`
-	Static	Always `"01"`	`IdentificacionDocumento.TipoDocumento`
-	Static	Always `"BSD"`	`IdentificacionDocumento.Moneda`
-	Static	Always `"002"`	`IdentificacionDocumento.Sucursal`

Buyer (Customer) Fields

main.py:123-134

"Comprador": {
    "TipoIdentificacion": str(row["Documento"]),
    "NumeroIdentificacion": str(row["DNI/C.I./C.C./IFE"]),
    "RazonSocial": row["Cliente"],
    "Direccion": row["Dirección"],
    "Pais": "VE",
    "Telefono": [str(row["Telefono"])],
    "Correo": [row["Correo"]],
    ...
}

Excel Column	Transformation	JSON Path
`Documento`	Convert to string	`Comprador.TipoIdentificacion`
`DNI/C.I./C.C./IFE`	Convert to string	`Comprador.NumeroIdentificacion`
`Cliente`	Direct mapping	`Comprador.RazonSocial`
`Dirección`	Direct mapping	`Comprador.Direccion`
`Telefono`	Convert to string, wrap in array	`Comprador.Telefono`
`Correo`	Wrap in array	`Comprador.Correo`

Totals Section (BSD)

main.py:137-172

"Totales": {
    "NroItems": "1",
    "MontoGravadoTotal": str(monto_bs),
    "SubtotalAntesDescuento": str(bolivares_sin_iva),
    "Subtotal": str(bolivares_sin_iva),
    "TotalIVA": str(iva_bs),
    "MontoTotalConIVA": str(monto_bs),
    "TotalAPagar": str(monto_bs),
    "MontoEnLetras": monto_a_letras_b(monto_bs),
    ...
}

Amounts are converted from calculated floats to strings for JSON serialization.

Totals Section (USD)

main.py:174-198

"TotalesOtraMoneda": {
    "Moneda": "USD",
    "TipoCambio": str(row["Tasa"]),
    "MontoGravadoTotal": str(precio_sin_iva),
    "Subtotal": str(precio_sin_iva),
    "TotalAPagar": str(monto_usd),
    "TotalIVA": str(iva_usd),
    "MontoTotalConIVA": str(monto_usd),
    "MontoEnLetras": monto_a_letras(monto_usd),
    ...
}

The exchange rate (Tasa) from Excel is included to document the conversion factor.

Line Items

main.py:201-224

"DetallesItems": [
    {
        "NumeroLinea": "1",
        "CodigoPLU": "005",
        "IndicadorBienoServicio": "2",
        "Descripcion": row["Plan"],
        "Cantidad": "1",
        "UnidadMedida": "4L",
        "PrecioUnitario": str(bolivares_sin_iva),
        "PrecioItem": str(bolivares_sin_iva),
        "TasaIVA": "16",
        "ValorIVA": str(iva_bs),
        "ValorTotalItem": str(bolivares_sin_iva),
        ...
    }
]

Excel Column	Transformation	JSON Path
`Plan`	Direct mapping	`DetallesItems[0].Descripcion`
`bolivares sin iva`	Convert to string	`DetallesItems[0].PrecioUnitario`
Calculated `iva_bs`	Convert to string	`DetallesItems[0].ValorIVA`

Additional Information

main.py:228-243

"InfoAdicional": [
    {"Campo": "Contrato", "Valor": str(row["ID Servicio"])},
    {"Campo": "Mes1", "Valor": "JUL"},
    {"Campo": "Mes2", "Valor": "AGO"},
    {"Campo": "Cmes1", "Valor": str(navegacion1)},
    {"Campo": "Cmes2", "Valor": str(navegacion2)},
    {"Campo": "Promedio", "Valor": str(int(promedio))},
    ...
]

Batch Organization

Invoices are organized into batches of 100 for efficient processing:

main.py:262-271

for idx, row in df.iterrows():
    data = transformar_fila(row)
    correlativo = str(row["Correlativo"]).zfill(6)
    
    lote_num = (idx // 100) + 1
    lote_nombre = f"J408185431-{hoy}-{str(lote_num).zfill(3)}"
    folder = f"FAC-{hoy}/{lote_nombre}/"
    
    filename = f"{folder}0{correlativo}.json"
    zf.writestr(filename, json.dumps(data, indent=4, ensure_ascii=False))

Batch Structure

Calculate Batch Number

lote_num = (idx // 100) + 1

Rows 0-99 → Batch 1
Rows 100-199 → Batch 2
Rows 200-299 → Batch 3

Format Batch Name

J408185431-{YYYYMMDD}-{batch_number}Example: J408185431-20250309-001

Create Folder Structure

FAC-{YYYYMMDD}/{batch_name}/Example: FAC-20250309/J408185431-20250309-001/

Generate Filename

0{correlativo}.jsonCorrelativo is zero-padded to 6 digits:

Invoice 123 → 0000123.json
Invoice 6746 → 0006746.json

Example Output Structure

FAC-20250309.zip
├── FAC-20250309/
│   ├── J408185431-20250309-001/
│   │   ├── 0000001.json
│   │   ├── 0000002.json
│   │   └── ... (100 files)
│   ├── J408185431-20250309-002/
│   │   ├── 0000101.json
│   │   ├── 0000102.json
│   │   └── ... (100 files)
│   └── J408185431-20250309-003/
│       └── ... (remaining files)

Transformation Example

Input: Excel Row

Correlativo	Cliente	DNI/C.I./C.C./IFE	Plan	bolivares	Total	Tasa	Fecha Emision
6746	CARLOS ALBERTO SOLANO	14965208	PLAN OPTIMO 300 MBPS	4494.49	29	154.9825	09/09/2025

Output: JSON Invoice

View Generated JSON

{
  "DocumentoElectronico": {
    "Encabezado": {
      "IdentificacionDocumento": {
        "TipoDocumento": "01",
        "NumeroDocumento": "6746",
        "FechaEmision": "09/09/2025",
        "TransaccionId": "FA6746",
        "Moneda": "BSD"
      },
      "Comprador": {
        "NumeroIdentificacion": "14965208",
        "RazonSocial": "CARLOS ALBERTO SOLANO"
      },
      "Totales": {
        "Subtotal": "3874.56",
        "TotalIVA": "619.93",
        "TotalAPagar": "4494.49"
      },
      "TotalesOtraMoneda": {
        "Moneda": "USD",
        "TipoCambio": "154.9825",
        "Subtotal": "25.0",
        "TotalIVA": "4.0",
        "TotalAPagar": "29.0"
      }
    },
    "DetallesItems": [
      {
        "Descripcion": "PLAN OPTIMO 300 MBPS",
        "PrecioUnitario": "3874.56",
        "ValorIVA": "619.93"
      }
    ],
    "InfoAdicional": [
      {"Campo": "Contrato", "Valor": "2802"}
    ]
  }
}

Data Type Conversions

All numeric and date values are converted to strings for JSON serialization:

Python Type	Conversion Method	Example
`float`	`str(round(value, 2))`	`3874.56` → `"3874.56"`
`int`	`str(value)`	`6746` → `"6746"`
`datetime`	`strftime("%d/%m/%Y")`	`2025-09-09` → `"09/09/2025"`
`string`	Direct or `str()` cast	`"CARLOS"` → `"CARLOS"`

String conversion is required because the JSON schema expects all numeric fields as strings, likely for precision and compatibility with tax authority systems.

Getting Started

Core Concepts

Guides

Overview

Transformation Flow

Step 1: Reading Excel Data

Step 2: Row Iteration and Transformation

The transformar_fila() Function

Date Formatting

Currency Calculations

Field Mappings

Navigation Data Generation

Complete Field Mappings

Document Identification Fields

Buyer (Customer) Fields

Totals Section (BSD)

Totals Section (USD)

Line Items

Additional Information

Batch Organization

Batch Structure

Example Output Structure

Transformation Example

Input: Excel Row

Output: JSON Invoice

Data Type Conversions

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Guides

​Overview

​Transformation Flow

​Step 1: Reading Excel Data

​Step 2: Row Iteration and Transformation

​The transformar_fila() Function

​Date Formatting

​Currency Calculations

​Field Mappings

​Navigation Data Generation

​Complete Field Mappings

​Document Identification Fields

​Buyer (Customer) Fields

​Totals Section (BSD)

​Totals Section (USD)

​Line Items

​Additional Information

​Batch Organization

​Batch Structure

​Example Output Structure

​Transformation Example

​Input: Excel Row

​Output: JSON Invoice

​Data Type Conversions

Build docs developers (and LLMs) love

Overview

Transformation Flow

Step 1: Reading Excel Data

Step 2: Row Iteration and Transformation

The transformar_fila() Function

Date Formatting

Currency Calculations

Field Mappings

Navigation Data Generation

Complete Field Mappings

Document Identification Fields

Buyer (Customer) Fields

Totals Section (BSD)

Totals Section (USD)

Line Items

Additional Information

Batch Organization

Batch Structure

Example Output Structure

Transformation Example

Input: Excel Row

Output: JSON Invoice

Data Type Conversions