org.h2.tools

Class Csv

java.lang.Object

org.h2.tools.Csv

All Implemented Interfaces:

SimpleRowSource

public class Csv
extends java.lang.Object
implements SimpleRowSource

A facility to read from and write to CSV (comma separated values) files. When reading, the BOM (the byte-order-mark) character 0xfeff at the beginning of the file is ignored.

Constructor Summary

Constructors

Constructor and Description
Csv()

Method Summary

Modifier and Type	Method and Description
void	close() INTERNAL
boolean	getCaseSensitiveColumnNames() Get the current case sensitive column names setting.
char	getEscapeCharacter() Get the current escape character.
char	getFieldDelimiter() Get the current field delimiter.
char	getFieldSeparatorRead() Get the current field separator for reading.
java.lang.String	getFieldSeparatorWrite() Get the current field separator for writing.
char	getLineCommentCharacter() Get the line comment character.
java.lang.String	getLineSeparator() Get the line separator used for writing.
java.lang.String	getNullString() Get the current null string.
boolean	getPreserveWhitespace() Whether whitespace in unquoted text is preserved.
boolean	getWriteColumnHeader() Whether the column header is written.
boolean	isQuotedNulls() Returns true if the null values are quoted.
java.sql.ResultSet	read(java.io.Reader reader, java.lang.String[] colNames) Reads CSV data from a reader and returns a result set.
java.sql.ResultSet	read(java.lang.String inputFileName, java.lang.String[] colNames, java.lang.String charset) Reads from the CSV file and returns a result set.
java.lang.Object[]	readRow() INTERNAL
void	reset() INTERNAL
void	setCaseSensitiveColumnNames(boolean caseSensitiveColumnNames) Override the case sensitive column names setting.
void	setEscapeCharacter(char escapeCharacter) Set the escape character.
void	setFieldDelimiter(char fieldDelimiter) Set the field delimiter.
void	setFieldSeparatorRead(char fieldSeparatorRead) Override the field separator for reading.
void	setFieldSeparatorWrite(java.lang.String fieldSeparatorWrite) Override the field separator for writing.
void	setLineCommentCharacter(char lineCommentCharacter) Set the line comment character.
void	setLineSeparator(java.lang.String lineSeparator) Set the line separator used for writing.
void	setNullString(java.lang.String nullString) Set the value that represents NULL.
java.lang.String	setOptions(java.lang.String options) INTERNAL.
void	setPreserveWhitespace(boolean value) Enable or disable preserving whitespace in unquoted text.
void	setQuotedNulls(boolean quotedNulls) Defines if the null values must be quoted.
void	setWriteColumnHeader(boolean value) Enable or disable writing the column header.
int	write(java.sql.Connection conn, java.lang.String outputFileName, java.lang.String sql, java.lang.String charset) Writes the result set of a query to a file in the CSV format.
int	write(java.lang.String outputFileName, java.sql.ResultSet rs, java.lang.String charset) Writes the result set to a file in the CSV format.
int	write(java.io.Writer writer, java.sql.ResultSet rs) Writes the result set to a file in the CSV format.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

Csv

public Csv()

Method Detail

write

public int write(java.io.Writer writer,
                 java.sql.ResultSet rs)
          throws java.sql.SQLException

Writes the result set to a file in the CSV format.

Parameters:

writer - the writer

rs - the result set

Returns:

the number of rows written

Throws:

java.sql.SQLException - on failure

write

public int write(java.lang.String outputFileName,
                 java.sql.ResultSet rs,
                 java.lang.String charset)
          throws java.sql.SQLException

Writes the result set to a file in the CSV format. The result set is read using the following loop:

 while (rs.next()) {
     writeRow(row);
 }
 

Parameters:

outputFileName - the name of the csv file

rs - the result set - the result set must be positioned before the first row.

charset - the charset or null to use the system default charset

Returns:

the number of rows written

Throws:

java.sql.SQLException - on failure

write

public int write(java.sql.Connection conn,
                 java.lang.String outputFileName,
                 java.lang.String sql,
                 java.lang.String charset)
          throws java.sql.SQLException

Writes the result set of a query to a file in the CSV format.

Parameters:

conn - the connection

outputFileName - the file name

sql - the query

charset - the charset or null to use the system default charset (see system property file.encoding)

Returns:

the number of rows written

Throws:

java.sql.SQLException - on failure

read

public java.sql.ResultSet read(java.lang.String inputFileName,
                               java.lang.String[] colNames,
                               java.lang.String charset)
                        throws java.sql.SQLException

Reads from the CSV file and returns a result set. The rows in the result set are created on demand, that means the file is kept open until all rows are read or the result set is closed. If the columns are read from the CSV file, then the following rules are used: columns names that start with a letter or '_', and only contain letters, '_', and digits, are considered case insensitive and are converted to uppercase. Other column names are considered case sensitive (that means they need to be quoted when accessed).

Parameters:

inputFileName - the file name

colNames - or null if the column names should be read from the CSV file

charset - the charset or null to use the system default charset

Returns:

the result set

Throws:

java.sql.SQLException - on failure

read

public java.sql.ResultSet read(java.io.Reader reader,
                               java.lang.String[] colNames)
                        throws java.io.IOException

Reads CSV data from a reader and returns a result set. The rows in the result set are created on demand, that means the reader is kept open until all rows are read or the result set is closed.

Parameters:

reader - the reader

colNames - or null if the column names should be read from the CSV file

Returns:

the result set

Throws:

java.io.IOException - on failure

readRow

public java.lang.Object[] readRow()
                           throws java.sql.SQLException

INTERNAL

Specified by:

readRow in interface SimpleRowSource

Returns:

the row or null

Throws:

java.sql.SQLException - on failure

close

public void close()

INTERNAL

Specified by:

close in interface SimpleRowSource

reset

public void reset()
           throws java.sql.SQLException

INTERNAL

Specified by:

reset in interface SimpleRowSource

Throws:

java.sql.SQLException - if this operation is not supported

setFieldSeparatorWrite

public void setFieldSeparatorWrite(java.lang.String fieldSeparatorWrite)

Override the field separator for writing. The default is ",".

Parameters:

fieldSeparatorWrite - the field separator

getFieldSeparatorWrite

public java.lang.String getFieldSeparatorWrite()

Get the current field separator for writing.

Returns:

the field separator

setCaseSensitiveColumnNames

public void setCaseSensitiveColumnNames(boolean caseSensitiveColumnNames)

Override the case sensitive column names setting. The default is false. If enabled, the case of all column names is always preserved.

Parameters:

caseSensitiveColumnNames - whether column names are case sensitive

getCaseSensitiveColumnNames

public boolean getCaseSensitiveColumnNames()

Get the current case sensitive column names setting.

Returns:

whether column names are case sensitive

setFieldSeparatorRead

public void setFieldSeparatorRead(char fieldSeparatorRead)

Override the field separator for reading. The default is ','.

Parameters:

fieldSeparatorRead - the field separator

getFieldSeparatorRead

public char getFieldSeparatorRead()

Get the current field separator for reading.

Returns:

the field separator

setLineCommentCharacter

public void setLineCommentCharacter(char lineCommentCharacter)

Set the line comment character. The default is character code 0 (line comments are disabled).

Parameters:

lineCommentCharacter - the line comment character

getLineCommentCharacter

public char getLineCommentCharacter()

Get the line comment character.

Returns:

the line comment character, or 0 if disabled

setFieldDelimiter

public void setFieldDelimiter(char fieldDelimiter)

Set the field delimiter. The default is " (a double quote). The value 0 means no field delimiter is used.

Parameters:

fieldDelimiter - the field delimiter

getFieldDelimiter

public char getFieldDelimiter()

Get the current field delimiter.

Returns:

the field delimiter

setEscapeCharacter

public void setEscapeCharacter(char escapeCharacter)

Set the escape character. The escape character is used to escape the field delimiter. This is needed if the data contains the field delimiter. The default escape character is " (a double quote), which is the same as the field delimiter. If the field delimiter and the escape character are both " (double quote), and the data contains a double quote, then an additional double quote is added. Example:

 Data: He said "Hello".
 Escape character: "
 Field delimiter: "
 CSV file: "He said ""Hello""."
 

If the field delimiter is a double quote and the escape character is a backslash, then escaping is done similar to Java (however, only the field delimiter is escaped). Example:

 Data: He said "Hello".
 Escape character: \
 Field delimiter: "
 CSV file: "He said \"Hello\"."
 

The value 0 means no escape character is used.

Parameters:

escapeCharacter - the escape character

getEscapeCharacter

public char getEscapeCharacter()

Get the current escape character.

Returns:

the escape character

setLineSeparator

public void setLineSeparator(java.lang.String lineSeparator)

Set the line separator used for writing. This is usually a line feed (\n or \r\n depending on the system settings). The line separator is written after each row (including the last row), so this option can include an end-of-row marker if needed.

Parameters:

lineSeparator - the line separator

getLineSeparator

public java.lang.String getLineSeparator()

Get the line separator used for writing.

Returns:

the line separator

setQuotedNulls

public void setQuotedNulls(boolean quotedNulls)

Defines if the null values must be quoted.

Parameters:

quotedNulls - True if the null values must be quoted.

isQuotedNulls

public boolean isQuotedNulls()

Returns true if the null values are quoted.

Returns:

True if the null values are quoted.

setNullString

public void setNullString(java.lang.String nullString)

Set the value that represents NULL. It is only used for non-delimited values.

Parameters:

nullString - the null

getNullString

public java.lang.String getNullString()

Get the current null string.

Returns:

the null string.

setPreserveWhitespace

public void setPreserveWhitespace(boolean value)

Enable or disable preserving whitespace in unquoted text.

Parameters:

value - the new value for the setting

getPreserveWhitespace

public boolean getPreserveWhitespace()

Whether whitespace in unquoted text is preserved.

Returns:

the current value for the setting

setWriteColumnHeader

public void setWriteColumnHeader(boolean value)

Enable or disable writing the column header.

Parameters:

value - the new value for the setting

getWriteColumnHeader

public boolean getWriteColumnHeader()

Whether the column header is written.

Returns:

the current value for the setting

setOptions

public java.lang.String setOptions(java.lang.String options)

INTERNAL. Parse and set the CSV options.

Parameters:

options - the options

Returns:

the character set