Formatting a number when using command "Database: Read from database using SQL statement..." when using Excel as database

  • 26 June 2024
  • 3 replies

Does anyone know how to format a number with commas/decimal when using command "Database: Read from database using SQL statement..." when using Excel as the database?



I’m connecting to an Excel spreadsheet as a database, looping through each row and saving the data into variables that will be used to populate a contract.



I’m having a hard time formatting the Annual Salary field from my Excel file into my $vAnnualSalary$ variable. 


This is my SELECT statement:


SELECT [Employee First Name], [Employee Last Name], [[Annual Salary] from [Sheet1$$] 


Though the Annual Salary field is formatted correctly on the Excel spreadsheet (numerical formatting with commas and decimals, no currency symbol), the value that is saved to my $vAnnualSalary$ variable (after the SQL command is executed) doesn’t contain any formatting (which I guess is expected with SQL).


Does anyone know how I can apply numerical formatting to the Annual Salary so that it contains commas and a decimal?


Ex: Excel value on spreadsheet: 50,000.00

      Value returned from SELECT statement: 50000

       Value as I need it displayed in my $vAnnualSalary$ variable: 50,000.00


Thank you


Best answer by HemanthaPindra 26 June 2024, 10:27

View original

3 replies

Userlevel 6
Badge +15

Hi @Val 6777 ,

Based on the above, would you like to see final output in this format 50,000.00 is correct ?

Userlevel 1
Badge +3

Hi @Val 6777,


You can use VB script to do this.


VB Script Code:

Function FormatAsCurrency(AnnualSal)
  Dim value1
  Dim value1Result
  value1 = AnnualSal
  value1Result = FormatCurrency(value1)
  FormatAsCurrency = value1Result

End Function


Call this function in AA by passing $vAnnualSalary$ variable as a parameter to function. Then you will see the output as $50,000.00 by doing string operations you can remove $ sign and use the value.

Code snippet is below.

Hope it will help.



Hemantha Pindra

@HemanthaPindra , this is exactly what I needed to solve my problem. Thank you so much!