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.
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.
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:
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 Creatoron Tomcat, copy the
cdata.jdbc.csv.jarJAR file (the JDBC driver) from the
databaseDriversdirectory to the
/libdirectory. 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 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.
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.
- In the connection wizard, clickCSV Files.
- 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.