ICsv interface

ICsv interface is used to work with CSV files and CSV-formatted strings. Parses CSV, creates/stores/manages 2-dim table in memory, composes CSV from the table.

CSV is a simple text format used to store tables. Rows are separated by new lines, cells by commas. Values containing new lines, commas or double quotes are enclosed in double quotes. Double quotes are doubled. Spaces around commas and new lines are ignored. Example:

value1,value2, value3
11,22,33
"value, with, commas","value with ""quotes""","multiline
value"
,,
,rows with empty values,
,,

The CSV file format is supported by many programs, including Microsoft Excel, and therefore can be used to exchange data between them. Another popular file format XML is more powerful but adds much overhead (slower parsing/composing, requires more space in file and in memory). To store tables, CSV format usually is better and easier to use.

You can find more information about CSV on the Internet.

CSV files also can be manipulated using Database class, but it is slower.

Added in QM 2.3.0.

Declare an ICsv variable and call _create. Then use ICsv interface functions.

Example 1

 a CSV string for testing
str csv=
 A1, B1, C1
 A2, B2, C2

 create ICsv variable and load data
ICsv x._create
x.FromString(csv)

 get a cell
str cell
cell=x.Cell(1 1)
out F"cell 1 1 was: {cell}"

 change cell
cell="Changed"
x.Cell(1 1)=cell

 get CSV data to string
x.ToString(csv)

 results
out "changed CSV:"
out csv

Example 2

 craete ICsv variable, change separator and load data from file
str s
ICsv v._create
v.Separator=";"
v.FromFile("$my qm$\test.csv")

 enumerate rows and columns
int nr=v.RowCount
int nc=v.ColumnCount
int r c
for r 0 nr ;;for each row
	out "--- row %i ---" r
	for c 0 nc ;;for each column
		s=v.Cell(r c)
		out s

 v.ToString(s); out s ;;show CSV

 save to file
v.ToFile("$my qm$\test.csv")

Examples of adding rows

Instead of _create can be used global function CreateCsv. Before QM 2.3.4, _create could not be used.

ICsv'CreateCsv [flags] ;;flags: 1 separator is comma

Creates object and returns ICsv COM interface pointer.

flags (QM 2.3.2):

1	Use comma as separator. Same as var.Separator=",". If this flag not used, default separator is as specified in Control Panel. Note that this behavior is different than with _create, where default separator is comma.

Example: ICsv x=CreateCsv(1)

ICsv member functions

Member functions are called like: variable.Function(arguments). See the example code at the top of this topic. Note that the colored code lines below are not function calling examples. They are copied from interface declaration and used here to show function name, arguments etc.

[p]Separator($sep)
[g]$Separator()

Sets or gets (QM 2.3.2) separator that is used when parsing and composing CSV. Example: v.Separator=";".

If sep is "", uses separator specified in Control Panel -> Regional and Language Options -> Customize... -> List Separator.

Info: Excel, when opening and saving CSV files, also uses the separator that is set in Control Panel.

Initially the variable uses comma if it was created with _create. Uses "" if created with CreateCsv without flag 1.

FromString($s)

Parses CSV string and creates table in memory. The ICsv variable manages the table.

Error if the CSV string contains errors. Also, must match separator (see Separator, above).

ToString(str&so)

Composes CSV string from the memory table and store an a str variable.

FromFile($file)

Parses CSV file and creates table in memory.

Error if the CSV file contains errors or uses different separator.

Supports macro resources (QM 2.4.1) and exe resources.

ToFile($file [flags]) ;;flags: 1 append, 0x100 safe, 0x200 safe+backup

Saves to file in CSV format.

flags:

1	Append.
0x100	QM 2.4.0. Safe saving. The file will never be corrupted on power failure etc. Writes to a temporary file, flushes its buffers, and renames the temporary file to file, replacing if exists.
0x200	QM 2.4.0. Safe saving and backup. Same as 0x100, but also creates a backup file, named file-backup.

QM 2.3.5. Creates parent folder if does not exist.

FromArray(ARRAY(str)&a)

Creates the memory table from 2-dim array.

Added in QM 2.3.4.

Note: in 2-dim arrays, the first dimension is for columns, the second for rows.

ToArray(ARRAY(str)&a)

Creates 2-dim array from the memory table.

Added in QM 2.3.4.

Note: in 2-dim arrays, the first dimension is for columns, the second for rows.

FromQmGrid(hwnd [flags]) ;;flags: 1 no first column, 2 no empty rows, 4 selected

Gets cells from QM_Grid control and creates table in memory.

hwnd - control handle.

flags:

1	don't get first column.
2	don't get empty rows.
4 (QM 2.3.2)	get only selected or checked (depending on style) rows.
8 (QM 2.3.4)	remove <...> in first column that was used to set row type.

Does not change Separator and RowDataSize.

ToQmGrid(hwnd [flags]) ;;flags: 1 only first column, 2 no first column

Populates QM_Grid control.

hwnd - control handle.

flags:

1	only first column.
2	except first column.

If flag 2 used, does not clear the control. The control must already contain cells in the first column. Cells in other columns can be empty or not.

If flag 1 used, the CSV table should contain 1 column. The grid can contain 1 or more columns.

If flag 2 used, the CSV table should contain control's column count -1.

Else the CSV table should contain control's column count.

Clear()

Deletes all rows.

Sets ColumnCount to 0.

Does not change Separator and RowDataSize.

[g]#RowCount()
[g]#ColumnCount()
[p]ColumnCount(count)

Gets the number of rows or columns.

QM 2.3.2. ColumnCount also can be used to set column count.

[g]$Cell(row col)
[p]Cell(row col $value)

Gets or sets cell value. See the example at the beginning.

row - 0-based row index.

col - 0-based column index.

value - cell text.

The returned string of the "get" function is temporary. It becomes invalid after calling a function that modifies the table. To use it later, assign it to a str variable, like in the example.

QM 2.4.3: Can set cell even if cell specified by row and col does not exist. If col is > column count, adds more columns. If row is = row count or < 0, adds new row. Would be error in older QM.

[g]#CellInt(row col)
[p]CellInt(row col value)
[p]CellHex(row col value)

Gets or sets cell value. The same as Cell (see above), but converts string to/from number.

row - 0-based row index.

col - 0-based column index.

value - an integer value.

The "get" function gets cell text and converts to int. Cell text can be a number in decimal or hexadecimal format. Can contain operator |. For example, returns 7 if cell text is "1|2|0x4". If cell text is empty or does not begin with a number, sets _hresult=1 and returns 0.

There are two "put" functions:

CellInt puts value converted to decimal format string, like "10".
CellHex puts value converted to hexadecimal format string, like "0xA".

Added in QM 2.4.3.

Examples

ICsv x._create
x.FromString("one,1[]two,2")
int i=5

x.CellInt(0 1)=i ;;the same as x.Cell(0 1)=F"{i}"
out x.Cell(0 1) ;;"5"

x.CellHex(0 1)=i ;;the same as x.Cell(0 1)=F"0x{i}"
out x.Cell(0 1) ;;"0x5"

i=x.CellInt(0 1) ;;the same as i=val(x.Cell(0 1))
if(_hresult) out "not a number"; else out i

RemoveRow(row)

Removes row.

row - 0-based row index.

#AddRowMS(row [nCells] [$cells] [firstCell])
#AddRowLA(row [nCells] [lpstr*cells] [firstCell])
#AddRowSA(row [nCells] [str*cells] [firstCell])

Adds or inserts new row.

See also (below): AddRow1, AddRow2, AddRow3, AddRowCSV.

row - 0-based row index where to insert the new row. Use an invalid index (e.g. -1) to add to the end.

nCells - number of cells to add.

cells - cell values.

firstCell (QM 2.3.2) - 0-based column index where to insert the cells.

Each of these functions differs only by the format of the values array (cells).

Function	cells type	Comments
AddRowMS	lpstr	cells must be in multistring format, ie multiple null-terminated strings following each other, like "string1[0]string2[0]string3[0]". See also GetRowMS, below.
AddRowLA	lpstr*	cells must be address of the first variable in an array of lpstr variables.
AddRowSA	str*	cells must be address of the first variable in an array of str variables.

If the number of columns (ColumnCount) is still not set (is 0), sets it to nCells+firstCell. You can also set it with ColumnCount before adding rows.

If cells or nCells is omitted or 0, adds 1 empty row. You can use Cell to set cell values.

Cells that are outside the range specified by firstClell and nCells will be "". If some strings in cells are null, the cells will be "". If RowDataSize is nonzero, the functions fill row data with 0.

QM 2.4.3: If nCells+firstCell is > ColumnCount, adds more columns. Also if nCells is 0 and ColumnCount is 0. In both cases, in older QM would be error (need to set column count with ColumnCount).

Examples

out
ICsv x._create
x.ColumnCount=3

 ------------

 add empty row and use Cell
int r=x.AddRowMS(-1)
x.Cell(r 0)="c1"; x.Cell(r 1)="c2"; x.Cell(r 2)="c3"

 MS
lpstr multistring="ms1[0]ms2[0]ms3"
x.AddRowMS(-1 3 multistring)

 LA with array
str st="a1 a2 a3"
ARRAY(lpstr) a
int nt=tok(st a 3 "" 1)
x.AddRowLA(-1 nt &a[0])

 SA with array
ARRAY(str) as="as1[]as2[]as3"
x.AddRowSA(-1 as.len &as[0])

 LA with local variables
lpstr s1("s1") s2("s1") s3("s1")
x.AddRowLA(-1 3 &s1)

 SA with local variables
str ss1("ss1") ss2("ss1") ss3("ss1")
x.AddRowSA(-1 3 &ss1)

 ------------

str s
x.ToString(s)
out s

#AddRow1(row $s1)
#AddRow2(row $s1 [$s2])
#AddRow3(row $s1 [$s2] [$s3])

Adds or inserts row with 1, 2 or 3 cells. Similar to the above functions. Use when it is more convenient to pass 1, 2 or 3 values than to use an array.

row - 0-based row index where to insert the new row. Use an invalid index (e.g. -1) to add to the end.

s1, s2, s3 - cell text.

AddRow2 added in QM 2.3.4, other functions in QM 2.4.3.

#AddRowCSV(row $s1)
#ReplaceRowCSV(row $s1 [$s2])

AddRowCSV adds or inserts one or more rows, using a CSV string to specify the cells. Returns first new row index.

ReplaceRowCSV replaces one or more rows, using a CSV string to specify the cells. Returns first replaced row index.

row - 0-based row index where to insert the new row. Use an invalid index (e.g. -1) to add to the end.

csv - CSV string containing one or more rows and any number of columns. Don't forget to "enclose" cells containing commas etc.

Added in QM 2.4.3.

Example

ICsv x._create
x.FromString("A,B[]C,D")
out x.AddRowCSV(-1 "E,F")
x.ToString(_s); out _s

#ReplaceRowMS(row [nCells] [$cells] [firstCell])
#ReplaceRowLA(row [nCells] [lpstr*cells] [firstCell])
#ReplaceRowSA(row [nCells] [str*cells] [firstCell])

Replaces row.

See also (above): Cell, ReplaceRowCSV.

Replaces nCells cells of row row, starting from firstCell. Does not change other cells. If row does not exist, adds new row to the end like the AddRowX functions.

Everything else is as with the AddRowMS/LA/SA functions.

Added in QM 2.3.2.

GetRowMS(row str*cells)

Stores all row cells into a str variable in multistring format.

Added in QM 2.3.2.

The variable then can be used to add/insert/replace a row of this or another ICsv variable. Use function AddRowMS or ReplaceRowMS (see above).

MoveRow(row to)

Moves row.

row - index of row to move.

to - new index of the row.

Added in QM 2.3.2.

InsertColumn(col)
RemoveColumn(col)

InsertColumn - inserts 1 empty column. If col is invalid (e.g. -1), adds to the end.

RemoveColumn - removes 1 column.

Alternatively you can use ColumnCount to add or remove columns.

Added in QM 2.3.2.

[p]RowDataSize(nBytes)
[g]#RowDataSize()
[g]!*RowData(row)

RowDataSize - sets or gets size of extra memory to allocate for each row.

RowData - returns address of extra memory of a row.

Allows to place any data in each row. It simplifies using ICsv as base of new table-based classes. The data exists only in memory.

Added in QM 2.3.2.

Sort(flags [col]) ;;flags: 0 simple, 1 insens, 2 ling, 3 ling/insens, 4 number/ling/insens, 128 date, 0x100 descending

Sorts rows using text of one of columns.

flags:

0	Simple, case sensitive. Uses StrCompare to compare strings.
1	Simple, case insensitive. Uses StrCompare to compare strings.
2	Linguistic, case sensitive. Uses StrCmp to compare strings.
3	Linguistic, case insensitive. Uses StrCmpI to compare strings.
4	Number, linguistic, case insensitive. Uses StrCmpLogicalW to compare strings. It compares numbers in strings as number values, not as strings.
128	QM 2.3.3. Date.
0x100	Sort descending.

col - 0-based column index.

Added in QM 2.3.2.

#Find($s [flags] [col] [startRow]) ;;flags: 1 insens, 2 wildcard, 4 beginning, 8 end, 16 middle, 32 rx

Finds row. Returns 0-based row index, or -1 if not found.

s - cell text.

flags - how to compare strings.

col - 0-based column index.

startRow (QM 2.4.3) - 0-based row index from where to start searching.

Added in QM 2.3.4.

Notes

ICsv functions are not thread-safe. Don't use a single variable in multiple threads simultaneously. It can damage data. If need to use in multiple threads, use lock.