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

    }


    @Test
    public void testCSVCommonsUpsertBadEncapsulatedControlChars()
            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);
            try {
                csvUtil.upsert(new StringReader(
                        CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER));
                fail();
            } catch (RuntimeException e) {
                assertTrue(
                        e.getMessage(),
                        e.getMessage()
                                .contains(
                                        "invalid char between encapsulated token and delimiter"));
            }
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

        }
    }


    @Test
    public void testCSVCommonsUpsert_WithArray() throws Exception {
        CSVParser parser = null;
        PhoenixConnection conn = null;
        try {


            // Create table
            String statements = "CREATE TABLE IF NOT EXISTS ARRAY_TABLE "
                    + "(ID BIGINT NOT NULL PRIMARY KEY, VALARRAY INTEGER ARRAY);";
            conn = DriverManager.getConnection(getUrl()).unwrap(
                    PhoenixConnection.class);
            PhoenixRuntime.executeStatements(conn,
                    new StringReader(statements), null);


            // Upsert CSV file
            CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, "ARRAY_TABLE",
                    Collections.<String> emptyList(), true, null, "!");
            csvUtil.upsert(
                    new StringReader("ID,VALARRAY\n"
                            + "1,2!3!4\n"));


            // Compare Phoenix ResultSet with CSV file content
            PreparedStatement statement = conn
                    .prepareStatement("SELECT ID, VALARRAY FROM ARRAY_TABLE");
            ResultSet phoenixResultSet = statement.executeQuery();
            assertTrue(phoenixResultSet.next());
            assertEquals(1L, phoenixResultSet.getLong(1));
            assertEquals(
                    PArrayDataType.instantiatePhoenixArray(PDataType.INTEGER, new Integer[]{2, 3, 4}),
                    phoenixResultSet.getArray(2));
            assertFalse(phoenixResultSet.next());
        } finally {
            if (parser != null)
                parser.close();
            if (conn != null)
                conn.close();
        }
    }

View Full Code Here

      // a certain person
      Map<Long,List<Preference>> preferecesOfUsers = new HashMap<Long,List<Preference>>();
      
      // use a CSV parser for reading the file
      // use UTF-8 as character set
      CSVParser parser = new CSVParser(new InputStreamReader(new FileInputStream(data), "UTF-8"));
      
      // parse out the header
      // we are not using the header
      String[] header = parser.getLine();
      
      // should output person name
      log.fine(header[0]+" "+header[1]);
      
      
      String[] line;
      
      // go through every line
      while((line = parser.getLine()) != null) {
      
        String person = line[0];
        String likeName = line[1];
        
        // other lines contained but not used

View Full Code Here


    public void importIpsToRedis(Jedis conn, File file) {
        FileReader reader = null;
        try{
            reader = new FileReader(file);
            CSVParser parser = new CSVParser(reader);
            int count = 0;
            String[] line = null;
            while ((line = parser.getLine()) != null){
                String startIp = line.length > 1 ? line[0] : "";
                if (startIp.toLowerCase().indexOf('i') != -1){
                    continue;
                }
                int score = 0;

View Full Code Here

    public void importCitiesToRedis(Jedis conn, File file) {
        Gson gson = new Gson();
        FileReader reader = null;
        try{
            reader = new FileReader(file);
            CSVParser parser = new CSVParser(reader);
            String[] line = null;
            while ((line = parser.getLine()) != null){
                if (line.length < 4 || !Character.isDigit(line[0].charAt(0))){
                    continue;
                }
                String cityId = line[0];
                String country = line[1];

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