Table of Contents

Library for handling CSV files

Module CSVMOD Library for handling CSV files

Description

Library for CSV handling.
“CSV object” in scope of this library is a two-dimensional array of string values (stored in cache).
Though, it can be retrieved from some CSV (comma-separated-values) table, it also might be created from scratch and even read from JSON (see CSVSetFromJson for details).

Main functions:
- CSVInit ( in ArgContent text) - to initialize new CSV object (empty or from CSV-table)
- CSVSetFromJson - to initialize new CSV object from JSON file
- CSVCleanup - to erase CSV object from memory

CSVInit and CSVSetFromJSON return handle which can be used to access the CSV object via functions CSVGet… e.g.:
- CSVGetRow
- CSVGetValue
- CSVGetValueByColumnName
etc.

Size of the CSV object can be retrieved by:
- CSVGetNRows - number of rows (started from 1)
- CSVGetNCols - number of columns (started from 1)
Row number 0 contains columns headers.

If empty content passed to CSVInit - it initializes an empty CSV object which can be then constructed by usage of CSVSet…, CSVAdd… functions:
- CSVAddColumn
- CSVAddRow
- CSVSetValue
- CSVSetValueByColumnName
etc.

CSV object can be exported to the screen field or to the stream. Possible options are:
- CSVSerialize - puts content of CSV object as comma-separated-values which can be saved as *.csv file. Value in each cell is quoted with double-quotes
- CSVSetStreamGrid - puts content of CSV object into given StreamGrid field.
- CSVtoHTMLField - converts content of CSV object into HTML table and puts it into the given stream field with ViewType “HTMLView”. In this case cells that contain carriage returns are escaped with “<br>” tag.


See inline comments of functions for more details of usages

Properties

Property YES/NO
Single page frame No
Show toolbar No
Show menu No
Show statusbar No
Show hint No
Fit panel size No

Rules

Rule Name Additional
function CSVGetColumnIndex numeric global
function CSVGetNCols numeric global
function CSVGetNRows numeric global
function CSVGetRow text global
function CSVGetValue text global
function CSVGetValueByColumnName text global
function CSVInit numeric global
function CSVSetFromJSON numeric global
sub CSVAddColumn global
sub CSVAddRow global
sub CSVCleanup global
sub CSVGetColumn global
sub CSVSerialize global
sub CSVSetColumnMinSize global
sub CSVSetColumnSize global
sub CSVSetFromJSONMultiple global
sub CSVSetNumberOfSkippedRows global
sub CSVSetRow global
sub CSVSetRowsLimit global
sub CSVSetStreamGrid global
sub CSVSetValue global
sub CSVSetValueByColumnName global
sub CSVtoHTMLField global
sub CSVtoHTMLFieldMultiple global

Rules Description and Comments

function CSVGetColumnIndex( in ArgHandle numeric, in ArgColumnName text ) : numeric global

Returns index of the column by its name

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgColumnName - name of the column

function CSVGetNCols( in ArgHandle numeric ) : numeric global

Returns number of columns in CSV-file

ArgHandle - handle of the correspondent CSV object returned by CSVParse

function CSVGetNRows( in ArgHandle numeric ) : numeric global

Returns number of rows in CSV-file

ArgHandle - handle of the correspondent CSV object returned by CSVParse

function CSVGetRow( in ArgHandle numeric, in ArgRow numeric ) : text global

Returns the entire row of CSV-table with TAB-separated values
that is suitable for usage in StreamGrid

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgRow - row number - Content starts from 1, row number 0 contains headers (columns names)

function CSVGetValue( in ArgHandle numeric, in ArgRow numeric, in ArgCol numeric ) : text global

Returns value of the cell of CSV file by row and column index
Row number 0 contains columns names

For better performance on reading large files - use the following pattern:
$row = CSVGetRow( ArgHandle, ArgRow )
Tokenize( $stm, $row, 3)

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgRow - row number (starting from 1)
ArgCol - column number (starting from 1)

function CSVGetValueByColumnName( in ArgHandle numeric, in ArgRow numeric, in ArgColumnName text ) : text global

Returns value of the cell of the CSV table by its position (coordinates)

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgRow - row number (starting from 1)
ArgCol - column number (staring from 1)

function CSVInit( in ArgContent text ) : numeric global

Parse table (CSV) into 2-dimensional array in cache
Returns integer handle of CSV-object that could be used for accessing content of file

If ArgContent is empty - creates an empty CSV-object

After CSVParse is called for stream contained CSV - the following functions
could be used to retrieve data from the array using the returned handle:

Get total count of indecies:
CSVGetNRows(handle) - returns total number of rows in file
CSVGetNCols(handle) - returns total number of columns in file

Get values:
CSVGetRow - returns whole row by row index (row number 0 contains headers)
CSVGetColumn - returns whole column as a stream

CSVGetValue(handle, row, column) - returns value of the cell by position (row, column)
CSVGetValueByColumnName (handle, ROW, COLUMN_name ) - returns value of the cell by row number and column name

CSVGetColumnIndex(handle, ColumnName) - returns index of the column by its name

NOTE: all indecies (row, column) start from 1

Possible errorcodes:
99 - table does not contain any columns
98 - fail when parsing certain line
-99 - table is empty (does not contain any rows)
-100 - limit of rows reached. Default limit 100 000, can be set by calling CSVSetRowsLimit

function CSVSetFromJSON( in ArgJsonHandle numeric, in ArgPath text ) : numeric global

Converts path of JSON into CSV object. Arrays and json objects are skipped (i.e. only flat part is taken)
In case ArgPath is array - then array will be converted to the table accordingly

One JSON file can be represented as a set of tables. I.e.:
- Each flat JSON object is a table of one row (JSON tags are column headers)
- Each array holding objects is a table with row per array item (tags of objects are column headers)
- Each array holding primitive types (strings, numeric) is a table with one column (column name is a tag of array)

ArgHandle - JSON handle
ArgPath - path to use for creating a table. Has to point on Object or array. Empty for the root of JSON
if path is not valid JSON path - the function returns errorcode 300

Returns handle of CSV object

To convert the complete file to a set of CSV-objects - see CSVSetFromJSONMultiple

sub CSVAddColumn( in ArgHandle numeric, in ArgColName text ) global

Appends column to CSV object and adjusts the size accordingly

ArgHandle - handle of CSV object
ArgColName - column name to add

To add new row - see CSVAddRow
To update existing row - see CSVSetRow

sub CSVAddRow( in ArgHandle numeric, in ArgRow stream ) global

Appends row to the CSV object. Adjusts number of columns if it does not fit

ArgHandle - handle of the CSV object
ArgRow - stream with values for the row

sub CSVCleanup( in ArgHandle numeric ) global

Releases given handle ArgHandle and removes content of CSV-object from memory

sub CSVGetColumn( in ArgHandle numeric, out ArgColumn stream, in ArgCol numeric ) global

Returns the whole column by its index as a Stream

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgColumn - output stream that contains all value of the column
ArgCol - column index (starts from 1)

sub CSVSerialize( in ArgHandle numeric, out ArgCsv stream ) global

Writes CSV object from memory into stream ArgCSV
Tokens are comma separated
Each value is quoted using double quotes

sub CSVSetColumnMinSize( in ArgHandle numeric, in ArgSize numeric ) global

Sets minimal size of the columns in StreamGrid

sub CSVSetColumnSize( in ArgHandle numeric, in ArgColumn text, in ArgSize numeric ) global

Sets the relative size of the column in percentage in StreamGrid which is a result of CSVSetStreamGrid call
If not set - the default size will be used

The default size of each column is <max width of StreamGrid> / <number of columns>
By setting width using this function - the weight of the column can be increased/decreased

For example:
- to set column “Model” twice bigger than other columns call:
CSVSetColumnSize( ArgHandle, “Model”, 200 )

- to set column “Year” 50% smaller than other columns call:
CSVSetColumnSize( ArgHandle, “Year”, 50 )

Sizing is set in respect to the minimal size of the columns (which is set by CSVSetColumnMinSize)

sub CSVSetFromJSONMultiple( in ArgJsonHandle numeric, in ArgTitle text, inout ArgHandles stream, inout ArgTitles stream ) global

Converts the complete JSON file to several tables representation
Each object in JSON represented as a table (CSV object)
Tags of JSON are titles of the tables

ArgJsonHandle - handle of JSON from TDJSONInit
ArgTitle - optional title related to the complete file (can be filename)
ArgHandles - stream contained handles of the CSV-objects derived from JSON
ArgTitles - stream contained titles of each CSV-object

The out streams are not cleared for each call, thus several JSON files can be joined by
executing the following chunk:

\\
StreamClear( $handles )\\
StreamClear( $titles )\\
\\
$handle = TDJsonInit( content1 )\\
CSVSetFromJsonMultiple( $handle, "Json 1", $handles, $titles )\\
TDJsoncleanup( $handle )\\
\\
$handle = TDJsonInit( content2 )\\
CSVSetFromJsonMultiple( $handle, "Json 2", $handles, $titles )\\
TDJsoncleanup( $handle )\\



Then, $handles and $titles can be passed to CSVtoHTMLFieldMultiple to represent the files in HTML-field

sub CSVSetNumberOfSkippedRows( in ArgRow numeric ) global

Set number of first rows that have to be skipped for parsing

Default value: 0 (start from first row)

sub CSVSetRow( in ArgHandle numeric, in ArgRow numeric, in ArgContent stream ) global

Sets the entire row of CSV-table from stream ArgContent

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgRow - row number - Content starts from 1, row number 0 contains headers (columns names)
ArgContent - separate values

To set value of a single cell - see CSVSetValue
Special characters are escaped with convertblock (…, 21)

sub CSVSetRowsLimit( in ArgLimit numeric ) global

Set limit of how many rows should be parsed
When limit reached - errorcode -100 is raised by CSVParse and parse stopped

Default limit: 100 000 rows

sub CSVSetStreamGrid( in ArgHandle numeric, inout ArgStreamGrid field ) global

Put CSV object from handle ArgHandle into streamgrid field ArgStreamGrid

ArgHandle - handle of CSV object
ArgStreamGrid - streamgrid field to update with the table

To set field of type HTML - see CSVtoHTMLField

sub CSVSetValue( in ArgHandle numeric, in ArgRow numeric, in ArgCol numeric, in ArgVal text ) global

Sets value of the cell of CSV file by row and column index
Row number 0 contains columns names

ArgHandle - handle of the correspondent CSV object returned by CSVParse
ArgRow - row number (starting from 1)
ArgCol - column number (starting from 1)
ArgVal - content to set

To set value by column name - see function CSVSetValueByColumnName
To update the whole row - see CSVSetRow
To get value from cell - see CSVGetValue

sub CSVSetValueByColumnName( in ArgHandle numeric, in ArgRow numeric, in ArgColumnName text, in ArgValue text ) global

Sets value by name of the column and given row
If row is not available - adds new row
If column does not exist - appends the column

ArgHandle - handle of CSV object
ArgRow - row number of the cell to update
ArgColumnName - name of the column of the cell to update
ArgValue - value to set

To set value by column/row index - see function CSVSetValue

sub CSVtoHTMLField( in ArgHandle numeric, in ArgTitle text, in ArgField field ) global

Converts CSV object into HTML-table which can be viewed in Stream field with Viewtype HTML
Carriage returns escaped with <br>
Cell values are escaped with ConvertBlock( …, 13 ) for HTML compatibility

ArgHandle - handle of CSV object
ArgTitle - optional title of the table
ArgField - field of type Stream and Viewtype HTMLView to show the content

To set multiple CSV-objects to one HTML field - see the function CSVtoHTMLFieldMany

If the passed field is not stream - returns errorcode 30

sub CSVtoHTMLFieldMultiple( in ArgHandles stream, in ArgTitles stream, in ArgField field ) global

Converts multiple CSV objects into HTML-tables which can be viewed in Stream field with Viewtype HTML
Carriage returns escaped with <br>
Cell values are escaped with ConvertBlock( …, 13 ) for HTML compatibility

ArgHandles - stream contained handles of CSV objects
ArgTitles - stream contained titles of the tables (has to be the same size of ArgHandles)
ArgField - field of type Stream and Viewtype HTMLView to show the content

To set one CSV-object to HTML field - see the function CSVtoHTMLField

If the passed field is not stream - returns errorcode 30

Modules

Module class Instance Comment
SYSTDC SYSTDC
SYSTDT SYSTDT