Creating a CSV File with PersisTables

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.