Excel2SQL

The Excel2SQL tool can be used to automatically upload one or more files to SQL Server.

Instructions

  • Copy excel2sql.exe into a local folder.
  • Open excel2sql.exe.
  • When prompted, enter the database name.
    Database:
    >>> database123

    If using an alternative server (not XXVIR712000,00094) you can enter the server by using the database@client syntax.
    Database:
    >>> database123@XXVIS71200,00094
  • When prompted, enter the filepath, including the file name and extension.
    Path:
    >>> C:\Client\Data\loanbook.xlsx

    If uploading multiple files, multiple filepaths can be included using the AND syntax.
    Path:
    >>> C:\Client\Data\loanbook.xlsx AND C:\Client\Data\forborne_data.csv
    If uploading ALL xlsx/xlsm/xls/csv/txt files in a directory, just enter the path for that directory instead.
    Path:
    >>> C:\Client\Data
    To specify a sheet (Excel only), type either the sheet name or sheet index (counting from 0) in the format sheet@path.
    Path:
    >>> 1@C:\Client\Data\loanbook.xlsx AND clients@C:\Client\Data\records.xlsx

Jupyter

Alternatively and offering more flexibility is using the pre-built Jupyter notebook file, this can be found in the same directory as the executable.
Although the Jupyter version offers more flexibility, it requires the Python and modules listed in the Technical Detail section.

data module

Both the Excel2SQL.exe and the Jupyter notebook predominantly use the data.sql module class. This can be accessed as a normal Python module by following the instructions in the data.sql documentation.


Python and Modules

To use the Excel2SQL Jupyter or the modules used by Excel2SQL, the following Python and Python module versions should be installed.

Python 3.7.1, we suggest using the Anaconda distribution.

Jupyter Notebook, again we would suggest downloading this via the Anaconda distribution, pip version requirements are:

  • jupyter 1.0.0
  • jupyter_client 5.2.4
  • jupyter_console 6.0.0
  • jupyter_core 4.4.0
  • jupyterlab 0.35.3
  • jupyterlab_server 0.2.0

pandas is included in the Anaconda distribution, otherwise it can be downloaded using the pip package manager. Ensure Python is included within your system paths and run:

pip install pandas==0.25.0

This can also be run from Anaconda prompt if you need to change the version installed.

pyodbc is included in the Anaconda distribution, otherwise it can be downloaded using the pip package manager.

pip install pyodbc==4.0.25

See the pandas tab for more detail and on pip install syntax when using Deloitte networks.


Compiling

The excel2sql.py file is the compiler target. Firstly, before compiling, the following lines in the excel2sql.py file should be commented out.

import sys
sys.path.insert(0, r'C:\\filepath')

The executable program is compiled using Pyinstaller. Documentation on the following Pyinstaller arguments can be found here. When compiling the following modules must be included using --hidden-import

--hidden-import=pandas

pandas provides high-performance data structures, wrangling and analysis. We use it in Excel2SQL for reading in Excel, csv and txt files within the data module.

--hidden-import=os.path

os.path module is particularly useful for any path related operations. We use in in Excel2SQL for pulling specific parts of the filepath.

--hidden-import=datetime

datetime provides many useful date and time functions. We use it for parsing datetime objects into the correct date string formatted like dd/mm/yyyy. It is also used for timing the import and insert operations and required for the insert ETA calculation.

--hidden-import=pyodbc

pyodbc is used to communicate with MS SQL Server using Python. We use it to establish a connection with SQL Server and insert the data to a new table.


Alongside this, there are several modules that should be excluded using --exclude-module, examples include;

  • scipy
  • matplotlib
  • PyQt5

To include the custom modules the libraries directory must be specified using --paths. The final compile cmd window script should look something like: 

pyinstaller -F --onefile --hidden-import pandas --hidden-import os.path --hidden-import datetime --hidden-import pyodbc --exclude-module=scipy --exclude-module=matplotlib --exclude-module=PyQt5 --paths="C:/pysqlplus/lib" excel2sql.py

Once this has been complete, an excel2sql.spec file is generated. This can be used for future compiling. To use this, open the file in a text editor to modify parameters, then type pyinstaller excel2sql.spec into the cmd window terminal.