Convert.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install pandas xlrd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "c_FormaPago\n",
      "Found the start of the sheet c_FormaPago c_FormaPago\n",
      "1\n",
      "c_Moneda\n",
      "Found the start of the sheet c_Moneda c_Moneda\n",
      "1\n",
      "c_TipoDeComprobante\n",
      "Found the start of the sheet c_TipoDeComprobante c_TipoDeComprobante\n",
      "1\n",
      "c_Exportacion\n",
      "Found the start of the sheet c_Exportacion c_Exportacion\n",
      "1\n",
      "c_MetodoPago\n",
      "Found the start of the sheet c_MetodoPago c_MetodoPago\n",
      "1\n",
      "c_CodigoPostal_Parte_1\n",
      "Found the start of the sheet c_CodigoPostal_Parte_1 c_CodigoPostal\n",
      "1\n",
      "c_CodigoPostal_Parte_2\n",
      "Found the start of the sheet c_CodigoPostal_Parte_2 c_CodigoPostal\n",
      "1\n",
      "c_Periodicidad\n",
      "Found the start of the sheet c_Periodicidad c_Periodicidad\n",
      "1\n",
      "c_Meses\n",
      "Found the start of the sheet c_Meses c_Meses\n",
      "1\n",
      "c_TipoRelacion\n",
      "Found the start of the sheet c_TipoRelacion c_TipoRelacion\n",
      "1\n",
      "c_RegimenFiscal\n",
      "Found the start of the sheet c_RegimenFiscal c_RegimenFiscal\n",
      "1\n",
      "c_Pais\n",
      "Found the start of the sheet c_Pais c_Pais\n",
      "1\n",
      "c_UsoCFDI\n",
      "Found the start of the sheet c_UsoCFDI c_UsoCFDI\n",
      "1\n",
      "c_ClaveProdServ\n",
      "Found the start of the sheet c_ClaveProdServ c_ClaveProdServ\n",
      "1\n",
      "c_ClaveUnidad\n",
      "Found the start of the sheet c_ClaveUnidad c_ClaveUnidad\n",
      "1\n",
      "c_ObjetoImp\n",
      "Found the start of the sheet c_ObjetoImp c_ObjetoImp\n",
      "1\n",
      "c_Impuesto\n",
      "Found the start of the sheet c_Impuesto c_Impuesto\n",
      "1\n",
      "c_TipoFactor\n",
      "Found the start of the sheet c_TipoFactor c_TipoFactor\n",
      "1\n",
      "C_Colonia_1\n",
      "Found the start of the sheet C_Colonia_1 c_Colonia\n",
      "1\n",
      "C_Colonia_2\n",
      "Found the start of the sheet C_Colonia_2 c_Colonia\n",
      "1\n",
      "C_Colonia_3\n",
      "Found the start of the sheet C_Colonia_3 c_Colonia\n",
      "1\n",
      "c_Estado\n",
      "Found the start of the sheet c_Estado c_Estado\n",
      "1\n",
      "C_Localidad\n",
      "Found the start of the sheet C_Localidad c_Localidad\n",
      "1\n",
      "C_Municipio\n",
      "Found the start of the sheet C_Municipio c_Municipio\n",
      "1\n"
     ]
    }
   ],
   "source": [
    "\n",
    "file_name =  \"catCFDI_V_4_16112023.xls\"\n",
    "\n",
    "\n",
    "\n",
    "from xlrd import open_workbook, Book as bookType\n",
    "from typing import Union\n",
    "import json\n",
    "\n",
    "# book = open_workbook(file_name,on_demand=True)\n",
    "\n",
    "file_name = \"catCFDI_V_4_16112023.xls\"\n",
    "\n",
    "outputfolder = \"output\"\n",
    "\n",
    "# If the output folder does not exist, we should create it\n",
    "import os\n",
    "if not os.path.exists(outputfolder):\n",
    "    os.makedirs(outputfolder)\n",
    "\n",
    "# We should set the encoding to utf-8\n",
    "book: bookType = open_workbook(file_name, on_demand=True, encoding_override=\"utf-8\")\n",
    "sheets_ignore = [\"c_TasaOCuota\", \"c_NumPedimentoAduana\", \"c_Aduana\", \"c_PatenteAduanal\"]\n",
    "\n",
    "for name in book.sheet_names():\n",
    "    if name in sheets_ignore:\n",
    "        continue\n",
    "    print(name)\n",
    "    sheet = book.sheet_by_name(name)\n",
    "\n",
    "    startsaving = False\n",
    "    headers = []\n",
    "    data = []\n",
    "    # We would find the row where the first cell value is equal to name.\n",
    "    # We would then iterate and save the row values until we get to the end of the sheet or we find a row with an empty first cell.\n",
    "    for row in sheet.get_rows():\n",
    "        # If the sheet name starts with the row value\n",
    "        if len(str(row[0].value)) > 0 and (name.lower()).startswith(str(row[0].value).lower()):\n",
    "            print(\"Found the start of the sheet\", name, row[0].value)\n",
    "            headers = [cell.value for cell in row]\n",
    "            headers[0] = \"id\"\n",
    "            data.append(headers)\n",
    "            startsaving = True\n",
    "            # How I can know if the row is a merge cell?\n",
    "            print(row[0].ctype)\n",
    "            continue\n",
    "        if startsaving:\n",
    "            rowData = [cell.value for cell in row]\n",
    "            # rowData = {}\n",
    "            # for index, cell in enumerate(row):\n",
    "            #     rowData[headers[index]] = cell.value\n",
    "            data.append(rowData)\n",
    "    \n",
    "    \n",
    "    #\n",
    "    # We should save the data content to a file called name.json\n",
    "    filename_json = name[0].lower() + name[1:] + \".json\"\n",
    "    content_json = json.dumps(data, indent=4)\n",
    "    with open(outputfolder + \"/\" + filename_json, \"w\") as f:\n",
    "        f.write(content_json)\n",
    "\n",
    "    # We should unload the sheet from memory\n",
    "    book.unload_sheet(name)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}