{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "84c2c897",
   "metadata": {},
   "source": [
    "# Create an Excel import job"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bf70cec",
   "metadata": {},
   "source": [
    "You use an Excel import job to import data from a properly formatted Excel spreadsheet.\n",
    "\n",
    "This example shows how to create an Excel import job request, submit it to the job\n",
    "queue, and interact with the resulting Excel import job object returned by the server.\n",
    "\n",
    "Information on how to create a properly formatted Excel import template is\n",
    "outside the scope of this example. For information on using Excel to import data into\n",
    "Granta MI, see the Granta MI documentation or consult your ACE representative."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a63014cc",
   "metadata": {},
   "source": [
    "## Connect to Granta MI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0681bdd0",
   "metadata": {},
   "source": [
    "Import the ``Connection`` class and create the connection. For more information,\n",
    "see the [Connect and access the job queue](0_Getting_started.ipynb) example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9918ccbc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from ansys.grantami.jobqueue import Connection\n",
    "\n",
    "server_url = \"http://my_grantami_server/mi_servicelayer\"\n",
    "client = Connection(server_url).with_credentials(\"user_name\", \"password\").connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00560e0a",
   "metadata": {},
   "source": [
    "## Create an ``ExcelImportJobRequest`` object"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47dc2778",
   "metadata": {},
   "source": [
    "The first step in importing an Excel file with the job queue is to create an\n",
    "``ExcelImportJobRequest`` object. When creating this object, specify the name of the job and the\n",
    "files to import. You can also specify an optional description and the scheduled execution\n",
    "date, if the import should be deferred until that date and time.\n",
    "\n",
    "Different job types require different input files. For example, an Excel import can use a\n",
    "*template*, one or more *data* files, or *combined* files, which include both the template\n",
    "and data files. You should specicy any additional files to imported as file or picture attributes\n",
    "as *attachment* files. You can provide these additional files as relative or absolute paths or as\n",
    "``pathlib.Path`` objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecd37964",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ansys.grantami.jobqueue import ExcelImportJobRequest\n",
    "\n",
    "separate_excel_import_request = ExcelImportJobRequest(\n",
    "    name=\"Excel Import (separate template and data files)\",\n",
    "    description=\"An example excel import job\",\n",
    "    template_file=\"assets/import_template.xlsx\",\n",
    "    data_files=[\"assets/data_file_1.xlsx\", \"assets/data_file_2.xlsx\"],\n",
    ")\n",
    "separate_excel_import_request"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "434f1dc8",
   "metadata": {},
   "source": [
    "## Submit the job to the server\n",
    "Next, submit the jobs to the server. There are two methods for submitting job\n",
    "requests:\n",
    "\n",
    "* ``create_job()``: Submit the job request to the server and immediately return an\n",
    "  ``AsyncJob`` object in the *pending* state.\n",
    "* ``create_job_and_wait()``: Submit the job request to the server and block until the job\n",
    "   either completes or fails. Return an ``AsyncJob`` object in the *succeeded* or *failed* state.\n",
    "\n",
    "This example uses the ``create_job_and_wait()`` method. For an example that shows\n",
    "how to create and submit a job that runs asynchronously, see\n",
    "[Schedule and modify jobs](4_Scheduling_and_modifying_jobs.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69d91378",
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_job = client.create_job_and_wait(separate_excel_import_request)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2fe883e",
   "metadata": {},
   "source": [
    "## Access output files\n",
    "Finally, access the results of the job. Import jobs typically create log files, but the exact type\n",
    "of files generated varies based on the type of import template. In this case, the files are all\n",
    "plain text."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59501322",
   "metadata": {},
   "source": [
    "Access the list of files generated by the job with the ``output_file_names`` property. This\n",
    "returns a list of file names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b24754be",
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_job.output_file_names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de5332c6",
   "metadata": {},
   "source": [
    "In general, an Excel import job returns two files:\n",
    "\n",
    "- ``<job name>.log``: Log file of the import operation on the server\n",
    "- ``summary.json``: Data file that summarizes the number of records impacted by the import\n",
    "   job and provides details of any errors that occurred during processing."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60c16c14",
   "metadata": {},
   "source": [
    "This cell shows how to access the content of the log file as ``bytes`` using the\n",
    "``AsyncJob.get_file_content()`` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5fa303c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "log_file_name = next(name for name in completed_job.output_file_names if \"log\" in name)\n",
    "log_file_content = completed_job.get_file_content(log_file_name)\n",
    "log_file_string = log_file_content.decode(\"utf-8\")\n",
    "print(f\"{log_file_name} (first 200 characters):\")\n",
    "print(f\"{log_file_string[:500]}...\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "578aad22",
   "metadata": {},
   "source": [
    "This next cell shows how to download the import summary file to disk using the\n",
    "``AsyncJob.download_file()`` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8cc03f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_file_name = next(name for name in completed_job.output_file_names if name == \"summary.json\")\n",
    "output_path = f\"./{summary_file_name}\"\n",
    "completed_job.download_file(summary_file_name, output_path)\n",
    "f\"{summary_file_name} saved to disk\""
   ]
  }
 ],
 "metadata": {
  "granta": {
   "clean_database": true
  },
  "jupytext": {
   "notebook_metadata_filter": "granta"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}