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. With the JDBC driver installed, CSV Files is available as an option in the connection wizard when creating an API.
lac51
You can build REST applications in 
Layer7 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. With the JDBC driver installed, 
CSV Files
 is available as an option in the connection wizard when creating an API.
In this article:
 
 
2
 
 
Verify the Prerequisites
Before connecting to a file directory that contains 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
The self-contained, single-user version of 
Layer7 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 
Layer7 Live API Creator
, no additional steps are required to install the JDBC driver.
If you have installed 
Layer7 Live API Creator
 on another Java container, such as Tomcat, copy the 
cdata.jdbc.csv.jar
 JAR file (the JDBC driver) that is located in the 
databaseDrivers
 directory into the directory that is based on the Java container on which you have installed 
Layer7 Live API Creator
:
Apache Tomcat
Copy the JDBC driver into the 
%{CATALINA_HOME}/lib
 directory
Oracle WebLogic
Copy the JDBC driver to the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver to your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation.
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.
You can connect to your CSV/TSV file directory as a data source using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API and you have different types of file formats or you want to include files with headers) By adding a connection to your CSV/TSV file director as a data source, and then specify how the files should be parsed and read.
    For more information about how to add a connection to a data source, see Database Connectivity.
The following procedure describes how to connect to your CSV/TSV file directory as a data source when creating your API.
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select 
    CSV Files
     as the data source.
  4. 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 
Layer7 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
     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. 
    Layer7 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.
CSV Data Source Limitation
The tables that you create from CSV files are read-only. The columns are defined as 
char
 data type by default. Since the tables that you create from CSV data sources do not have a primary key, you cannot update them directly.
For more information about how to create tables in CSV data sources, see Manage Tables in Managed Databases.