Data Source Guides
      Back to home
      1. Help Center
      2. Data Source Guides

      Guide: Using Excel with Databox

      HOW TO

      How to connect an Excel workbook

      IN THIS SECTION

      How to connect an Excel workbook

      The ability to connect Excel and access Metric Builder is available on the Professional and higher plans. Request a trial of Excel by following these steps.

      To connect an Excel workbook in Databox, go to  Data Manager > + New connection. 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).

      Connection popup - About

      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.

        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.

      How to import data sets to an Excel workbook

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

      How to import data from different data sources to an Excel workbook

      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.

      How to format Excel file

      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.

      What is the maximum file size for an Excel workbook in Databox?

      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. 

      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.

      Pro 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.

      How to update the data for my Excel files, which were uploaded manually?

      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. 

      How often does Excel data sync with Databox?

      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.

      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.

      How to update Excel workbooks that are syncing with Databox

      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.

      How to create a Databoard using data from an Excel workbook

      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. 

      Pro 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.

      Additional information

      • 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. It is on our roadmap to enable connecting Excel files that are stored in SharePoint to Databox. You can upvote this improvement on our Roadmap here.
      • 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.