Access Excel Files within Sub-Folders Using CData Excel Drivers

Microsoft Excel is a powerful spreadsheet tool that allows users to manage, analyze, and visualize data. The CData Driver for Excel provides an intuitive way to connect to and retrieve data from Excel files, treating them as database tables, enabling seamless integration with business intelligence, reporting, and ETL tools.

In this guide, we will explore how to retrieve and access data from Excel files located within sub-folders of a resource or data source root folder. While this article uses the CData JDBC Driver for Excel and DBVisualizer, the principals apply to any of the CData Drivers and Connectors for Excel and any data tool.

Let's get started!

Install and Configure the CData Excel JDBC Driver

For this guide, we will use the CData Microsoft Excel JDBC Driver and set up a data source within DbVisualizer to retrieve files from a local directory.

Follow the steps below:

1. Download and install the Excel JDBC Driver from the CData website.
2. Download and install the free edition of DbVisualizer from here.
3. In DbVisualizer, navigate to Tools > Driver Manager.
4. Click the plus sign in the top-left to create a new driver.
5. Select "Custom" as the template.
6. On the Driver Settings tab:
   1. Set Name to a user-friendly name (e.g., CData Excel Driver).
   2. Set URL Format to jdbc:excel:.
   
   3. Under Driver Artifacts and JAR Files:
      1. Click the plus sign on the mid-right and select Add files....
      2. Navigate to the lib folder in your CData installation directory (e.g., C:\Program Files\CData\[Product_Name] XXXX\lib\).
      3. Select the JAR file cdata.jdbc.excel.jar and click "Open".

7. The Driver Class should populate automatically. If not, select cdata.jdbc.excel.ExcelDriver.

Configure the CData Driver for Excel to Access Sub-Folders

Now that you have installed the driver and created the connection, follow the steps below to configure the connection properties in the connected driver.

1. Close the "Driver Manager" and follow the steps below to save the connection properties in the JDBC URL.
2. Under the "Databases" tab, click the plus sign "" on the top-left and select the driver you just created.
3. In the "Connection" section, set the following options:
   1. Database Type: If you selected the wizard option, the database type is automatically detected. If you selected the "No Wizard" option, select either "Generic" or "Auto Detect" from the Database Type menu.
   2. Driver Type: Select the driver you just created. In our example, it is CData Excel Driver.
   3. Database URL: Enter the full JDBC URL. The syntax of the JDBC URL is:

      jdbc:cdata:excel:URI='C:\Users\YourUsername\Documents\Excel-Files\';IncludeSubdirectories=true;DirectoryRetrievalDepth=2;PathSeparator='_';

      Explanation of URL Components:

      • jdbc:cdata:excel: The base URL indicating the type of connection to be established using the CData Excel driver.
      • URI: Specifies the location of the root folder for your file, e.g., 'C:\Users\Public\Documents\Excel-Files\'.
      • IncludeSubdirectories: When enabled, this option drills down into subfolders within the root folder. The schema names are prepended with the relative folder path from the root folder.
      • DirectoryRetrievalDepth: Controls how deep the driver drills into subdirectories. For example, if set to 2, it will go two levels deep. Setting it to -1 scans all subdirectories.
      • PathSeparator: An optional parameter to resolve name conflicts. The default character is "_". When IncludeSubdirectories is enabled, this property sets the character used for path separation in schema names. You may customize this character if necessary to suit your file structure.
NOTE: Since Excel does not require a User or Password for authentication, you can use any values for the Database Userid and Database Password fields, or leave them empty.
4. Click Connect to establish the connection. You should now be able to see the available Excel files in the database navigator.

Explore the Excel Data

Now that you've successfully connected, it's time to explore your data. You can view the Excel sheets as tables by toggling and selecting a table under the CData default database listed under your driver name.

You can also visualize the tables by clicking References next to Tables. This will visualize the schema in ER diagrams, helping you better understand the relationships within your data.

To start querying the data, go to SQL Commander > New SQL Commander and type your queries. For example, SELECT * FROM sys_tables retrieves all the available tables from the driver. For more information on querying, check out the documentation for the Excel JDBC Driver.

Table Names Based on File Path

The table below illustrates how the CData Excel Drivers determine table names for Excel files and sheets located in sub-folders based on their file paths. The CData driver treats each Excel workbook as a schema and each individual sheet within the workbook as a table.

Since we've enabled IncludeSubdirectories and DirectoryRetrievalDepth, the PathSeparator will prepend the schema names of the files with the relative folder path from the root folder, using the default separator '_'.

Try the CData Excel Drivers for 30 Days Free

Unlock the power of seamless integration with your Excel data. Try the CData Excel Drivers for 30 days free and experience the flexibility and performance they bring to your applications. Start your free trial today!