Instructions for Connecting to the Consumables Database and Importing Data

These instructions will guide you through connecting to the consumables database using an ODBC connection and importing data into spreadsheet software such as Excel, LibreOffice Calc, or OpenOffice Calc. Regardless of the software used, setting up the ODBC connection is necessary.

Step 1: Download and Install SQLite3 ODBC Driver

  1. Visit www.ch-werner.de/sqliteodbc to download the SQLite3 ODBC driver.
  2. Scroll down to the Current Versionsection and download the appropriate file: 
    • For 32-bit Windows: sqliteodbc.exe
    • For 64-bit Windows: sqliteodbc_w64.exe
  3. Run the downloaded installer: 
    • During installation, you will be prompted to select SQLite 2 Drivers. Do not check this option unless you are certain you need to connect to an SQLite 2 database.

Step 2: Set Up an ODBC Connection

Accessing the ODBC Data Source Administrator:

  1. Open the Windows ODBC Data Source Administrator(64-bit or 32-bit): 
    • Access it via Control Panel > Administrative Tools.
    • Alternatively, press Win + R, type odbcad32.exe, and press Enter.

Creating the ODBC Connection:

  1. In the ODBC Data Source Administrator, click Add.
  2. From the list of available drivers, select SQLite3 ODBC Driver and click Finish.
  3. In the SQLite3 ODBC DSN Configurationwindow: 
    • Click Browse next to the Database Name field.
    • Navigate to C:\ProgramData\Xitron\Databases and select the Xiflow.Media.db file.
    • Give the connection a recognizable name (e.g., "ConsumablesDatabase").
  4. Click OK to save the connection.
  5. Verify that your connection appears under the User Data Sources list.

Step 3: Import Data into Your Spreadsheet Software

For Excel:

  1. Open Excel and create a new workbook.
  2. Click the Data tab and select Get Data > From Other Sources > From ODBC.
  3. In the popup window, select the ODBC connection created earlier and click OK.
  4. In the next dialog: 
    • Choose Default or Custom on the left-hand side.
    • Click Connect.
  5. Select the desired table: 
    • INK_USAGE: Ink usage reporting.
    • PRINTBAR_INK_USAGE: Printhead consumption data.
  6. Click Load to import the table data into the worksheet.
  7. Save the workbook. To refresh data in the future, go to the Data tab and click Refresh All.

For LibreOffice Calc:

Registering the Database:

  1. Open LibreOffice and select Base Database from the startup menu.
  2. In the Database Wizard, choose Connect to an existing database and select ODBC from the dropdown.
  3. Click Next, then: 
    • Click Browse next to Name of the ODBC data source and select the connection created earlier.
    • Click OK, then Next.
  4. Leave the User name field blank and ensure Password required is unchecked.
  5. Click Test Connection. If successful, click Next.
  6. Select Yes, register the database for me, and uncheck Open the database for editing.
  7. Click Finish and save the connection settings with a recognizable filename.

Importing Data:

  1. Open LibreOffice Calc and go to View > Data Sources (or press Ctrl+Shift+F4).
  2. Expand the database connection and the Tables list.
  3. Drag INK_USAGE or PRINTBAR_INK_USAGE to the desired location in the spreadsheet.
  4. To refresh data, click Data > Calculate > Recalculate (or press F9).

For OpenOffice Calc:

Registering the Database:

  1. Open OpenOffice and select Database from the startup menu.
  2. Choose Connect to an existing database and select ODBC from the dropdown.
  3. Click Next, then: 
    • Click Browse next to the ODBC data source name field and select the connection created earlier.
    • Click OK, then Next.
  4. Leave the User name field blank and ensure Password required is unchecked.
  5. Click Test Connection. If successful, click Next.
  6. Select Yes, register the database for me, and uncheck Open the database for editing.
  7. Click Finish and save the connection settings with a recognizable filename.

Importing Data:

  1. Open OpenOffice Calc and go to View > Data Sources (or press F4).
  2. Expand the database connection and the Tables list.
  3. Drag INK_USAGE or PRINTBAR_INK_USAGE to the desired location in the spreadsheet.
  4. To refresh data, click Data > Refresh Range.

Notes:

  • Ensure the database file remains in the original location (C:\ProgramData\Xitron\Databases).
  • If any issues arise, verify that the SQLite3 ODBC driver is correctly installed and the database path is accurate.
  • For additional help, consult your IT department or database administrator.


If you want to use Python to access the data, a simple script is attached to this article.