Top

Name

ft_set_file — associates table with tabular data structure represented by CSV document content with external CSV file.

Synopsis

varchar ft_set_file ( in tb varchar ,
  in fname varchar ,
  in delimiter varchar ,
  in newline varchar (default '\n') ,
  in esc varchar (default NULL) ,
  in skip_rows integer (default 0) );
 

Description

Associates table with tabular data structure represented by CSV document content with external CSV file.

Parameters

tb

The name of the table that has tabular data structure represented by CSV document content.

Note that there is no special object type for File Tables corresponding to the tabular data structure represented by the CSV document content. To create a File Table, a user must have DBA group privileges. In addition, access to the referenced file is subject the same file system access functions (e.g., file_to_string and DirsAllowed INI file settings) that govern the whole Virtuoso instance.

The text in each field is parsed according to the data type declared for the column whose position in the CREATE TABLE statement corresponds to that field's position on the line. The parsing is as by the SQL CAST function from a VARCHAR value. If the CAST fails, the line is silently ignored.

fname

External CSV file name

delimiter

Delimiter to be used.

Each column in the CSV file is expected to end with the delimiter character. A field of zero length is considered a SQL NULL value; i.e., if two delimiters are adjacent, the field is considered NULL . Likewise, if a line begins with the delimiter, the first field is considered NULL .

newline

Set new line encoding. By default is '\n'.

esc

Set escape character. By default is NULL.

The newline and escape characters must be single character strings. A newline or escape character following the escape character will be added to the parsed input as a literal character, without its special interpretation.

skip_rows

Use to skip n rows from the begining. By default is 0. When set to 1 for ex., treats the first row as header and skips it. When set to 2 skips 2 rows etc.

Examples

Example 24.115. Using a Table as a Placeholder for CSV Attachment

In this example a SQL TABLE is created and then associated with an external CSV file. This file is situated in a host operating system location that is accessible to the Virtuoso instance using relative (to instance .db file) or full path file name reference (constrained by DirsAllowed entry in Virtuoso INI ).

CSV filename: contacts.csv (which can also be referred to as file:contacts.csv) .

File Content: 

Id,Fname,Sname,Age
0,John,Smith,48
1,Anna,Clarks,62
2,Rojer,Danrette,27
3,Kate,Sigton,56
4,Tim,Craft,41

CSV File to SQL Table association steps:

  1. CREATE an empty TABLE , using the SQL command: 

    CREATE TABLE csv.tutorial.contacts
  (
    Id INTEGER NOT NULL,
    Fname VARCHAR(20),
    Sname VARCHAR(20),
    Age INTEGER,
    CONSTRAINT demo_table_pk PRIMARY KEY (Id)
  );

    1. Notes:

      1. There is no special object type for File Tables corresponding to the tabular data structure represented by the CSV document content;

      2. To create a File Table, a user must have DBA group privileges. In addition, access to the referenced file is subject the same file system access functions (e.g., file_to_string and DirsAllowed INI file settings) that govern the whole Virtuoso instance.

  2. Use Virtuoso ft_set_file function to associate the above newly created internal TABLE (first procedure argument) with the external CSV file (second procedure argument, in the form of a file name relative to the Virtuoso instance); optional arguments may be used to specify delimiter, newline, and escape characters, respectively: 

    ft_set_file ('csv.tutorial.contacts', 'contacts.csv', ',', 1) ;

    1. Notes:

      1. The text in each field is parsed according to the data type declared for the column whose position in the CREATE TABLE statement corresponds to that field's position on the line. The parsing is as by the SQL CAST function from a VARCHAR value. If the CAST fails, the line is silently ignored.

      2. The newline and escape characters must be single character strings. A newline or escape character following the escape character will be added to the parsed input as a literal character, without its special interpretation.

      3. Each column in the CSV file is expected to end with the delimiter character. A field of zero length is considered a SQL NULL value; i.e., if two delimiters are adjacent, the field is considered NULL. Likewise, if a line begins with the delimiter, the first field is considered NULL.

  3. Check the inserted data in the csv.tutorial.contacts table: 

    SQL>SELECT * FROM csv.tutorial.contacts;

Id                Fname     Sname       F_AGE
INTEGER NOT NULL  VARCHAR   VARCHAR     INTEGER
______________________________________________________________________

0                 John      Smith       48
1                 Anna      Clarks      62
2                 Rojer     Danrette    27
3                 Kate      Sigton      56
4                 Tim       Craft       41

5 Rows. -- 15 msec.

Prev  Up  Next
file_open   Home  file_dirlist