Control_Analysis.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-QhPWE1lwZHH"
      },
      "source": [
        "# Gemini Analysis for controls in the agricultural sector"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fa7c47ae6451"
      },
      "source": [
        "<table class=\"tfo-notebook-buttons\" align=\"left\">\n",
        "  <td>\n",
        "    <a target=\"_blank\" href=\"https://ai.google.dev/tutorials/quickstart_colab\"><img src=\"https://ai.google.dev/static/site-assets/images/docs/notebook-site-button.png\" height=\"32\" width=\"32\" />View on Google AI</a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a target=\"_blank\" href=\"https://colab.research.google.com/github/google/generative-ai-docs/blob/main/site/en/tutorials/quickstart_colab.ipynb\"><img src=\"https://www.tensorflow.org/images/colab_logo_32px.png\" />Run in Google Colab</a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a target=\"_blank\" href=\"https://github.com/google/generative-ai-docs/blob/main/site/en/tutorials/quickstart_colab.ipynb\"><img src=\"https://www.tensorflow.org/images/GitHub-Mark-32px.png\" />View source on GitHub</a>\n",
        "  </td>\n",
        "</table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NNNg43Ymw54e"
      },
      "source": [
        "## Prerequisites\n",
        "\n",
        "You can run this tutorial in Google Colab, which doesn't require additional environment configuration.\n",
        "\n",
        "Alternatively, to complete this quickstart locally, see the Python guidance in [Get started with the Gemini API](https://ai.google.dev/tutorials/quickstart)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kHkHARdb1ZID"
      },
      "source": [
        "## Install the SDK\n",
        "\n",
        "The Python SDK for the Gemini API is contained in the [`google-generativeai`](https://pypi.org/project/google-generativeai/) package. Install the dependency using pip:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "J6Pd9SFJ1yVi"
      },
      "outputs": [],
      "source": [
        "!pip install -q -U google-generativeai"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EeMCtmx9ykyx"
      },
      "source": [
        "## Set up your API key\n",
        "\n",
        "To use the Gemini API, you'll need an API key. If you don't already have one, create a key in Google AI Studio.\n",
        "\n",
        "<a class=\"button\" href=\"https://aistudio.google.com/app/apikey\" target=\"_blank\" rel=\"noopener noreferrer\">Get an API key</a>\n",
        "\n",
        "In Colab, add the key to the secrets manager under the \"🔑\" in the left panel. Give it the name `GOOGLE_API_KEY`. Then pass the key to the SDK:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HTiaTu6O1LRC"
      },
      "outputs": [],
      "source": [
        "# Import the Python SDK\n",
        "import google.generativeai as genai\n",
        "# Used to securely store your API key\n",
        "from google.colab import userdata\n",
        "\n",
        "GOOGLE_API_KEY=userdata.get('GOOGLE_API_KEY')\n",
        "genai.configure(api_key=GOOGLE_API_KEY)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CZPYk29o2No0"
      },
      "source": [
        "## Initialize the Generative Model\n",
        "\n",
        "Before you can make any API calls, you need to initialize the Generative Model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "s-JqXcDe2hZ_"
      },
      "outputs": [],
      "source": [
        "model = genai.GenerativeModel('gemini-pro')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nXxypzJH4MUl"
      },
      "source": [
        "## Small sample on how to use Gemini"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "j51mcrLD4Y2W",
        "outputId": "39597b8e-543e-49f4-f2b9-b2b927c78d23",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Berne\n"
          ]
        }
      ],
      "source": [
        "response = model.generate_content(\"Quel est le nom de la capitale de la suisse ?\")\n",
        "print(response.text)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zUUAQS9u4biH"
      },
      "source": [
        "## What's next\n",
        "\n",
        "To learn more about working with the Gemini API, see the [Python tutorial](https://ai.google.dev/tutorials/python_quickstart).\n",
        "\n",
        "If you're new to generative AI models, you might want to look at the\n",
        "[concepts guide](https://ai.google.dev/docs/concepts) and the\n",
        "[Gemini API overview](https://ai.google.dev/docs/gemini_api_overview)."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Export all the control points for a collection of pdfs"
      ],
      "metadata": {
        "id": "wuBFQ1DdWjPs"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install google-cloud google-auth google-auth-oauthlib google-auth-httplib2 PyPDF2 pandas openpyxl"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "collapsed": true,
        "id": "h0QVKXgbWo4s",
        "outputId": "59376bed-99de-40ef-a065-139ccfbfbc41"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting google-cloud\n",
            "  Downloading google_cloud-0.34.0-py2.py3-none-any.whl.metadata (2.7 kB)\n",
            "Requirement already satisfied: google-auth in /usr/local/lib/python3.11/dist-packages (2.27.0)\n",
            "Requirement already satisfied: google-auth-oauthlib in /usr/local/lib/python3.11/dist-packages (1.2.1)\n",
            "Requirement already satisfied: google-auth-httplib2 in /usr/local/lib/python3.11/dist-packages (0.2.0)\n",
            "Collecting PyPDF2\n",
            "  Downloading pypdf2-3.0.1-py3-none-any.whl.metadata (6.8 kB)\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.11/dist-packages (2.2.2)\n",
            "Requirement already satisfied: openpyxl in /usr/local/lib/python3.11/dist-packages (3.1.5)\n",
            "Requirement already satisfied: cachetools<6.0,>=2.0.0 in /usr/local/lib/python3.11/dist-packages (from google-auth) (5.5.1)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.11/dist-packages (from google-auth) (0.4.1)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.11/dist-packages (from google-auth) (4.9)\n",
            "Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.11/dist-packages (from google-auth-oauthlib) (2.0.0)\n",
            "Requirement already satisfied: httplib2>=0.19.0 in /usr/local/lib/python3.11/dist-packages (from google-auth-httplib2) (0.22.0)\n",
            "Requirement already satisfied: numpy>=1.23.2 in /usr/local/lib/python3.11/dist-packages (from pandas) (1.26.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.11/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.11/dist-packages (from pandas) (2025.1)\n",
            "Requirement already satisfied: tzdata>=2022.7 in /usr/local/lib/python3.11/dist-packages (from pandas) (2025.1)\n",
            "Requirement already satisfied: et-xmlfile in /usr/local/lib/python3.11/dist-packages (from openpyxl) (2.0.0)\n",
            "Requirement already satisfied: pyparsing!=3.0.0,!=3.0.1,!=3.0.2,!=3.0.3,<4,>=2.4.2 in /usr/local/lib/python3.11/dist-packages (from httplib2>=0.19.0->google-auth-httplib2) (3.2.1)\n",
            "Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in /usr/local/lib/python3.11/dist-packages (from pyasn1-modules>=0.2.1->google-auth) (0.6.1)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.11/dist-packages (from python-dateutil>=2.8.2->pandas) (1.17.0)\n",
            "Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.11/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib) (3.2.2)\n",
            "Requirement already satisfied: requests>=2.0.0 in /usr/local/lib/python3.11/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib) (2.32.3)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.11/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib) (3.4.1)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.11/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib) (3.10)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.11/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib) (2.3.0)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.11/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib) (2025.1.31)\n",
            "Downloading google_cloud-0.34.0-py2.py3-none-any.whl (1.8 kB)\n",
            "Downloading pypdf2-3.0.1-py3-none-any.whl (232 kB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m232.6/232.6 kB\u001b[0m \u001b[31m4.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: google-cloud, PyPDF2\n",
            "Successfully installed PyPDF2-3.0.1 google-cloud-0.34.0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OoN_s2wIWtSV",
        "outputId": "c19f1c6b-6392-4c32-bff6-96f58af9ce87"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import os\n",
        "import PyPDF2\n",
        "import pandas as pd\n",
        "import time\n",
        "from openpyxl import load_workbook\n",
        "from google.colab import files"
      ],
      "metadata": {
        "id": "bh49mCUYXoxL"
      },
      "execution_count": 57,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Import the Python SDK\n",
        "import google.generativeai as genai\n",
        "# Used to securely store your API key\n",
        "from google.colab import userdata\n",
        "\n",
        "GOOGLE_API_KEY=userdata.get('GOOGLE_API_KEY')\n",
        "genai.configure(api_key=GOOGLE_API_KEY)"
      ],
      "metadata": {
        "id": "XSpc2ZdZXb34"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def extract_text_from_pdf(pdf_path):\n",
        "    with open(pdf_path, 'rb') as pdf_file:\n",
        "        pdf_reader = PyPDF2.PdfReader(pdf_file)\n",
        "        total_pages = len(pdf_reader.pages)\n",
        "        text = ''\n",
        "        for page_num in range(total_pages):\n",
        "            page = pdf_reader.pages[page_num]\n",
        "            text += page.extract_text()\n",
        "        return text"
      ],
      "metadata": {
        "id": "t269YVzZXsea"
      },
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def summarize_text(text, max_tokens=30000):\n",
        "    truncated_text = text[:max_tokens]\n",
        "    model = genai.GenerativeModel('gemini-pro')\n",
        "    response = model.generate_content(\"The following content contains control points for the agricultural sector. Can you list as bullet points, without categories and groups, these control points and write them in french ? Every bullet point has to be on line without carriage return\" + truncated_text)\n",
        "    summary = response.text\n",
        "    return summary"
      ],
      "metadata": {
        "id": "R46iVsjEXz2a"
      },
      "execution_count": 63,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def write_to_excel(data, filename):\n",
        "    df = pd.DataFrame(data)\n",
        "    # Vérifiez si le fichier Excel existe\n",
        "    if not os.path.exists(filename):\n",
        "       # Créez un nouveau fichier Excel avec une feuille de base\n",
        "       with pd.ExcelWriter(filename, engine='openpyxl') as writer:\n",
        "          df.to_excel(writer, sheet_name='ControlPoints', index=False)\n",
        "    else:\n",
        "       # Chargez le fichier Excel existant\n",
        "        book = load_workbook(filename)\n",
        "        if 'ControlPoints' in book.sheetnames:\n",
        "            sheet = book['ControlPoints']\n",
        "        else:\n",
        "            # Si la feuille n'existe pas, créez une nouvelle feuille\n",
        "            sheet = book.create_sheet('ControlPoints')\n",
        "\n",
        "        # Trouvez la première ligne vide dans la feuille\n",
        "        max_row = sheet.max_row\n",
        "        for i, row in df.iterrows():\n",
        "            for j, value in enumerate(row):\n",
        "                sheet.cell(row=max_row + 1 + i, column=j + 1, value=value)\n",
        "\n",
        "        # Enregistrez les modifications dans le fichier Excel\n",
        "        book.save(filename)"
      ],
      "metadata": {
        "id": "u6nmyAUrpktI"
      },
      "execution_count": 55,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Répertoire contenant les fichiers PDF\n",
        "# pdf_directory = '/content/drive/MyDrive/OFAG/Controls/FR_ControlPDF'\n",
        "pdf_directory = '/content/drive/MyDrive/OFAG/Controls/DE_ControlPDF'\n"
      ],
      "metadata": {
        "id": "2bPW663eX3ZT"
      },
      "execution_count": 65,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Liste pour stocker les résultats\n",
        "file_counter = 0\n",
        "fichier_excel = 'DE_ControlPDF_Summary.xlsx'\n",
        "#fichier_excel = 'FR_ControlPDF_Summary.xlsx'\n",
        "\n",
        "# Parcourir les fichiers PDF et extraire le texte et les résumés\n",
        "for filename in os.listdir(pdf_directory):\n",
        "    if filename.endswith('.pdf'):\n",
        "        file_counter += 1\n",
        "        print (file_counter)\n",
        "        if file_counter > 0:\n",
        "          pdf_path = os.path.join(pdf_directory, filename)\n",
        "          text = extract_text_from_pdf(pdf_path)\n",
        "          #time.sleep(60)\n",
        "          summary = summarize_text(text)\n",
        "          lignes_contenu = summary.splitlines()\n",
        "          # Itère sur les lignes et affiche chaque ligne\n",
        "          data = []\n",
        "          for ligne in lignes_contenu:\n",
        "            if len(ligne) > 0:\n",
        "              data.append({'File': filename, 'Control points': ligne})\n",
        "          write_to_excel(data, fichier_excel)\n",
        "          #break\n",
        "\n",
        "# Télécharger le fichier Excel sur Google Drive\n",
        "files.download(fichier_excel)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "ThntIJjrYDry",
        "outputId": "f564718b-8ee1-40ad-db9c-0d9e7187946f"
      },
      "execution_count": 66,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1\n",
            "2\n",
            "3\n",
            "4\n",
            "5\n",
            "6\n",
            "7\n",
            "8\n",
            "9\n",
            "10\n",
            "11\n",
            "12\n",
            "13\n",
            "14\n",
            "15\n",
            "16\n",
            "17\n",
            "18\n",
            "19\n",
            "20\n",
            "21\n",
            "22\n",
            "23\n",
            "24\n",
            "25\n",
            "26\n",
            "27\n",
            "28\n",
            "29\n",
            "30\n",
            "31\n",
            "32\n",
            "33\n",
            "34\n",
            "35\n",
            "36\n",
            "37\n",
            "38\n",
            "39\n",
            "40\n",
            "41\n",
            "42\n",
            "43\n",
            "44\n",
            "45\n",
            "46\n",
            "47\n",
            "48\n",
            "49\n",
            "50\n",
            "51\n",
            "52\n",
            "53\n",
            "54\n",
            "55\n",
            "56\n",
            "57\n",
            "58\n",
            "59\n",
            "60\n",
            "61\n",
            "62\n",
            "63\n",
            "64\n",
            "65\n",
            "66\n",
            "67\n",
            "68\n",
            "69\n",
            "70\n",
            "71\n",
            "72\n",
            "73\n",
            "74\n",
            "75\n",
            "76\n",
            "77\n",
            "78\n",
            "79\n",
            "80\n",
            "81\n",
            "82\n",
            "83\n",
            "84\n",
            "85\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_87428675-64e3-49e5-be8d-eb05fdfa96e2\", \"DE_ControlPDF_Summary.xlsx\", 72867)"
            ]
          },
          "metadata": {}
        }
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}