John Liu Blog

Every drop counts

Create embeddings in SQL2025

SQL2025 has the ability to use local ONNX model on the server to create embeddings. Following are steps for configuration. 1. If this feature is still developer preview feature, run following SQL scrit to enable preview features ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON; 2. Enable AI runtime on SQL2025 EXEC sp_execute 'external AI runtime enabled',1; RECONFIGURE WITH OVERRIDE; 3. Set up the ONNX runtime library Create a local folder, say C:\onnx_runtime, to store the runtime libarary files.

Posted by John Liu Sunday, October 26, 2025

SQL Server Scientific Notation

When we import numeric data into SQL server, sometime the source data might be in scientific notation format. SQL Server can’t automatically convert scientific notation value string to decimal or integer value and will result with implecite conversion error. To properly import the data, we need to convert the source string value to FLOAT or REAL data type first before convert to the target data type. SELECT CONVERT(DECIMAL(18,3),CONVERT(FLOAT,'1.2E-5'))

Posted by John Liu Sunday, September 7, 2025

Convert Excel to CSV

This Python converts Excel worksheets to CSV comma delimited format and combine all worksheets into a single file with worksheet name as the section name. # pip install pandas openpyxl import pandas as pd # Path to your Excel file excel_file = "your excel file.xlsx" # Output text file output_file = "combined_output.csv" # Read the Excel file xls = pd.ExcelFile(excel_file) with open(output_file, "w", encoding="utf-8") as f: for sheet_name in xls.sheet_names: f.

Posted by John Liu Friday, August 15, 2025

Extract Excel Columns

You might want to grab all columns from all worksheets in an Excel. This simple Python script will help to do that. # pip install pandas openpyxl import pandas as pd # Path to your Excel file excel_file = "your excel file.xlsx" # Read the Excel file xls = pd.ExcelFile(excel_file) # Initialize an empty list to store tuples sheet_column_pairs = [] # Loop through each sheet and get column names for sheet_name in xls.

Posted by John Liu Friday, August 15, 2025

SQL Query External Dta

Read text file contents as a single value: SELECT [BulkColumn] FROM OPENROWSET(BULK 'C:\TEMP\test.json', SINGLE_CLOB) AS Contents Read data from Excel: Option1: Using OPENROWSET. Note: if your column has data more than 255 characters, try to format the column as Text. Also try to change following registry key TypeGuessRows to 0 (default is 8), under path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel (for 64-bit machine) or HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel (for 32-bit machine). Restart of SQL instance required if change registry setting.

Posted by John Liu Wednesday, June 4, 2025