This utility procedure/method instantiates a result set bound to a comma separated value (CSV) data source. The result set is returned as a dynamic result set in the procedure context object. Any records in the CSV source that begin with "--" are considered to be comments and are skipped. The columns from the data source form a result row. The structure of result row is defined as a row type descriptor passed as the pRowType formal parameter. The format of a row type descriptor is a comma delimited list of field definitions where each field definition contains a field name and a datatype. Both the field name and datatype follow the rules used by field definitions in a CREATE TABLE statement. For example:

ROW(StateFips VARCHAR(2),Zip_Code VARCHAR(5),State_Abbr VARCHAR(2),Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6))

For the purposes of the CSV method/procedure, the ROW() is optional.

Row type descriptors do not normally allow missing (empty) field definitions but for the specific purpose of describing the records of a CSV data source empty fields are allowed and indicate a skipped column in the data source. In the example below, the first two columns and the fourth column are ignored:

ROW(,,Zip_Code VARCHAR(5),,Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6))

It is not necessary to add additional commas to indicate columns skipped at the end of the record.

This utility can be invoked either as a class method or as a stored procedure. The parameters are:

pSelectMode	The select mode that defines the format of the data. Valid values are 0 - logical, 1 - ODBC, and 2 - Display.
pRowType	A comma-delimited list of comma specs as defined above. It is easier to access the data in the result set if the column names are also valid Cache Object identifiers.
pFileName	The name of the file containing the data.
pDelimiter	The csv delimiter character. The default is comma.
pQuote	The quote character. The default is a double quote. This is the character used to delimit values that might contain a value delimiter character or other control characters.

The result is returned in the procedure context object. This is normally %sqlcontext if the utility is invoked as a class method call. It is the result object if the procedure is invoked using dynamic SQL. For example:

set rowtype = "StateFips VARCHAR(2),Zip_Code VARCHAR(5),State_Abbr VARCHAR(2),Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6)"
set filename = "/Users/test/Documents/zip.csv"
set result = ##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.filename)
set resultSet = result.%NextResult()
write resultSet.%Next()
write resultSet.StateFips


Import a CSV document into a Cache persistent class. The row type defines the property names and types. Refer to CSV for more information on row types. The class will be generated if it does not already exist. If the class does exist then it is expected to implement the Import method with an interface compatible with the Import method generated by the utility. The easiest way to see the Import method interface is to generate a class from a row type.

This utility can be invoked either as a class method or as a stored procedure. The parameters are:

pSelectMode	The select mode that defines the format of the data. Valid values are 0 - logical, 1 - ODBC, and 2 - Display.
pRowType	A comma-delimited list of comma specs as defined above. It is easier to access the data in the result set if the column names are also valid Cache Object identifiers.
pFileName	The name of the file containing the data.
pDelimiter	The csv delimiter character. The default is comma.
pQuote	The quote character. The default is a double quote. This is the character used to delimit values that might contain a value delimiter character or other control characters.
pHeaderCount	The number of records that are to be skipped at the beginning of the file.
pClassName	The name of the class where the data will be imported. If this class does not yet exist then it will be generated from the row type. If this class already exists then it is assumed that the class definition matches the row type and that the Import method is implemented.