Introduction
The chapter on table functions covers following four sections
- Loading, saving and console I/O
- Basic table access (creating, writing, reading, deleting)
- Searching and expoorig tables
- Processing tables with powerful functions
- Multi-Table Integration (comparing, validating, lookups, merging, etc.)
- Formatting and Styling Tables (color, column widths, fonts, borders, setting autofilters, etc.)
Procedures and Functions Provided:
Loading and saving tables:
table load
table load unchanged
table save
table save multiple
table save with decimal comma
table save with local decimal separator
excel list sheets
table load excel file
table save excel file
Table console I/O:
table list
table menu
table menu quick
Create and initialize tables:
table create
table create if not existing
table initialize
Table configuration settings:
table configure
forget memorized table columns
Writing tables:
table append
table append on same field
table append on same row
table append blank rows
table add row
table write row
table add missing row
table write or add missing row
table write column selected rows
Reading tables:
table read row
table read column selected rows
Clearing and deleting tables:
table clear
table delete
table delete silently
Access table information:
list tables
table existing
table length
table row width
table max width
table min width
row
col
Searching tables:
table search
table search vertically
table search row
table find row
table all selected rows
table selected rows
table selected column numbers
table selected columns
table selected headers
table search header row
Explore, filter and extract tables:
table explore
table filter
table filter ignore case
table extract
with table
for-loop through table rows:
for all table rows
for all table selected rows
for-loop through table columns:
for all table columns
for all table selected columns
for all current table columns
for all current table selected columns
Checking and processing headers:
table check header
table column number
table check headers
table check headers silently
table rename column headers
table rename existing headers
table rename headers
table correct headers
table correct headers ignore case
table lift contents
table lift header row
table lift headers
Inserting table columns:
table insert columns
table insert missing columns
Copying table columns:
table copy columns
table copy columns selected rows
table duplicate columns
Sorting and rearranging table columns:
table sort columns
table rearrange columns
table rearrange existing columns
Keeping and deleting table columns:
table keep columns
table keep existing columns
table delete columns
table delete remaining columns
table delete all blank columns
table delete all empty columns
table delete all unnamed columns
table delete blank columns
table delete empty columns
table delete unnamed columns
Insert table rows:
table insert rows
table insert rows if needed
table insert above selected rows
table insert selected rows
Deleting table rows:
table delete rows
table delete rows if needed
table delete remaining rows
table delete remaining rows if needed
table delete selected rows
table keep selected rows
table delete blank rows
Check table rows:
table check row
Move and rearrange table rows:
table move rows
table move selected rows
table rearrange selected rows
Sort and rearrange table rows:
table sort rows
table sort selected rows
table rank rows
table rank selected rows
Process table rows:
table process
table process all rows
table process all selected rows
table process rows
table process selected rows
table process selected rows fast
Process table columns:
table process columns
table process selected columns
Process table cells:
table process all cells
table process cells
table process cells selected rows
table process cells in columns
table process cells in columns selected rows
Manipulate table contents:
table manipulate
table manipulate selected rows
Table filling and substitution functions:
table fill vertically
table fill vertically selected rows
table fill horizontally
table fill horizontally selected rows
table substitute vertically
table substitute vertically selected rows
table substitute horizontally
table substitute horizontally selected rows
Delete table contents:
table delete cells selected rows
table delete cells selected columns
Cleaning up and miscellaneous table functions:
table format numbers
table clean
table fit
Transpose tables:
table transpose
Change tabular contents to serialized contents:
table serialize
table serialize all
table serialize ignore zero
Spread serialized data across specified columns:
table spread
table spread accumulating
table spread given headers
table spread given headers accumulating
Consolidate table rows:
table consolidate
table consolidate selected rows
Distribute quantities over timeline, e.g. for production planning:
table distribute
table distribute accumulate
table distribute advance
table distribute advance accumulate
Renaming tables:
table rename
Copy and split tables:
table copy table
table copy table selected rows
table split table selected rows
table copy table columns
table copy table columns selected rows
table split table columns
Compare two tables:
table compare
table compare ignore blanks
table compare ignore both
table compare ignore case
[This is a user-defined function you need to create]
Validation functions:
table validate
table check duplicates
table check duplicates ignore blanks
table check duplicates ignore blanks selected rows
table check duplicates ignore case
table check duplicates ignore case selected rows
table check duplicates ingore both
table check duplicates ingore both selected rows
table check duplicates selected rows
Looking up data from other tables:
table lookup
table lookup ignore case
table lookup top down
table lookup top down ignore case
table lookup once
table lookup once ignore case
table lookup fast
table lookup fast ignore case
table lookup smart
table lookup smart ignore case
table lookup smart once
table lookup smart once ignore case
table lookup with rules
table lookup with rules ignore case
table lookup with rules once
table lookup with rules once ignore case
Looking up data from other tables and integrating their results:
table integrate
table integrate ignore case
table integrate top down
table integrate top down ignore case
table integrate once
table integrate once ignore case
table integrate fast
table integrate fast ignore case
table integrate smart
table integrate smart ignore case
table integrate smart once
table integrate smart once ignore case
table integrate with rules
table integrate with rules ignore case
table integrate with rules once
table integrate with rules once ignore case
Looking up data from other tables, integrate results, add rows in case of multiple matches:
table expand
table expand ignore case
table expand fast
table expand fast ignore case
table expand smart
table expand smart ignore case
table expand fast smart
table expand fast smart ignore case
table expand with rules
table expand with rules ignore case
table expand with rules once
table expand with rules once ignore case
table expand fast with rules
table expand fast with rules ignore case
table expand fast with rules ignore case once
table expand fast with rules once
Looking up data from other tables, integrate results from one or more matching row into target table row:
table digest
table digest ignore case
table digest smart
table digest smart ignore case
table digest with rules
table digest with rules ignore case
table digest with rules ignore case once
table digest with rules once
table describe
table describe ignore case
table describe selected rows
table describe selected rows ignore case
Merge two tables with union-set principle:
table merge
table merge exclusive columns
table merge extend columns
table merge intersect columns
table merge subtract columns
One table overlays the other table, without adding rows:
table overlay
table overlay columns
table overlay exclusive columns
table overlay extend columns
table overlay intersect columns
Row-wise intersection made from two tables:
table intersect
table intersect columns
table intersect exclusive columns
table intersect extend columns
table intersect intersect columns
One table does row-subtraction on other table:
table subtract
table subtract columns
table subtract exclusive columns
table subtract extend columns
table subtract subtract columns
Derive disjoint union (exclusive or) from both tables:
table exclude
table exclude columns
table exclude exclude columns
table exclude exclusive columns
table exclude extend columns
Relational multiplication of two tables:
table multiply
table multiply selected rows
Relational division of two tables:
table divide
table divide selected rows
Arrange multiple tables side-by-side:
table arrange
table arrange with spacing
Color functions:
add color
rgb
darken color
lighten color
weaken color
lighten colors
darken colors
weaken colors
Apply style on tables:
table style table
table style rows
table style columns
table style cells
table style auto width
table style theme
Translate applied style to target file format:
translate style attributes for excel
translate style attributes for excel xml 2003
translate style attributes for html
Remove all style attributes from the table:
table style reset