I recently ran across an interesting situation where I needed to calculate excel formulas in SQL. I was working on a project to warehouse my family budget data. This project uses Python to scrape excel files and import values into a SQL database.
After importing the data from the excel spreadsheets, I noticed that the import contained not only the budget line numbers, but also some formulas that were entered to calculate the values:
In the example above, I would want the formula ‘=90*2’ to be a value of 180. There are a number of approaches that can be used to do this:
- Handle these values in the import files (ex. modify the excel spreadsheets to eliminate the formulas)
- Handle them in SQL
- Handle them in the Integration code (in this case python)
I chose to handle them in SQL. I can parse the string ‘=90*2’ to include only the formula and easily calculate it. I also found that
Ideally, we would be able to use a user-defined scalar valued function that would take as an input the formula and output the result of the calculation value. In this case we cannot do that because we need to execute a block of dynamic SQL to get the value. Scalar valued functions cannot call other stored procedures.
The other option here is to created a stored procedure that will output the result of my calculation. So – I ended up with a proc that works pretty well:
CREATE PROCEDURE [dbo].[sp_CalculateExcelValue] (@Input VARCHAR(100), @retvalout MONEY OUTPUT ) AS BEGIN DECLARE @retval MONEY DECLARE @ParmDefinition nVARCHAR(100) DECLARE @SQL nVARCHAR(100) SET @ParmDefinition = N'@retvalOUT MONEY OUTPUT'; DECLARE @N INT IF @Input LIKE '=%' AND (ISNUMERIC(SUBSTRING(@Input,2,1))=1 OR SUBSTRING(@Input,2,1)='(') BEGIN
SET @SQL='SELECT @retvalOUT= '+RIGHT(@Input, LEN(@Input)-1) EXEC sp_executesql @SQL, @ParmDefinition, @retvalOUT=@retval OUTPUT; END SET NOCOUNT ON; select @retvalout=@retval RETURNEND
As you can see, the procedure accepts a string as its’ input and only does work if it is a formula beginning with the ‘=’ character. We are also checking that the second character of the input string is a number. There may be cases where the formula references other cells. If this the case, the process will be ignored. It executes the formula and returns the result.
Here is an example of how I use this procedure. I iterate through all of the line that I have imported and return a value if the line contains a formula:
CREATE PROCEDURE [dbo].[sp_PopulatedCalculatedValuestoSTG] AS declare @return money DECLARE @val VARCHAR(100) DECLARE db_cursor CURSOR FOR SELECT LineValue from stg.BudgetFromExcel WHERE LEFT(LineValue,1)='=' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @val WHILE @@FETCH_STATUS=0 BEGIN exec dbo.sp_CalculateExcelValue @val, @retvalout=@return OUTPUT; UPDATE stg.BudgetFromExcel SET CalculatedAmount=@return WHERE LineValue=@val FETCH NEXT FROM db_cursor INTO @val END CLOSE db_cursor DEALLOCATE db_cursor
One quick note: This solution will only handle numeric calculations. It does not handle situations where calculations reference other excel cells (as shown below). For this project, I created a process that moves these values to a ‘bad values’ table and are not used in further processing.
I hope you found this trick to dynamically calculate excel Formulas in SQL useful!