Examples of org.apache.commons.csv.CSVParser

org.apache.commons.csv.CSVParser
.ietf.org/html/rfc4180" target="_blank">RFC 4180 format.

To parse CSV input in a format like Excel, you write:
```
 CSVParser parser = CSVParser.parse(csvData, CSVFormat.EXCEL); for (CSVRecord csvRecord : parser) { ... } 
```
If the predefined formats don't match the format at hands, custom formats can be defined. More information about customising CSVFormats is available in {@link CSVFormat CSVFormat JavaDoc}.

Parsing into memory

If parsing record wise is not desired, the contents of the input can be read completely into memory.
```
 Reader in = new StringReader("a;b\nc;d"); CSVParser parser = new CSVParser(in, CSVFormat.EXCEL); List<CSVRecord> list = parser.getRecords(); 
```
There are two constraints that have to be kept in mind:
1. Parsing into memory starts at the current position of the parser. If you have already parsed records from the input, those records will not end up in the in memory representation of your CSV data.
2. Parsing into memory may consume a lot of system resources depending on the input. For example if you're parsing a 150MB file of CSV data the contents will be read completely into memory.
Notes

Internal parser state is completely covered by the format and the reader-state.
@version $Id: CSVParser.java 1617069 2014-08-10 08:53:42Z britter $ @see package documentation for more details

        }
    }


    @Test
    public void testCSVUpsertWithCustomDelimiters() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
                    + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
            conn = DriverManager.getConnection(getUrl()).unwrap(
                    PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
                    Arrays.<String> asList(STOCK_COLUMNS), true,
                    '1', '2', '3', CSVCommonsLoader.DEFAULT_ARRAY_ELEMENT_SEPARATOR);
            csvUtil.upsert(new StringReader(STOCK_CSV_VALUES_WITH_DELIMITER));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT SYMBOL, COMPANY FROM "
                            + STOCK_TABLE);
            ResultSet phoenixResultSet = statement.executeQuery();
            parser = new CSVParser(new StringReader(
                    STOCK_CSV_VALUES_WITH_DELIMITER), csvUtil.getFormat());
            for (CSVRecord record : parser) {
                assertTrue(phoenixResultSet.next());
                int i = 0;
                for (String value : record) {
                    assertEquals(value, phoenixResultSet.getString(i + 1));
                    i++;
                }
            }
            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVUpsertWithColumns() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
                    + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
                    Arrays.<String> asList(STOCK_COLUMNS), true);
            // no header
            csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT SYMBOL, COMPANY FROM "
                            + STOCK_TABLE);
            ResultSet phoenixResultSet = statement.executeQuery();
            parser = new CSVParser(new StringReader(
                    STOCK_CSV_VALUES), csvUtil.getFormat());
            for (CSVRecord record : parser) {
                assertTrue(phoenixResultSet.next());
                int i = 0;
                for (String value : record) {
                    assertEquals(value, phoenixResultSet.getString(i + 1));
                    i++;
                }
            }


            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVUpsertWithNoColumns() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
                    + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
                    null, true);
            csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT SYMBOL, COMPANY FROM "
                            + STOCK_TABLE);
            ResultSet phoenixResultSet = statement.executeQuery();
            parser = new CSVParser(new StringReader(
                    STOCK_CSV_VALUES), csvUtil.getFormat());
            for (CSVRecord record : parser) {
                assertTrue(phoenixResultSet.next());
                int i = 0;
                for (String value : record) {
                    assertEquals(value, phoenixResultSet.getString(i + 1));
                    i++;
                }
            }


            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVUpsertWithBogusColumn() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
                    + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file, not strict
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
                    Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), false);
            csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT SYMBOL, COMPANY FROM "
                            + STOCK_TABLE);
            ResultSet phoenixResultSet = statement.executeQuery();
            parser = new CSVParser(new StringReader(STOCK_CSV_VALUES),
                    csvUtil.getFormat());
            for (CSVRecord record : parser) {
                assertTrue(phoenixResultSet.next());
                assertEquals(record.get(0), phoenixResultSet.getString(1));
                assertNull(phoenixResultSet.getString(2));
            }


            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVUpsertWithAllColumn() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
                    + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
                    Arrays.asList("FOO", "BAR"), false);


            try {
                csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
                fail();
            } catch (SQLException e) {
                assertTrue(
                        e.getMessage(),
                        e.getMessage()
                                .contains(
                                        "ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.[FOO, BAR]"));
            }
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVUpsertWithBogusColumnStrict() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
                    + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
                    Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), true);
            try {
                csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
                fail();
            } catch (SQLException e) {
                assertTrue(
                        e.getMessage(),
                        e.getMessage()
                                .contains(
                                        "ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.BOGUS"));
            }
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testAllDatatypes() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS "
                    + DATATYPE_TABLE
                    + " (CKEY VARCHAR NOT NULL PRIMARY KEY,"
                    + "  CVARCHAR VARCHAR, CINTEGER INTEGER, CDECIMAL DECIMAL(31,10), CUNSIGNED_INT UNSIGNED_INT, CBOOLEAN BOOLEAN, CBIGINT BIGINT, CUNSIGNED_LONG UNSIGNED_LONG, CTIME TIME, CDATE DATE);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
                    DATATYPE_TABLE, Collections.<String> emptyList(), true);
            csvUtil.upsert(new StringReader(DATATYPES_CSV_VALUES));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT CKEY, CVARCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE FROM "
                            + DATATYPE_TABLE);
            ResultSet phoenixResultSet = statement.executeQuery();
            parser = new CSVParser(new StringReader(DATATYPES_CSV_VALUES),
                    csvUtil.getFormat());


            for (CSVRecord record : parser) {
                assertTrue(phoenixResultSet.next());
                int i = 0;
                int size = record.size();
                for (String value : record) {
                    assertEquals(value, phoenixResultSet.getObject(i + 1)
                            .toString().toUpperCase());
                    if (i < size - 2)
                        break;
                    i++;
                }
                // special case for matching date, time values
                assertEquals(DateUtil.parseTime(record.get(8)),
                        phoenixResultSet.getTime("CTIME"));
                assertEquals(DateUtil.parseDate(record.get(9)),
                        phoenixResultSet.getDate("CDATE"));
            }


            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVCommonsUpsertEncapsulatedControlChars() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {
            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS "
                    + ENCAPSULATED_CHARS_TABLE
                    + "(MYKEY VARCHAR NOT NULL PRIMARY KEY, MYVALUE VARCHAR);";
            conn = DriverManager.getConnection(getUrl())
                    .unwrap(PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
                    ENCAPSULATED_CHARS_TABLE, Collections.<String> emptyList(),
                    true);
            csvUtil.upsert(new StringReader(
                    CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT MYKEY, MYVALUE FROM "
                            + ENCAPSULATED_CHARS_TABLE);
            ResultSet phoenixResultSet = statement.executeQuery();
            parser = new CSVParser(new StringReader(
                    CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER),
                    csvUtil.getFormat());
            for (CSVRecord record : parser) {
                assertTrue(phoenixResultSet.next());
                int i = 0;
                for (String value : record) {
                    assertEquals(value, phoenixResultSet.getString(i + 1));
                    i++;
                }
            }


            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

      this.strategy = strategy;
      this.base = base;
    }


    void add(SolrInputDocument doc, int line, int column, String val) {
      CSVParser parser = new CSVParser(new StringReader(val), strategy);
      try {
        String[] vals = parser.getLine();
        if (vals!=null) {
          for (String v: vals) base.add(doc,line,column,v);
        } else {
          base.add(doc,line,column,val);
        }

View Full Code Here

        for (int i=0; i<skipLines; i++) {
          r.readLine();
        }
      }


      CSVParser parser = new CSVParser(reader, strategy);


      // parse the fieldnames from the header of the file
      if (fieldnames==null) {
        fieldnames = parser.getLine();
        if (fieldnames==null) {
          throw new SolrException( SolrException.ErrorCode.BAD_REQUEST,"Expected fieldnames in CSV input");
        }
        prepareFields();
      }


      // read the rest of the CSV file
      for(;;) {
        int line = parser.getLineNumber();  // for error reporting in MT mode
        String[] vals = parser.getLine();
        if (vals==null) break;


        if (vals.length != fields.length) {
          input_err("expected "+fields.length+" values but got "+vals.length, vals, line);
        }

View Full Code Here

0 1 2 3 4 5 6 7

TOP

Related Classes of org.apache.commons.csv.CSVParser

Chapter05

com.netflix.astyanax.util.CsvColumnReader

com.netflix.astyanax.util.CsvRecordReader

de.apaxo.bedcon.FacebookRecommender

name.abuchen.portfolio.datatransfer.CSVImporter

net.sf.uadetector.DeviceCategoryExamplesReader

net.sf.uadetector.OperatingSystemExamplesReader

net.sf.uadetector.OperatingSystemSampleReader

net.sf.uadetector.UserAgentExamplesReader

org.apache.any23.extractor.csv.CSVReaderBuilder

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.

Examples of org.apache.commons.csv.CSVParser

Parsing into memory

Notes

Related Classes of org.apache.commons.csv.CSVParser