Excel-DB Features

• Excel-DB Features
   
  • Connect from Excel to a database
  • Export: Query data into Excel
  • Import: Insert Excel data into a database
  • Update: Send updated data from Excel back to the database
  • Parameters: Using Excel cells and other parameters in the SQL
     
• Excel-DB Advanced Features
   
  • Advanced Database Connections:
     
    • Using the Data Link Properties wizard
    • Using the ODBC Data Source Administrator
       
  • Batch execute Multiple Statements
  • Use of non SQL commands
  • Explicit Column Formatting
  • Rerun a query
  • Execute Excel-DB from Excel macros (VBA)
  • Calling Procedures and Functions

  Excel-DB Features

  Excel-DB's main feature is establishing a connection to the database and interact with its data. After a database connection is established you can exchange information between Excel and the database - both imports and exports are supported. Unlike Microsoft's external data utility msquery - Excel-DB goes the extra mile and allows data imports as well:

  Connect from Excel to a database

  Excel-DB can connect to different database types. Excel-DB connections to databases have to be configured only once. As soon as a connection is established you can pick it from the Connection History list on the Connection dialog.

  Export: Query data into Excel

  To get data from a database into Excel you execute a query or select statement. If you are familiar with SQL you will feel right at home. If you are not so very proficient in SQL you'll find it easy to learn. SQL is run in Excel-DB from the SQL dialog.

  Import: Insert Excel data into a database

  Your Excel data can be imported easially into your database. To do a data import use the multiple rows insert dialog. There are only a few prerequisites:

  • Format the data as a table. No empty columns or empty rows are allowed. Empty cells (null values) are allowed.
  • You can only load data into 1 table at a time
  • Use the column names as they are in the database as heading above the data table. For example:

    	A	B	C
    1	employee_nr	employee_name	salary
    2	1000	Linker G	5000
    3	1001	Davis R	4000
    4	1002	Jones P	6000

  To insert the data, display the Insert Multiple Rows dialog (Excel-DB|Insert|Multiple Rows...). This dialog essentially takes 3 items as input.

  • Table Name:
    Type the table name you wish to insert the data into.
    For example:

    employees
    

  • Column Names:
    Either type the column names (comma separated) or use the button to use the mouse to select the range containing the column names. Excel-DB will build the comma separated column names list for you. For example:

    employee_nr , employee_name , salary
    

  • The Data Range:
    Use your mouse to click on the spreadsheet to select the range of data cells. Just select it as a single block and you will see that Excel-DB writes the cell addresses in the input field. For example:

    A1:C4

  Now just press the OK button and all the data will be sent to the database. It's as easy as this! Progress information will be shown in Excel's statusbar as % completed. Every now and then Excel-DB will refresh the screen so you can see the progress for yourself. At the end of each row in your spreadsheet Excel-DB will put a status indicator: successful or a short error message.

  Update: Send updated data from Excel back to the database

  Once data was changed in Excel you may want to synchronise your database with the changes applied in Excel. The multiple rows update feature in Excel-DB helps you to do this. The way multiple rows update works is as follows:

  • Use the SQL dialog to retrieve data in Excel that you want to change.
    E.g. select * from employee where employee_nr >= 1000
  • Change the data in Excel. 
    You are allowed to change as many column values  as you like. 
    Do not reorganise the table in any way. Do not sort, delete/add rows/columns.
    To nullify a value simply use the delete key in Excel to clear the cell.
  • To synchronise the database with your changes use the Update Multiple Rows Dialog of Excel-DB.

  To update the data, display the Update Multiple Rows dialog (Excel-DB|Update|Multiple Rows...). This dialog takes 2 settings as input.

  • Mode of Operation:
    • Review Only:
      Using this mode of operation Excel-DB will visit each changed cell and shows you the previous and current values. No updates are being made to the database.
    • Update Only:
      This option is the oposite of Review Only. It finds all changed cells and updates the database accordingly. It will not ask the user to ok each change.
    • Review & Update:
      As a mix of the 2 options above, this option allows you to go through all the changes and tell Excel-DB which changes you would like to apply and which ones you do not want to apply.

  Parameters: Using Excel cells and other parameters in the SQL

  You can use parameters in your SQL. Using parameters you can easially create reusable statements that prompt for values or take values from the worksheet.

  There are two types of parameters or variables:

  • Bind variables
    These type of variables are true bound variables to the SQL statement. The variables have a data type (e.g. number, text, date) and a direction (in, out or in/out).
    Examples:
    select * from emp where empno = :myempno;
    select * from emp where hiredate = :myhiredate;
    
  • Substitution variables
    These variables are string replaced into the SQL text. Therefore the data type of these variables is always text and the direction is always in.
    Example:
    select * from emp where empno = &myempno;
    select * from emp where hiredate = to_date( &myhiredate , 'dd-mon-yyyy');

  Note that you can use the following commands to define and prompt for substitution variables:

  • define: define a default value for a substitution variable. See the Define command
  • accept: prompts for a value for a substitution variable. See the Accept command

   

  Excel-DB Advanced Features
  

  Advanced Database Connections:

  Using the Data Link Properties wizard

  The Data Link Properties wizard can be used to connect from MS Excel to your database. For Excel-DB - or any other program - to connect to your database through ADO you need to specify a connection string. The Data Link Properties wizard helps you to build a connection to your database. Once the connection string is known Excel-DB will save it and reuse it in the future without the need to go through it all again.

  Using the ODBC Data Source Administrator

  The ODBC Data Source Administrator program in Windows can be used to connect from MS Excel to your database. For Excel-DB - or any other program - to connect to your database with the ODBC driver it needs a so called DSN: Data Source Name. A DSN can be seen as a nickname for your database.

  Batch execute Multiple Statements

  The SQL dialog in Excel-DB has the capability to take multiple SQL statements and other commands at the same time. When submitting more than 1 SQL statement each statement should be ended with a semi colon character (;). The source of the SQL does not matter. Multiple select statements can be submitted from a SQL file, a worksheet or from the SQL dialog itself. Let's give a few examples:
  
  To query both the emp and the dept table in one go enter the following into the SQL dialog (note that it also times these queries):
  
  set timing on;
  select * from emp;
  select * from dept;

  Explicit Column Formatting

  The Explicit Column Formatting feature of Excel-DB is the implementation of SQL*Plus like commands with which the resulset data can be explicitly formatted. These commands can be issued in the SQL dialog.

  
  The commands supported in Excel-DB are:

  column <column_name> format <format_spec>;
  column <column_name> format <cell_reference>;
  column <column_name> heading <heading_text>;

  Rerun a query

  After a query was executed it can sometimes be handy to be able to rerun the same query and effectively refresh the data. In Excel-DB there exists a shortcut to achieve this.
  
  Simply select the top left cell of the previously created resultset table and execute the menu command Excel-DB|Query. It will now load the SQL back into the query editor of Excel-DB. You can now rerun this SQL statement.

  Execute Excel-DB from Excel macros (VBA)

  Excel-DB's internal commands can be used from within the macro editor of Excel. By using VBA you can automate your Excel-DB tasks. Just record an Excel macro. The easiest way to get started programming Excel-DB commands with VBA is to record a macro in Excel and look at the generated code.

  Calling Procedures and Functions

  You can call your database (package) procedures and functions using Excel-DB. If you use an Oracle database just wrap the function or procedure call in a begin and end statement. Alternatively you an use the exec command to run a procedure or function:
  
  begin
     ProcTest.ProcMultiply( 2, 3, :A20);
  end;
  
  exec MyProcedure(1,2,'abc');