CSV Data Source

You can build REST applications in  with connectivity to comma-separated values (CSV) files, tab-separated values (TSV), or other delimited type files. You can connect to a file location and read text files with comma, tab, or user-defined column delimiter symbols as tables using this data source.
calac41
You can build REST applications in 
CA Live API Creator
 with connectivity to comma-separated values (CSV) files, tab-separated values (TSV), or other delimited type files. You can connect to a file location and read text files with comma, tab, or user-defined column delimiter symbols as tables using this data source.
The tables you create from CSV files are read-only. All columns are defined as char data type by default. Since the tables you create from the CSV data source do not have a primary key, you cannot update them directly.
For more information about how to create tables in your data source, see Manage Tables in Managed Databases.
In this article:
2
Verify the Prerequisites
Before connecting to one of your file directories containing CSV/TSV files, verify that you have the following information:
  • You know the CSV/TSV file directory location.
  • You know the format of the files that you will access using the CSV JDBC driver. For example, CSV, TSV, Text and the delimiter type used within these files.
Install the CSV JDBC Driver
If you have installed
CA Live API Creator
 on Tomcat, copy the 
cdata.jdbc.csv.jar
 JAR file (the JDBC driver) from the 
databaseDrivers
 directory to the 
%{CATALINA_HOME}
/lib
 directory. After you have installed the JDBC driver, the CSV data source is available as an option in the connection wizard when creating an API.
The self-contained, single-user version of 
CA Live API Creator
 based on Jetty includes the CSV JDBC driver. The JDBC driver is located in the 
caliveapicreator/lib/ext
 directory. If you have installed this version of
CA Live API Creator
, no additional steps are required to install the JDBC driver.
 For more information:
Connect to a CSV Data Source
You define CSV as your data source by connecting to the file directory where your CSV/TSV files are located. Connect to your CSV/TSV file directory as a data source when you create your API in API Creator. If you have different types of file formats or want to include files without headers, create another data source and specify how the files should be parsed and read.
For more information about how to create an API, see Create your API.
  1. In the connection wizard, click 
    CSV Files
    .
  2. Complete the following fields and then click
    Continue
    :
    Directory
    The directory that contains the CSV/TSV files.
    Examples:
      • (MAC/LINUX) 
        /Users/tmp/csvfiles
      • (Windows) 
        C:\temp\CSVfiles
    File Extensions
    The comma-separated list of file extensions. The file extension must start with a period.
    Examples: 
    .csv
    .txt
    .log
    , and 
    .tab
    .
    Has Header Row
    Defines whether the parsed file contains a header definition/column name.
    Values: 
    Yes or No
    Default:
     Yes
    Delimiter Type
    Defines the type of delimiter (the File Format (FMT)) that the CSV driver should use to parse and read the files. For example, If the first column contains the header definition, define the delimiter type as 
    HDR=yes
    .
    Values:
      • CSVDelimited
      • TABDelimited
      • Delimited(|)
    Default:
     CSVDelimited
    Line Ending
    If you are using Unix-based operating systems, such as Linux and Mac OS X, you may need to set the 
    LineEnding
     property to avoid issues.
    Values:
     CRLF or LF
    Default: 
    LF
You are connected to the CSV/TSV file directory as a data source.
Additional Information
The following topics provide additional information about working with your CSV data source.
Update Data from a CSV Data Source
You can update the tables you create from the CSV data source in
CA Live API Creator
 by exporting the schema, modifying the fields as needed, and importing the data from the CSV file by way of a POST.
Fields represent the columns in your database. The term field is used to stay within the metaphor of applications and screens, and not database terminology.
  1. Add a data source for a managed database within a managed data server.
    For more information about how to add a data source for a managed database in a managed data server, see Managed Data Server Administration.
  2. Export the schema definition created from the CSV data source.
    For more information about how to export your schema, Database Administration.
  3. (Optional) Modify the fields.
  4. Import the schema definition into the data source for a managed database.
    For more information about how to import your schema, see Database Administration.
  5. Perform a GET from your read-only CSV file and POST the data to your modified schema.
    The data is imported from the CSV file.
    The CSV JDBC driver is read-only. You cannot perform a POST or PUT content using this JDBC driver. Attempting this results in the following server error:
    server error 500 "Internal server error: Only simple select statement is supported"
    You can export the schema definition and then import the schema definition into a data source for a managed database. After you do this, you can POST the JSON content of your CSV dataset into new endpoints.
The tables are updated.
Override the Column Name and Formats
You can override the column name, data type, and formats for each file you access by way of the CSV data source.
Do one of the following:
  • With your API open, append the 
    Include
    Subdirectories=true
     property to the end of the URL in the 
    D
    ata source URL
     field on the Connection page.
    For more information about this field, see Database Connectivity.
    IncludeSubdirectories=True
    Example:
    [temp_statecodes.txt] ColNameHeader=True Format=TabDelimited DateTimeFormat=M/d/yyyy Col1=StateCode Text Width 2 Col2=StateDescription Text Width 50
  • Read files from subdirectories. Create a 
    schema.ini
     file in the same directory that contains the CSV/TSV files. This file contains the definitions. Include the 
    schema.ini
     file in each subdirectory. 
    CA Live API Creator
     pre-pends the directory name to the front of each of the files found in the subdirectory.
    For more information about the 
    schema.ini
     file and the formats that are required, see the Microsoft documentation.