Guide: Using Excel with Databox

To connect an Excel workbook in Databox, go to Data Manager > + New Data Source. Type Excel in the search bar and click on the green Connect button.

Next, select your Excel online storage. You can choose between Excel (via Google Drive), Excel (via One Drive), Excel (via Dropbox), Excel (via File upload).

This will open the Authorization window, which is where you will be prompted to enter your Google, Microsoft, or Dropbox login credentials (depending on the selected storage) and authorize the Databox app.

Note: There can be a delay when making a connection, as new files aren't shown instantly on OneDrive. It can take up to an hour for new files to show up on the API.

Learn how to import data from a CSV, HTML, or text file to an Excel workbook here.

Learn how to import data from different data sources, such as Power BI, Dynamics 365, SAP HANA, Oracle, IBM, JSON, and others to an Excel workbook using Power Query here.

In order to effectively sync your Excel data with Databox, there are guidelines to follow when formatting your Excel file.

You can format your data in columns, which would allow you to use the Metric Builder for Excel Wizard when creating Custom Metrics. This is the easiest way to get set up with Databox, and is the recommended setup.

If you prefer, you can also format your data in rows. While you wouldn't be able to use the Wizard, you can still manually set up Custom Metrics to sync data formatted this way with Databox.

Learn more about how to format your Excel files here.

The maximum file size allowed is 10 MB, without images included in the Excel workbook.

Pro Tip: To check the size of your Excel file, navigate to File > Informations > Properties, and look at the Size row.

Tip: To avoid any file size-related issues in Databox, it is recommended that you: Remove all images from the Excel file that is connected with Databox. Split large Excel files into several smaller files and connect them separately in Databox. Do not include custom formatting in the workbook, which incluedes customized fonts, bolding, adjusting the alignment, colors, etc. Aggregate values in your Excel worksheet to decrease the file size by following these instructions. You should do this in a new workbook in order to keep an original copy of your data. This can help reduce the file size of the Excel workbook that you want to connect to Databox.

What is the maximum number of cells that can be synced with Databox?

Databox can sync a maximum of 2,000,000 cells of data from an Excel workbook.

Tip: All cells, empty or full, are counted in the size of your workbook. To count the number of cells in your Excel workbook, follow these steps.

If you make changes to an Excel file that was manually uploaded (by using Excel File Upload), these changes will not reflect in Databox automatically. In order to get fresh data into Databox, you will need to re-upload the file again. By re-uploading the file, the previously synced data will be purged.

Learn more here.

Excel Custom Metrics will only update if new data is added or updated in the cells included in the Custom Metric. Databox checks for new data in your worksheet every hour and will sync when changes have been made.

Tip: Learn more about frequency here.

Data syncs in Databox are triggered based on the updated timestamp in your Excel worksheet file. In your Excel online file, you can find this information by navigating to Saved to OneDrive (or any other cloud storage) > Save status > Last saved.

If your Excel workbook is updated with new data automatically (i.e., if you use Scripts or other solutions to automatically add data to Excel worksheet), this might not update the Last saved information and therefore Databox may not recognize new data. As a result, data will not be synced.

If your Excel Data Source is updated automatically, you can try to initiate data syncing in Databox using one of the following options:

• Update the Excel worksheet manually
  You can manually add '1' in a blank cell in any sheet and update it when necessary to initiate a data sync. Make sure the given cell is not selected for any of your Excel Custom Metrics. Making changes manually to your sheets will update the Last saved information in the Excel worksheet, and the relevant data will be automatically updated in Databox with the next scheduled sync.
• Update the Excel workbook using Zapier
  You can use Zapier to push data into an Excel workbook (e.g. pushing current date/time) every hour. This will update the Last saved information every hour, so Databox will search for new data in the sheets with every scheduled sync.

When updating Excel worksheets, new rows or columns should not be added to the top or in the middle of the document. This would result in duplicate data syncing with Databox and potential data inconsistencies. It is recommended to add new rows or columns to the end of the document.

If you update a single cell's Value in your Excel worksheet, you should select All****Value as a Data Type for the Custom Metric in Databox. Learn more here.

The Excel integration is completely custom, so there are no Basic Metrics available. All Custom Metrics must be built using the Metric Builder for Excel. Learn more about creating Custom Excel Metrics here.

Tip: You can start building Databoard and Custom Excel Metrics with the help of a wizard. To do so, navigate to Databoards page > + New Databoard button > Use Wizard option, and the wizard will guide you to select your Excel Data Source connection and further through the Custom Metric creation.

• When you connect an Excel workbook in Databox, the connection is dependent on the location of the Excel workbook and the access level of the user who connected the Excel workbook. If any of these are changed, the Data Source will disconnect in Databox.
• In the Data Preview, you will only see the first 1,000 rows from your Excel worksheet. Additional rows are still included in the Custom Metric, they just are not visible in the Data Preview.
• Our Integration supports basic OneDrive available through Microsoft Account. To connect files, the individual file must be shared with the user. Files that were shared with a user on the Folder level will not be listed in Databox during the connection flow and cannot be connected in Databox by the shared user.
• As our integration supports OneDrive Personal ("Basic") and not OneDrive for Business (associated with Microsoft SharePoint), connecting Excel files that are stored in SharePoint is currently not supported in Databox. Connecting Excel files stored in SharePoint is currently not supported. You can submit a request using our Roadmap Request form.
• Only .xls and .xlsx file formats are supported in the Excel integration. If your file is in any other format, such as CSV, HTML, or text file, you can save it in .xlsx Excel format and connect it to Databox. Learn more here.
• Pivot Tables are not currently supported and cannot be synced with Databox.