Connection to ExcelFile

image_pdfimage_print

With the ExcelFile connection, you can connect to Excel workbooks with XLSX format and to Jedox Web spreadsheets with WSS format.

Main settings:

Location

Location of the file with the following options:

  • File System
  • URL
  • Report Designer

File name

File System location:

  • Filename with an absolute path on the Filesystem of the Server. For example: C:\Users\xyz\Sample.xlsx
  • Filename with relative path to the “data” directory of the Jedox Integrator Installation (default: .\tomcat\webapps\etlserver\data\files)
  • File residing on a network drive (see instructions in article “Accessing network files“).

URL location:

  • URL of a HTTP request that has a result in xlsx or wss format.
  • URL of a HTTPS request. In this case the SSL certificate will be automatically imported in the keystore.
  • URL of a FTP request. (Note: FTPS is not supported).

Report Designer location:

  • Filename with path in Report Designer. For example: //Default/Public Files/BikerOrderlines.wss
    Note: In case of access to a remote Report Designer the following syntax is used: <remote_ip>:<port>//<path>
    Example: 12.34.56.78:80//Default/Public Files/BikerOrderlines.wss
Advanced settings:

Password of ExcelFile

The password for the file.

Note to Excel 2016: to open password-protected Excel 2016 files, the “Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files” have to be installed in the Java Runtime Environment (JRE). These files can be downloaded from  http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html. The installation must be in directory <JRE_HOME>\lib\security.

SSL Mode

Off: SSL is not used. For Report Designer files, this option must be used if Jedox Web uses HTTP protocol.

Trust: SSL certificate will be imported to the keystore if not yet available. For Report Designer files, this option must be used at least once if Jedox Web uses HTTPS protocol to import the certificate.

Verify: SSL certificate is verified. For Report Designer files, this option should be used if Jedox Web uses HTTPS protocol and the certificate is already imported. Note: if the certificate has not been imported correctly, the following error message occurs: “Unable to find valid certification path to requested target”.

*If the location is URL, there are 2 additional fields for HTTP Basic Authentication:

Username

The username for HTTP Basic authentication

Password

The password for HTTP Basic authentication

 

Note: formulas are only evaluated for files in the XLSX format, not for files in the WSS format.

Connection to Google sheets:

With the ExcelFile connection, it is possible to connect to Google Sheets, a web-based spreadsheet program that is part of Google Drive. The Google Sheet has to be published to the web in the format “Microsoft Excel (.xlsx)”, as described here.  It is convenient to activate the option “Automatic republish when changes are made”.

Note that the published file doesn’t carry over sharing permissions, so anyone will be able to access it. Make sure that the used Google account allows this public publishing. In the ExcelFile connection, the URL of the published file must be used with location “URL”. No password is required. As publishing is done with HTTPS, the SSL mode has to be set to “trust” or “verify”.

Using Google sheets allows also the extraction from the web analytics service Google Analytics. Therefore the Google Analytics Spreadsheet Add-on has to be installed in Google analytics. Create a Google Sheet for the data and set a suitable intervall in the Add-on to refresh the data e.g. monthly. The Google Sheet has to be published to the web as described above and can then be used in the ExcelFile connection.

image_pdfimage_print
Was this post helpful?
NoYes (0 rating, 4 votes)
Loading...