XLplus Add-On: SQL Commands
This tool helps to create SQL Commands based on data of your spreadsheet.
Pic. 1: SQL Command Type
The tool supports in creating an Excel formula which results in a valid SQL Command. Alternatively, you can specify to create SQL Commands as strings. Both results can be inserted into a spreadsheet. Strings can be published in a Text-File, too.
The tool was designed helping to create quite complex Excel formulas. If you step through the provided Wizard this task can be performed easily and conveniently.
The Wizard consists of the following steps:
SQL Command Type
The following SQL Commands can be created:
SQL Table Name
Enter the desired name of your table here. Remember, names containing a minus sign have to be enclosed within square brackets.
Pic. 2: SQL Table Name
Data Area
Specify the range of cells which will be your data to work with. The Data Area is required for creating an INSERT or UPDATE command and will build the fields and values of the command.
Ex.:
INSERT INTO myTable (Field1, Field2, ..., FieldN) VALUES (Value1, Value2, ..., ValueN)
UPDATE myTable SET Field1=Value1, ..., FieldN=ValueN
Pic. 3: Data Area
In case the tool can identify a current region, the data range will be suggested already. In order to achieve this activate a cell of your data range before you launch this tool.
You can manually select the Data Area by the RefEdit Control provided (
). Alternatively, especially when having a huge Data Area, select a single cell of your Data Range and use the Expand tool to select the entire Data Area for you (
).
The tool assumes your first row of data contains the fieldnames your SQL Command requires. In case you have to specify fieldnames manually set option Manually Set SQL Fieldnames. You can Add, Edit or Remove field names. The tool will allow creating the correct number of fields, only.
Data Types
Specify the required Data Types here. You can specify
- String
- String (Unicode)
- Number
as type of data.
Pic. 4: Data Types
Where Conditions
When creating an UPDATE or DELETE command you might want to specify a Where Condition to use. You can create a Where Condition as static text or by using data from the Excel spreadsheet.
Pic. 5: Where Conditions
You can Add a new condition by selecting parts of it from the drop-down list. You start with either
- [Fieldname] as Excel Cell Reference or
- [Fieldname] as String.
Using Excel Cell References will create an Excel formula for your Where Condition which can be adjusted to the entire range of data you have available. When using Strings, your Where Condition will be static, means for all commands to create the same Where Condition can and will be created only.
Next specify the operation for the Where Condition. This can be
and many more.
Use ′ in order to preceed values of type string. Please do not forget to add a ´ after the value you specified - contrary to using N′ a single hyphen does not get closed automatically.
Once the appropriate operator was chosen you have to finish the Where Condition by specifying a value. Values can be specified as Excel Cell Reference or static String.
You can add additional conditions by combining them by AND or OR operators.
In case you identify an error in the condition you created, simply select the line from the list of the left hand side and edit the statement. Use the Edit Button to save your recent changes to the list. You can use Insert in order to insert a new statement before the statement currently selected. This is usefull in case you missed adding a bracket. Finally you can use the Remove option to delete an entire line of your Where Condition.
Pic. 6: Where Conditions
By default, the Wizard will adjust all Excel Cell References used in your Where Condition to the actual Data Area specified. When creating the final SQL Commands the Where Conditions will use a different row reference always. In case you are creating a DELETE command, no Data Area can be specified since this command does not use fields and values. In this case the Wizard will use the Data Area provided by the Where Conditions. This Data Area is determined by the first [Fieldname] as Excel Cell Reference specified. As long as filled rows can be identified a DELETE command will be created.
Using option Use specified settings for all rows of data range will overwrite the default behaviour of the Wizard. In this case the Where Conditions to create will not use different rows for each single SQL Command.
Once you have finished your Where Condition proceed to SQL Command in order to check it.
Note:
Your Where Condition will be validated and finalised once you proceeded to SQL Command or Output. The final Where Condition will contain valid coding for Excel Formulas and SQL Command Strings. The preview of the list used for creating the Where Condition is sometimes showing incorrect endings - this can be ignored.
SQL Command
SQL Command shows a preview of the Excel Formula and the SQL Command created.
Pic. 7: Excel Formula and SQL Command
Use the preview in order to check your Where Condition created and the Data Types your SQL Command uses.
Output
Use the options provided to specify where and in what format your SQL Commands shall be published. Data can be published within Excel workbooks or in a text-file.
Pic. 8: Output
By default, data will published within the active sheet. You have to specify a cell from where on data will be written.
Alternatively, specify publishing in a new Worksheet or Workbook. When publishing in a new Workbook, all Excel Formulas will be adjusted by receiving a reference to the active workbook. This allows creating SQL Commands referencing to a specific workbook which can be used later again. In case you are planning to regularly create SQL Commands of same type but different data this is the easiest way of doing so.
Alternatively, you might want to publish your data in a new text-file. The text-file requires a folder and filename to be specified. In case the file already exists you will be prompted whether to overwrite it or not.
Note: In case the file cannot be created due to Operating System Restrictions, no error message will be shown - the process simply stops. Change the target location, then.
Once you stepped through the Wizard, your SQL Commands will be published.
Pic. 9: Result
Versions:
Ver.: 2.0.107 and Ver.: 3.0.107: Correct a bug where saving as textfile was not possible due to missing data and the dialog was not shown anymore. The dialog will be shown, now.