Package org.h2.tools
Class Csv
java.lang.Object
org.h2.tools.Csv
- All Implemented Interfaces:
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 -
Method Summary
Modifier and TypeMethodDescriptionvoidclose()INTERNALbooleanGet the current case sensitive column names setting.charGet the current escape character.charGet the current field delimiter.charGet the current field separator for reading.Get the current field separator for writing.charGet the line comment character.Get the line separator used for writing.Get the current null string.booleanWhether whitespace in unquoted text is preserved.booleanWhether the column header is written.booleanReturns true if thenull valuesare quoted.Reads CSV data from a reader and returns a result set.Reads from the CSV file and returns a result set.Object[]readRow()INTERNALvoidreset()INTERNALvoidsetCaseSensitiveColumnNames(boolean caseSensitiveColumnNames) Override the case sensitive column names setting.voidsetEscapeCharacter(char escapeCharacter) Set the escape character.voidsetFieldDelimiter(char fieldDelimiter) Set the field delimiter.voidsetFieldSeparatorRead(char fieldSeparatorRead) Override the field separator for reading.voidsetFieldSeparatorWrite(String fieldSeparatorWrite) Override the field separator for writing.voidsetLineCommentCharacter(char lineCommentCharacter) Set the line comment character.voidsetLineSeparator(String lineSeparator) Set the line separator used for writing.voidsetNullString(String nullString) Set the value that represents NULL.setOptions(String options) INTERNAL.voidsetPreserveWhitespace(boolean value) Enable or disable preserving whitespace in unquoted text.voidsetQuotedNulls(boolean quotedNulls) Defines if thenull valuesmust be quoted.voidsetWriteColumnHeader(boolean value) Enable or disable writing the column header.intWrites the result set to a file in the CSV format.intWrites the result set to a file in the CSV format.intwrite(Connection conn, String outputFileName, String sql, String charset) Writes the result set of a query to a file in the CSV format.
-
Constructor Details
-
Csv
public Csv()
-
-
Method Details
-
write
Writes the result set to a file in the CSV format.- Parameters:
writer- the writerrs- the result set- Returns:
- the number of rows written
- Throws:
SQLException- on failure
-
write
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 filers- 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:
SQLException- on failure
-
write
public int write(Connection conn, String outputFileName, String sql, String charset) throws SQLException Writes the result set of a query to a file in the CSV format.- Parameters:
conn- the connectionoutputFileName- the file namesql- the querycharset- the charset or null to use the system default charset (see system property file.encoding)- Returns:
- the number of rows written
- Throws:
SQLException- on failure
-
read
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 namecolNames- or null if the column names should be read from the CSV filecharset- the charset or null to use the system default charset- Returns:
- the result set
- Throws:
SQLException- on failure
-
read
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 readercolNames- or null if the column names should be read from the CSV file- Returns:
- the result set
- Throws:
IOException- on failure
-
readRow
INTERNAL- Specified by:
readRowin interfaceSimpleRowSource- Returns:
- the row or null
- Throws:
SQLException- on failure
-
close
public void close()INTERNAL- Specified by:
closein interfaceSimpleRowSource
-
reset
INTERNAL- Specified by:
resetin interfaceSimpleRowSource- Throws:
SQLException- if this operation is not supported
-
setFieldSeparatorWrite
Override the field separator for writing. The default is ",".- Parameters:
fieldSeparatorWrite- the field separator
-
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
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
Get the line separator used for writing.- Returns:
- the line separator
-
setQuotedNulls
public void setQuotedNulls(boolean quotedNulls) Defines if thenull valuesmust be quoted.- Parameters:
quotedNulls- True if the null values must be quoted.
-
isQuotedNulls
public boolean isQuotedNulls()Returns true if thenull valuesare quoted.- Returns:
- True if the null values are quoted.
-
setNullString
Set the value that represents NULL. It is only used for non-delimited values.- Parameters:
nullString- the null
-
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
INTERNAL. Parse and set the CSV options.- Parameters:
options- the options- Returns:
- the character set
-