Excel File Connection

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:

List of locations (more info in article “Locations for Filebased Connections“):

  • FileSystem
  • HTTP
  • FTP
  • JedoxFiles
  • OneDrive
  • WebDAV
  • WebHDFS

File Name:

Name of the file in Excel format.
The syntax of the filename is location-specific. More info in article “Locations for Filebased Connections“.
Advanced Settings

Password of Excel File:

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.

Notes:

  • Formulas are only evaluated for files in the XLSX format, not for files in the WSS format.
  • The “Strict OOXML” format is currently not supported.
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