Skip to main content

csvread

1. CSVREAD Function​

The CSVREAD function in H2 database is used to read data from a CSV (Comma-Separated Values) file and import it into a table.

2. Syntax​

The syntax for the CSVREAD function in H2 database is as follows:

CSVREAD(fileName [, columnNames [, options]])

Arguments​

  • fileName: The name or path of the CSV file to be read. It should be a string value.
  • columnNames (optional): A comma-separated list of column names to be used in the created table. If not specified, the column names will be inferred from the CSV file.
  • options (optional): Additional options for reading the CSV file. This parameter supports various options such as separator, quote character, etc. Refer to the H2 documentation for more details.

Return​

  • The CSVREAD function returns a result set containing the data from the CSV file.

3. Notes​

  • The CSV file should be accessible and readable by the H2 database engine.
  • The CSV file should have proper headers or column names if the columnNames parameter is not provided.
  • The CSVREAD function assumes that the values in the CSV file are comma-separated by default. However, you can specify a different separator using the options parameter.
  • If the CSV file contains quoted values, you can specify the quote character using the options parameter.
  • The CSVREAD function is a table function, so it can be used in the FROM clause of a SELECT statement to retrieve data directly from the CSV file.

4. Examples​

Here are a few examples demonstrating the usage of the CSVREAD function in H2 database:

Example 1 - Reading a CSV file and inferring column names:​

CREATE TABLE myTable AS
SELECT * FROM CSVREAD('path/to/myfile.csv');

Example 2 - Reading a CSV file and specifying column names:​

CREATE TABLE myTable AS
SELECT * FROM CSVREAD('path/to/myfile.csv', 'col1, col2, col3');

Example 3 - Reading a CSV file with custom options:​

CREATE TABLE myTable AS
SELECT * FROM CSVREAD('path/to/myfile.csv', 'col1, col2, col3', 'SEPARATOR=;, QUOTE=\'"\'');
  • csvwrite - Write data from a table to a CSV file.