The following example uses CSVSplit on a table of CLOBs representing CSV text files.
Example: Creating a Table Containing CSV Files
CREATE TABLE CSVFiles(id INTEGER, csvFile CLOB);
Example: Inserting CSV Data into the Table
INSERT INTO CSVFiles VALUES(0, 'ItemID,ItemName,Quantity,Price;10021,Paint Brush,10,10.99;10033,Paint,3,24.99');
Example: Creating a Table for Split Operation Results
CREATE MULTISET TABLE csv_table( file_id INTEGER, csv DATASET(8000) STORAGE FORMAT CSV CHARACTER SET LATIN);
Example: Invoking the Table Operator to Split up CSV Files
INSERT INTO csv_table
SELECT id, data FROM CSVSplit
(
ON (SELECT csvFile, id FROM CSVFiles)
RETURNS(data DATASET STORAGE FORMAT CSV CHARACTER SET LATIN)
USING SCHEMA('{"record_delimiter":";"}')
) AS T;
Example: Selecting out Individual CSV Values
SELECT file_id, csv FROM csv_table ORDER BY file_id;
Result:
> file_id csv
0 10033,Paint,3,24.99
0 10021,Paint Brush,10,10.99