Command List
| Command |
Description |
sqlToolkit.sumSelectedNumbers |
Sum numbers with period as thousand separator |
sqlToolkit.sumSelectedNumbersAlt |
Sum numbers with comma as thousand separator |
sqlToolkit.transformToWhere |
Convert text to SQL WHERE clause |
sqlToolkit.removeDuplicates |
Remove duplicate lines |
sqlToolkit.sortLines |
Sort lines alphabetically or numerically |
sqlToolkit.transformToIn |
Convert text to SQL IN clause |
sqlToolkit.formatTable |
Format text as aligned table |
sqlToolkit.pivotData |
Transform data into pivoted format |
sqlToolkit.transformToInsert |
Convert text to SQL INSERT statements |
sqlToolkit.mergeDatasets |
Combine two datasets (Full Outer Join) |
sqlToolkit.saveToExcel |
Save selected datasets to Excel file |
sqlToolkit.saveToExcelSpanish |
Save selected datasets to Excel file with Spanish number format |
Description
SQL Toolkit is a Visual Studio Code extension that provides several utilities to facilitate SQL development. It includes commands for performing quick calculations, transforming text into SQL clause formats, and text manipulation.
Features
The extension offers the following features:
Sum Selected Numbers (sqlToolkit.sumSelectedNumbers):
- Calculates the sum of numbers found in the current text selection.
- Accepts numbers formatted with periods as thousand separators and commas as decimal separators (e.g.,
1.234.567,89).
Sum Selected Numbers (Alternative Format) (sqlToolkit.sumSelectedNumbersAlt):
- Calculates the sum of numbers found in the current text selection.
- Accepts numbers formatted with commas as thousand separators and periods as decimal separators (e.g.,
1,234,567.89).
Transform to WHERE Clause (sqlToolkit.transformToWhere):
Transforms a block of text, interpreted as a table, into a SQL WHERE clause.
The first line of the selected text is considered the header (column names).
Subsequent lines are considered data, and a WHERE clause with OR conditions is generated.
String values are encapsulated with single quotes, and numeric values are not.
Example:
Input:
col1 col2 col3
123 abc 3.14
456 def 2.71
Output:
(col1=123 AND col2='abc' AND col3=3.14)
OR
(col1=456 AND col2='def' AND col3=2.71)
Remove Duplicate Lines (sqlToolkit.removeDuplicates):
- Removes duplicate lines from the selected text.
- Duplicate lines are detected after applying a trim().
Sort Lines (sqlToolkit.sortLines):
- Sorts the lines of the selected text.
- If the lines after the header are numeric, it sorts them numerically; otherwise, it sorts them alphabetically.
Transform to IN Clause (sqlToolkit.transformToIn):
Input:
col1
abc
def
ghi
Output:
col1 IN ('abc', 'def', 'ghi')
Input:
abc
def
ghi
Output:
('abc', 'def', 'ghi')
Format Table (sqlToolkit.formatTable):
Formats a tab-separated table with aligned columns
Right-aligns all data values
Left-aligns headers
Adds separator lines between headers and data
Example:
Input:
COLUMN_NAME VALUE
Data1 1234.56
LongData 1.23
Output:
COLUMN_NAME| VALUE
-----------+--------
Data1 | 1234.56
LongData | 1.23
Pivot Data (sqlToolkit.pivotData):
Transforms a tab-separated table into a pivoted format
First column becomes row headers
Creates Value #N columns for each data row
Maintains data alignment and spacing
Example:
Input:
ID NAME VALUE
1 Test1 100
2 Test2 200
Output:
Name |Value [#1](https://github.com/buffingtomcat/MDSTOOLS/issues/1) |Value [#2](https://github.com/buffingtomcat/MDSTOOLS/issues/2)
-------------------+---------------------+--------------------
ID |1 |2
NAME |Test1 |Test2
VALUE |100 |200
How to Use
Open a file in VS Code.
Select the text to which you want to apply the feature.
Open the command palette (Ctrl+Shift+P or Cmd+Shift+P).
Type the name of the command (e.g., sqlToolkit.sumSelectedNumbers, sqlToolkit.transformToWhere, etc.) and press Enter.
The result will be shown in the editor (in the case of sumSelectedNumbers and sumSelectedNumbersAlt in an information message, and in the case of the other commands, in the edited text).
Transform to INSERT (sqlToolkit.transformToInsert):
Transforms a tab-separated table into INSERT statements
First line is considered as column headers
Automatically detects and formats numbers and strings
Generates one INSERT statement per data row
Example:
Input:
col1 col2
val1 2018
val2 2019
Output:
INSERT INTO [table] (col1, col2) VALUES ('val1', 2018);
INSERT INTO [table] (col1, col2) VALUES ('val2', 2019);
Merge Datasets (sqlToolkit.mergeDatasets):
Combines two datasets using common fields (Full Outer Join)
Uses tab as field separator
Automatically suffixes non-common fields with _left and _right
Applies table formatting to the result
Example:
- Dataset 1:
ID Name Value
1 Apple 100
2 Orange 200
3 Banana 150
- Dataset 2:
ID Name Stock
1 Apple 50
3 Banana 75
4 Grape 25
- Result:
ID Name Value_left Stock_right
1 Apple 100 50
2 Orange 200 NULL
3 Banana 150 75
4 Grape NULL 25
- Save to Excel (
sqlToolkit.saveToExcel, sqlToolkit.saveToExcelSpanish):
Saves selected text as Excel file (.xlsx)
Each dataset becomes a separate worksheet
Datasets are separated by empty lines
Preserves tab-separated column structure
Spanish format command converts numbers like "1.234,56" to Spanish Excel format
Example:
Input:
col1 col2
1.234,56 2.345,67
col1_ds2 col2_ds2
3.456,78 4.567,89
Output:
- Excel file with two worksheets
- Numbers displayed in Spanish format (1.234,56)
Dependencies
This extension depends on:
- Node.js (for npm and the compilation script)
- TypeScript (for extension development)
- VS Code (to run and use the extension)
Configuration
There are no additional configuration options for this extension.
Additional Notes
- This extension is designed to be a simple and easy-to-use tool for common SQL and text handling tasks.
- If you encounter errors or have suggestions, please report them on the extension's repository.
Author
https://github.com/buffingtomcat/
Version
0.0.9
| |
