Package 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.
-
-
Field Summary
Fields Modifier and Type Field Description private boolean
caseSensitiveColumnNames
private java.lang.String
characterSet
private java.lang.String[]
columnNames
private boolean
endOfFile
private boolean
endOfLine
private char
escapeCharacter
private char
fieldDelimiter
private char
fieldSeparatorRead
private java.lang.String
fieldSeparatorWrite
private java.lang.String
fileName
private java.io.BufferedReader
input
private char[]
inputBuffer
private int
inputBufferEnd
private int
inputBufferPos
private int
inputBufferStart
private char
lineComment
private java.lang.String
lineSeparator
private java.lang.String
nullString
private java.io.Writer
output
private boolean
preserveWhitespace
private boolean
writeColumnHeader
-
Constructor Summary
Constructors Constructor Description Csv()
-
Method Summary
All Methods Static Methods Instance Methods Concrete Methods Modifier and Type Method Description void
close()
INTERNALprivate static java.sql.SQLException
convertException(java.lang.String message, java.lang.Exception e)
private java.lang.String
escape(java.lang.String data)
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.private void
init(java.lang.String newFileName, java.lang.String charset)
private void
initRead()
private void
initWrite()
private static boolean
isParam(java.lang.String key, java.lang.String... values)
private static boolean
isSimpleColumnName(java.lang.String columnName)
private void
makeColumnNamesUnique()
private void
pushBack()
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.private int
readBuffer()
private int
readChar()
private void
readHeader()
private java.lang.String
readNull(java.lang.String s)
private java.sql.ResultSet
readResultSet(java.lang.String[] colNames)
java.lang.Object[]
readRow()
INTERNALprivate java.lang.String
readValue()
void
reset()
INTERNALvoid
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
setWriteColumnHeader(boolean value)
Enable or disable writing the column header.private java.lang.String
unEscape(java.lang.String s)
int
write(java.io.Writer writer, java.sql.ResultSet rs)
Writes the result set 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.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.private int
writeResultSet(java.sql.ResultSet rs)
private void
writeRow(java.lang.String[] values)
-
-
-
Field Detail
-
columnNames
private java.lang.String[] columnNames
-
characterSet
private java.lang.String characterSet
-
escapeCharacter
private char escapeCharacter
-
fieldDelimiter
private char fieldDelimiter
-
fieldSeparatorRead
private char fieldSeparatorRead
-
fieldSeparatorWrite
private java.lang.String fieldSeparatorWrite
-
caseSensitiveColumnNames
private boolean caseSensitiveColumnNames
-
preserveWhitespace
private boolean preserveWhitespace
-
writeColumnHeader
private boolean writeColumnHeader
-
lineComment
private char lineComment
-
lineSeparator
private java.lang.String lineSeparator
-
nullString
private java.lang.String nullString
-
fileName
private java.lang.String fileName
-
input
private java.io.BufferedReader input
-
inputBuffer
private char[] inputBuffer
-
inputBufferPos
private int inputBufferPos
-
inputBufferStart
private int inputBufferStart
-
inputBufferEnd
private int inputBufferEnd
-
output
private java.io.Writer output
-
endOfLine
private boolean endOfLine
-
endOfFile
private boolean endOfFile
-
-
Method Detail
-
writeResultSet
private int writeResultSet(java.sql.ResultSet rs) throws java.sql.SQLException
- Throws:
java.sql.SQLException
-
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 writerrs
- 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 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:
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 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:
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 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:
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 readercolNames
- or null if the column names should be read from the CSV file- Returns:
- the result set
- Throws:
java.io.IOException
- on failure
-
readResultSet
private java.sql.ResultSet readResultSet(java.lang.String[] colNames) throws java.io.IOException
- Throws:
java.io.IOException
-
makeColumnNamesUnique
private void makeColumnNamesUnique()
-
init
private void init(java.lang.String newFileName, java.lang.String charset)
-
initWrite
private void initWrite() throws java.io.IOException
- Throws:
java.io.IOException
-
writeRow
private void writeRow(java.lang.String[] values) throws java.io.IOException
- Throws:
java.io.IOException
-
escape
private java.lang.String escape(java.lang.String data)
-
initRead
private void initRead() throws java.io.IOException
- Throws:
java.io.IOException
-
readHeader
private void readHeader() throws java.io.IOException
- Throws:
java.io.IOException
-
isSimpleColumnName
private static boolean isSimpleColumnName(java.lang.String columnName)
-
pushBack
private void pushBack()
-
readChar
private int readChar() throws java.io.IOException
- Throws:
java.io.IOException
-
readBuffer
private int readBuffer() throws java.io.IOException
- Throws:
java.io.IOException
-
readValue
private java.lang.String readValue() throws java.io.IOException
- Throws:
java.io.IOException
-
readNull
private java.lang.String readNull(java.lang.String s)
-
unEscape
private java.lang.String unEscape(java.lang.String s)
-
readRow
public java.lang.Object[] readRow() throws java.sql.SQLException
INTERNAL- Specified by:
readRow
in interfaceSimpleRowSource
- Returns:
- the row or null
- Throws:
java.sql.SQLException
- on failure
-
convertException
private static java.sql.SQLException convertException(java.lang.String message, java.lang.Exception e)
-
close
public void close()
INTERNAL- Specified by:
close
in interfaceSimpleRowSource
-
reset
public void reset() throws java.sql.SQLException
INTERNAL- Specified by:
reset
in interfaceSimpleRowSource
- 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
-
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
-
isParam
private static boolean isParam(java.lang.String key, java.lang.String... values)
-
-