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.
You can build REST applications in
CA Live API Creatorwith 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 Filesis available as an option in the connection wizard when creating an API.
The tables that 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 CSV data source, see Manage Tables in Managed Databases.
In this article:
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
CA Live API Creatorbased on Jetty includes the CSV JDBC driver. The JDBC driver is located in the
caliveapicreator/lib/extdirectory. If you have installed this version of
CA Live API Creator, no additional steps are required to install the JDBC driver.
If you have installed
CA Live API Creatoron another Java container, such as Tomcat, copy the
cdata.jdbc.csv.jarJAR file (the JDBC driver) that is located in the
databaseDriversdirectory into the directory that is based on the Java container on which you have installed
CA Live API Creator:
Copy the JDBC driver into the
Copy the JDBC driver to the
<Your WebLogic Domain Directory>/libdirectory.
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:
- In API Creator, on the APIs page, clickCreate New API.The connection wizard opens.
- ClickDatabase First.
- SelectCSV Filesas the data source.
- Complete the following fields and then clickContinue:DirectoryThe directory that contains the CSV/TSV files.Examples:
File ExtensionsThe comma-separated list of file extensions. The file extension must start with a period.Examples:.csv,.txt,.log, and.tab.Has Header RowDefines whether the parsed file contains a header definition/column name.Values:Yes or NoDefault:YesDelimiter TypeDefines 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 asHDR=yes.Values:
Default:CSVDelimitedLine EndingIf you are using Unix-based operating systems, such as Linux and Mac OS X, you may need to set theLineEndingproperty to avoid issues.Values:CRLF or LFDefault:LF
You are connected to the CSV/TSV file directory as a data source.
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 Creatorby 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.
- 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.
- Export the schema definition created from the CSV data source.For more information about how to export your schema, Database Administration.
- (Optional) Modify the fields.
- Import the schema definition into the data source for a managed database.For more information about how to import your schema, see Database Administration.
- 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 theIncludeSubdirectories=trueproperty to the end of the URL in theData source URLfield on the Connection page.For more information about this field, see Database Connectivity.IncludeSubdirectories=TrueExample:[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 aschema.inifile in the same directory that contains the CSV/TSV files. This file contains the definitions. Include theschema.inifile in each subdirectory.CA Live API Creatorpre-pends the directory name to the front of each of the files found in the subdirectory.For more information about theschema.inifile and the formats that are required, see the Microsoft documentation.