Excel spreadsheets often encompass substantial data sets for lookup purposes. Formulas such as SUMIFS, COUNTIFS, and XLOOKUP are typically employed to extract specific data points from these datasets. However, housing this data within Excel can lead to inflated file sizes, resulting in slower load and computation times. A practical solution is to offload these data sets to an external database, which can help reduce the Excel file's size and complexity.
This guide is intended to demonstrate the method of separating a dataset from Excel and transitioning it to a database. We will also illustrate the replacement of typical lookup formulas with corresponding database queries.
Removing Data from an Excel File
We'll utilize a simple Excel template designed to tally shipping fees based on a parcel's weight and target ZIP code. The 'Zip' sheet, consisting of ZIP codes and associated factors across roughly 30,000 rows, substantially contributes to the file size of 460KB. Click this link download the original file. Our objective is to trim down the Excel file by transferring this 'Zip' sheet to a database within a SpreadsheetWeb application. By segregating the 'Zip' data into a distinct Excel file, we prepare it for subsequent importation into the application's database, which shrinks the original Excel workbook down to a mere 14KB.
Transferring Data to a Database
To facilitate data import into a database, we set up a simple SpreadsheetWeb application that requires two pieces of data: 'Zip' and 'Factor'.
- Begin by navigating to 'My Applications' and selecting the 'Create New Application' option.
- Upload the Excel workbook with Zip code data and proceed to create the application.
- When prompted, select 'Try Automation?' feature. Make sure the system recognizes the application as one for data collection.
- Click 'Edit User Interface' button, then hit 'Publish'. Confirm data import to populate the database with the information.
- Access the application designer and locate the 'Database' section.
- Copy the database table name by clicking on the black 'Copy Table Name' button, as it will later be linked to the Excel workbook to enable database connectivity.
- Publish the application to finalize.
Click View Data button and confirm the importation process has replicated the Excel file's rows into the database.
Integrating a Database Query in Place of Excel Formulas
With the dataset now decoupled from Excel and housed within a database table, we update the Excel model to incorporate the DBConnect database function instead of traditional lookup formulas.
-
- Copy the previously noted table name within the Excel sheet and assign it a name, such as 'TableID'.
- Write a query formula in a new cell using the format:
=CONCAT("SELECT [Factor] FROM [", TableID, "] WHERE [IsActive] = 1 AND [Zip] = ", Zip, "")
Label this cell with a named range, for instance, 'QueryString'.
- In a separate cell, input the 'DbConnect' function linked to the 'QueryString':
=DbConnect(QueryString,"Inputs!I3")
Assign a named range to this cell, 'DBQuery'. This formula will execute the database query and populate the result into cell Inputs!I3.
- Finally, the formula for calculating the Rate output requires modification. Replace the existing formula, which is:=VLOOKUP(Weight,WeightRates,2,FALSE)*VLOOKUP(Zip,ZipLookup,2,FALSE)with the updated version:=VLOOKUP(Weight,WeightRates,2,FALSE)*FactorThis change incorporates the 'Factor' value directly into the calculation, streamlining the process by using the data retrieved from the database. Click this link to download the updated Excel file.
Once the Excel file is updated, create a new application that takes 'Weight' and 'Zip' as inputs and 'Rate' as the output. Ensure the database module is activated to enable the 'DBConnect' functionality.
Once published, run the application. Inputting a ZIP code will trigger the calculation of the 'Rate' output, drawing the 'Factor' value directly from the database instead of the Excel dataset.
This optimized application will now pull the necessary data from the database, bypassing the need for the Excel file to maintain a large dataset, thus enhancing performance and efficiency.
