New – One Click install

/0 Comments/in /by

We have changed the download process for PersisTables. You can now download directly from the website here. The installation package will be downloaded by your chosen browser and opening it will cause the auto-extract and install process to start. There is a chance that your anti-virus software will object, you should enable the download process and a CMD window will open. If Excel is already running it will ask you to close down Excel so the install process can proceed.

The package will prompt you for permission to install. You will require Admin privileges currently (a Non Admin variant is available on request). Once you have agreed to proceed with the installation, the standard windows msi installer runs asking you some questions and then proceeds to set up the software. Uninstalling is the same process except the installer will give you the option to Repair or Uninstall.

Once installed you will be able to open Excel and access the PersisTable toolkit directly from a worksheet, further details on installation can be found here.

Note that the software is developed by Kulakor Limited and distributed by Persis Solutions Limited. You will see that the package has been verified as being published by Kulakor Limited

SQL Server Storage

/0 Comments/in /by
Read more

Using Array Formulae

/1 Comment/in /by

Excel functions are great when they have to return a single value, SUM(), MAX(), LEFT() all return the results of their processing into the cell the formula is entered in. What happens though when the formula needs to return multiple results?

The Array Formula is designed to handle just such a situation. It allows the results to be spread over a range of cells, so each cell contains one of the results.

The PersisTables Toolkit makes use of Array formulae to unpack the information held in a Table. The Table can hold many values and can be accessed through the Table Handle returned to the cell the formula generating the Table is entered in.

In this simple example the Range of cells B4:C9 is captured in the “powers” Table

The data can be extracted using the TKTable_Get() function.

However on its own we just see the top left value. To extract the full set of data we create an Array Formula by selecting the range we want to extract the data to and then pressing the [F2] key on the top left cell. This displays the TKTable_Get() formula

Now pressing the [CTRL][Shift] and [Enter] keys together creates the Array Formula

The braces in the formula bar indicate the cell highlighted (E6) is part of an Array formula.

 

Creating a CSV File with PersisTables

/2 Comments/in /by

The standard method of creating a comma separated values (CSV) file within Excel requires the user to copy the relevant data to a separate sheet and then use the ‘Save As’ menu and selecting ‘Save as Type’ CSV. Excel will then mutter that some features in the workbook might be lost before creating a comma separated values file for the data held on the current sheet. The user then needs to remember to switch back to ‘Save As Type’ Excel Workbook otherwise the rest of the workbook data will not be saved.

Saving Data

The PersisTables toolkit makes the whole process much simpler. The range of cells holding the relevant data can be captured in a Table using the TKTable_Create() function

The Table can then be saved directly from the spreadsheet using the TKTable_Save() function. As the data changes in the original Range it is automatically saved (assuming Calculation Mode is set to Automatic) or saved when the sheet is recalculated.

The CSV file has been created

Loading Data

It is equally easy to load an existing CSV file, the TKTable_Load() function will turn the CSV file into a Table

The data can then be viewed using the TKTable_Get() function as an array formula. (note an array formula allows a single function to return multiple values into adjacent cells)

Changing the Delimiter

Sometimes a file uses other characters as a the delimiter instead of ‘,’ often the ‘pipe’ symbol ‘|’ is chosen. Saving a file direct from Excel which must use a different character is more challenging. The PersisTables toolkit makes things much simpler.

In this case we pass in an optional parameter to the TKTable_Save() function which describes the delimiter to use and below we can see the modified file saved. Note the cell under delimiter contains the character that is to be used as the delimiter.

Loading the file will require the delimiter to be specified in the same way.

The PersisTables toolkit provides functions that allow Tables to be merged, sorted, filtered and subsets to be selected out of them. This makes saving data from a spreadsheet and processing it when it is loaded much simpler.