Edit SQL tables in Excel

with TABLE.Manager you can do exactly that





#1 Self-Service SQL for quick orientation in existing SQL server data



  • Self service BI … SQL query without writing SQL statements
  • All databases … of one SQL instance in one view
  • All tables .. of a database including number of colums and data records
  • Credentials … Save your DB specific credentials in the Add-In (if your governance rules allow that)

SQL Azure und lokale SQL Instanzen lesen
SQL Tabellen mit den üblichen Excel Funktionen formatieren und berechnen

#2 Evaluate with the efficient interface of Excel



  • Sort … simple sorting by one or more columns
  • Filter … simple filtering with the autofilter function and with slicer
  • Calculate … add calculated columns, identify duplicates, and more.
  • Format … date, numbers, left / right, etc.
  • Conditional formatting … Highlighting important information
  • Columns … simply change the order, grouping, show/hide data

# 3 Write back the changed data content into the SQL table



  • Edit … easy to modify, add and delete records
  • Calculations … Formulas for editing or filling entire columns
  • Search & Replace … to quickly overhaul entire columns or tables
  • Remove Duplicates … to quickly remove duplicate entries
  • Sandbox … experiment with the data before writing back

Daten aus Tabellen löschen, editieren und hinzufügen

For whom is TABLE.Manager a useful tool?

IT Department

to make the SQL content available to power users with a “No SQL Tool” and / or with a “Sandbox Tool”.

Power User

from all departments that want to use available SQL content for Excel-based analyzes and / or revision of the data.

Enterprises

who want to use the TABLE.Manager as a starting point for a company-specific VBA solution in order not start from scratch.

11 Reasons to use TABLE.Manager!

# 1 Self service SQL for quick orientation into existing SQL Server structures

# 2 Evaluate with the efficient interface of Excel spreadsheets

# 3 Write back the contents into the SQL table

# 4 Supports both local SQL Server and the SQL Azure Cloud

# 5 Supports both SQL tables and (writable) SQL Views

# 6 Make backups of SQL tables easy in Excel

# 7 Individual solutions can be implemented (eg PowerQuey to SQL)

# 8 Company-specific custom development (source code option available)

# 9 Personally configurable via the add-in settings

# 10 Company-wide license … any number of installations in the company

# 11 No maintenance constraints … Updates can be ordered as required

Test and buy

  • Trial Version
  • Free
  • Full functionality of the company license
  • 30 days running time
  • Write back directly into SQL table
  • Local SQL Server as well as SQL Azure Cloud
  • SQL tables as well as (writable) SQL Views
  • Personally configurable via the Add-In Settings
  • Company-wide license (any number of installations)
  • No maintenance constraints (updates as required)
  • Company license
  • EUR 499
  • Self-Service SQL for the departments
  • Evaluate with efficient Excel interface
  • Write back directly into SQL table
  • Local SQL Server as well as SQL Azure Cloud
  • SQL tables as well as (writable) SQL Views
  • Personally configurable via the Add-In Settings
  • Company-wide license (any number of installations)
  • No maintenance constraints (updates as required)
  • Sourcecode License
  • EUR 1.990
  • VBA source code for company-specific solutions
  • Customizing for advanced solutions
  • Write back directly into SQL table
  • Local SQL Server as well as SQL Azure Cloud
  • SQL tables as well as (writable) SQL Views
  • Personally configurable via the Add-In Settings
  • Company-wide license (any number of installations)
  • No maintenance constraints (updates as required)

All prices are net prices.

Invoicing is invariably plus 20% Austrian VAT, only when purchased by foreign companies (with a valid UID number) the service is invoiced tax-free using the reverse charge procedure.

Frequently Asked Questions

Product FAQ


Functional Requirements

Linearis TABLE.Manager can be used with Excel 2010/2013/2016 in both 32-bit and 64-bit versions. The user requires permission to install an Excel add-in on the client.

Access is tested on SQL Server 2008 R2 / 2012/2014/2016 and SQL Azure. The user needs at least read access to a table of an SQL database on a SQL instance, and to write back write privileges.

How does the installation work after the download?

For the initial installation, please proceed as follows:

  1. Download the setup zip file, fastest with the button “Download”
  2. Double-click the zip file (for example, in the download folder) and double-click the exe file in it
  3. Confirm the Windows security message “The computer has been protected by Windows” by clicking “Run anyway”.
  4. After selecting the installation language, simply follow the instructions of the installation routine, the application is installed within seconds.

    At a technical level, the Linearis Excel add-in is installed and activated and the TABLE.Manager Excel document is stored.
  5. Now start Excel and confirm the following message about the Personal Settings, which appears only during the initial installation.

    Now also the so-called Personal Settings are activated so that you can make your specific settings in the Ribbon via the “Personal Settings” button.

Updates are installed on the existing add-in without having to remove the existing add-in. After the installation has been completed, only the personal settings have to be updated. The software can be uninstalled normally before installing an update in the “Add / Remove Software” program. Make sure that Excel is closed during the uninstall process.

How do I activate after purchase?

Open the menu using the button “Testversion Remaining: x days” …

… the activiation form:

Now enter your company name and the activation code supplied by us in the two yellow fields, which you have received by mail after acquiring a company or source code license in our webshop (see information in the right FAQ block). Now press the Apply activation code button, the status display now changes to green:

Please be sure that the spelling of the company name does exactly match that of the delivery mail, since the delivered activation code is only valid in combination with your company name. Close the activation form and restart Excel if the ribbon is still in trial mode.

How does reading SQL data work?

So-called Excel Query Tables – a standard function of Excel – are used to read the data from SQL Server into the TABLE.Manager.

The benefit of using the TABLE.Manager is to dynamically establish and update the data connection to the SQL Server using the Excel add-in. Furthermore, the query of the data tables is enriched with numerous metadata such as, for example, the field types and a selective query (= filter on a column of the SQL table).

How does the write back process work?

There is no write-back function in the Excel Query Tables, so the write-back function is implemented completely via the Excel add-in. There are two modes:

  1. Replace mode
    In this mode, the SQL table is first (completely or selectively) deleted (= Delete) and then the records from the TABLE.Manager! Into the SQL table as new records (= insert). The process is performing.
  2. Update mode
    This option is only available if the SQL table has exactly one primary key field. This field is used to search for each record from the TABLE.Manager in the SQL table and update it with the values ​​from Excel (= update). Records in the SQL table, which no longer exist in Excel, are deleted from the SQL table. Records from Excel that are not already present in the SQL table are inserted into the SQL table (= Insert). The process is significantly less performing than the Replace mode and should therefore only be used with smaller tables / partial data sets.

Since the write-back also involves risks, we strongly recommend that you back up the SQL table before you write it back. For further protection, the TABLE.Manager is restarted before executing the write-back, whether the process should actually be started.

The technical access to SQL Server is made from VBA via the so-called ADODB object (Microsoft ActiveX Data Objects).

Limits of the Add-In

General restrictions

SQL tables / views with more than 1.048.576 can not be read completely due to the Excel line limit.

SQL field types that are not supported by Excel Query Tables (for example, uniqueidentifier, varbinary) can not be read or written using the TABLE.Manager.

For performance reasons, we recommend that you use TABLE.Manager to process tables up to about 100,000 lines. When wrting back, the performance differs very much between the Replace mode (= Delete + Insert in SQL, fast) and the update mode (= update in SQL, slow).

Access to SQL Azure in the cloud is, of course, slower than accessing an on-premise SQL Server instance.

Special features of Views

For Views, the number of records in the TABLE.Manager is not determined because SQL Server does not provide system tables for it, and the query might take a long time for all views in a database.

Views can only be written to on the SQL side under certain conditions. TABLE.Manager does not check the writability of a view, this is the responsibility of the user. Furthermore, the attributes of a field (such as the primary key property) which are relevant for the writeback can not always be automatically determined in Views, which must then be manually configured by the user in the TABLE.Manager. Also, only the Replace mode can be used to write back, but not the update mode.

In the so-called selective queries (= filter on a field of a mostly large table), there are restrictions when reading a view with the TABLE.Manager.

Can TABLE.Manager used for custom solutions?

Yes, this is also a great advantage of the solution. Both the READ function and the WRITE function from the Excel add-in can also be used in your own Excel applications. Here is a blog post that shows how to convert an Exchange rate query from PowerQuery to the TABLE.Manager add-in directly after SQL Server (only available in german at this time).

Technical Details

TABLE.Manager consists of two components:

  1. An Excel document (xlsx) that serves as a user interface for reading and writing back SQL tables.
    Technically speaking, several Excel Query Tables are used, which are dynamically updated via the add-in.
  2. An Excel add-in (xlam), which is visible as a ribbon in Excel and is used to execute the read and write process.
    Technically, this is a VBA project that can be used in both the 32-bit and 64-bit versions of Excel. The ADODB object (Microsoft ActiveX Data Objects) is used to restore.
    The Excel add-in allows the credentials (login & passwords) to be stored to access a SQL Server instance. This data is stored in encrypted form in an XML file on the client and displayed in the plain text in case of error messages. It is therefore necessary to clarify to what extent this function may be used with the internal governance rules.

Buyers FAQ


Is a free trial version available?

Yes!

Just click on the “Download” button in the “Try and Buy” section, enter your e-mail address and your name and the download will start. Follow the instructions for the product installation (here on the left) and start! The trial version can be used without restrictions for 30 days.

How can I buy the product?

Quite simply: select either the company or source code license at the top of the “Try and Buy” section and check the “Buy” button in the webshop. There the formal order process takes place, payment options are PayPal or on account. Your company-specific activation code will be delivered to you by e-mail after receipt of payment, as will the VAT invoice.

Buying a company license

The license for the TABLE.Manager is not a license for a single workplace but a company license. This means that the product can be used with as many users as you want! The company license is valid without any limitation in time for the product version purchased. A transfer or sale to a third party is not possible with this license.

The company and licensee is the organisation that makes the purchase, not a subsidiary, sister or parent company, nor is it the headquarter of a multinational organization. If TABLE.Manager is used in the whole organization, we can convert the company license into a process license. In this case, the license applies to all users in the large organization who are participating in a specific process. If so, please contact us before purchase.

Buying the source code license

In the normal company license and for custom versions, the source code is protected and not accessible to VBA developers. The TABLE.Manager with the source code license can be purchased to use the source code for in-house development.

With this license, the VBA source code of the TABLE.Manager is available for company-specific developments. A direct distribution or distribution to third parties of the source code or the further developments is also prohibited in this extended license.

Buying updates

The purchased company license is valid without limitation for the product version valid at the time of purchase. Updates to the current product version can be purchased as required, which then apply to the entire company. There is no obligation in the form of maintenance contracts or the purchase of skipped versions.

Customizing TABLE.Manager

We would like to discuss with you the adaptation and further development of the TABLE.Manager Add-In to your specific project requirements and create an offer for the implementation. Please contact us if you have any questions about the customization of TABLE.Manager.

Do you have questions? Contact Us!

Robert Lochner

Robert Lochner

Entrepreneur and Business Intelligence Consultant

is founder and managing director of Linearis, author of the Linearis blog and developer of the TABLE.Manager Add-In. He has been an entrepreneur for over fifteen years, and since his studies of business management he has accumulated a wealth of experience in the design, implementation and operation of Business Intelligence applications.