notebooks/Export All Data to Single CSV.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export All Data to Single CSV\n",
    "This notebook will walk you through the process of exporting indicator data as a single CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pyodbc\n",
    "import csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test to see if you have the needed drivers installed to connect to an Access Database\n",
    "Run the line below and if you see an emtpy array: `[]` as a result, you may need to install the 64-bit ACE drivers\n",
    "\n",
    "*for reference https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Microsoft-Access*\n",
    "\n",
    "*for 64-bit drivers : https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set your working directory\n",
    "example: *C:\\users\\me\\working_directory\\my_country*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_working_directory = r'C:\\Users\\adam6475\\devinfo\\tanzania'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enter the path to your DevInfo Access Database\n",
    "example: *C:\\users\\me\\working_directory\\my_country\\my_devinfo_database.mdb (.accdb)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "access_database = r'C:\\Users\\adam6475\\devinfo\\tanzania\\TSED_20180423.mdb'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enter the location and name for your output CSV file\n",
    "example: *C:\\users\\me\\working_directory\\my_country\\devinfo_output.csv*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_csv = r'C:\\Users\\adam6475\\devinfo\\tanzania\\tz_out_new.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute the query against the DevInfo Access tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connStr = (\n",
    "    r\"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};\"\n",
    "    r\"DBQ={};\".format(access_database)\n",
    "    )\n",
    "\n",
    "cnxn = pyodbc.connect(connStr)\n",
    "\n",
    "sql = \"\"\"\\\n",
    "SELECT \n",
    "UT_Data.Indicator_NId AS INDICATOR_ID, \n",
    "UT_Indicator_en.Indicator_Name AS [INDICATOR], \n",
    "UT_Area_en.Area_ID AS REF_AREA_ID, \n",
    "UT_Area_en.Area_Name AS REF_AREA, \n",
    "UT_Data.Data_Value AS OBS_VALUE, \n",
    "UT_Unit_en.Unit_Name AS UNIT, \n",
    "UT_Subgroup_Vals_en.Subgroup_Val AS SUBGROUP, \n",
    "UT_Indicator_Classifications_en.IC_Name AS SOURCE, \n",
    "UT_Area_en.Area_Parent_NId AS REF_AREA_PARENT_ID, \n",
    "UT_TimePeriod.TimePeriod AS TIME_PERIOD\n",
    "\n",
    "FROM \n",
    "UT_TimePeriod INNER JOIN (UT_Indicator_Classifications_en INNER JOIN ((((UT_Area_en INNER JOIN UT_Data ON UT_Area_en.Area_NId = UT_Data.Area_NId) LEFT JOIN UT_Indicator_en ON UT_Data.Indicator_NId = UT_Indicator_en.Indicator_NId) INNER JOIN UT_Subgroup_Vals_en ON UT_Data.Subgroup_Val_NId = UT_Subgroup_Vals_en.Subgroup_Val_NId) INNER JOIN UT_Unit_en ON UT_Data.Unit_NId = UT_Unit_en.Unit_NId) ON UT_Indicator_Classifications_en.IC_NId = UT_Data.Source_NId) ON UT_TimePeriod.TimePeriod_NId = UT_Data.TimePeriod_NId\n",
    "\n",
    "ORDER BY UT_Data.Indicator_NId;\n",
    "\"\"\"\n",
    "\n",
    "crsr = cnxn.execute(sql)\n",
    "\n",
    "rows = crsr.fetchall()\n",
    "\n",
    "print ('sucessfully executed data query :: {} rows returned'.format(len(rows)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write the result to a CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_path = os.path.abspath(output_csv)\n",
    "\n",
    "# set encoding \n",
    "# when reading some CSV files, there may be encoding issues that result in unicode characters appearing in the field names\n",
    "# if you have any unexpected behavior that is related to this, either re-save your file with \"utf-8\" encoding\n",
    "# or try using 'utf-8-sig' as your encoding value below\n",
    "# Stack Overflow reference: https://stackoverflow.com/questions/17912307/u-ufeff-in-python-string/17912811#17912811\n",
    "encoding = 'utf-8'\n",
    "with open(output_path, 'w', encoding=encoding, newline='') as csvfile:\n",
    "    writer = csv.writer(csvfile)\n",
    "    \n",
    "    header_row = [d[0] for d in crsr.description]\n",
    "        \n",
    "    writer.writerow(header_row)\n",
    "    \n",
    "    for row in rows:\n",
    "        writer.writerow(row)\n",
    "\n",
    "print ('csv successfully created at {}'.format(os.path.abspath(output_path)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}