Package org.h2.tools

Class 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 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

    • Constructor Detail

      • Csv

        public Csv()

    • 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 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

      • 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 interface SimpleRowSource
        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)

      • 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

      • 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)