Example usage for org.apache.commons.csv CSVFormat EXCEL

Introduction

In this page you can find the example usage for org.apache.commons.csv CSVFormat EXCEL.

Prototype

CSVFormat EXCEL

To view the source code for org.apache.commons.csv CSVFormat EXCEL.

Click Source Link

Document

Excel file format (using a comma as the value delimiter).

Usage

From source file:uk.trainwatch.osgb.codepoint.util.CodePointImport.java

private void importCode(Connection con, String table, Function<CSVRecord, String> code,
        Function<CSVRecord, String> name, File file) throws SQLException {

    try (CSVParser p = new CSVParser(new FileReader(file), CSVFormat.EXCEL)) {
        LOG.log(Level.INFO, () -> "Clearing down " + table);

        SQL.deleteIdTable(con, SCHEMA, table);
        try (Statement s = con.createStatement()) {
            s.executeUpdate("INSERT INTO " + SCHEMA + "." + table + " VALUES (0,'','')");
        }//from   ww w.j  av  a2 s  .  co m

        try (PreparedStatement ps = con
                .prepareStatement("INSERT INTO " + SCHEMA + "." + table + " (code,name) VALUES(?,?)")) {

            p.getRecords().stream().collect(Collectors.toMap(code::apply, name::apply, (a, b) -> a))
                    .forEach(SQLBiConsumer.guard((c, n) -> SQL.executeUpdate(ps, c, n)));

            con.commit();

            LOG.log(Level.INFO, () -> "Imported " + p.getRecordNumber() + " entries into code table");
        }
    } catch (SQLException ex) {
        con.rollback();
        throw new UncheckedSQLException(ex);
    } catch (IOException ex) {
        throw new UncheckedIOException(ex);
    }
}

From source file:us.parr.animl.data.DataTable.java

public static DataTable loadCSV(String fileName, String formatType, VariableType[] colTypesOverride,
        String[] colNamesOverride, boolean hasHeaderRow) {
    try {//from  ww  w .j a va 2 s  .com
        // use apache commons io + csv to load but convert to list of String[]
        // byte-order markers are handled if present at start of file.
        FileInputStream fis = new FileInputStream(fileName);
        final Reader reader = new InputStreamReader(new BOMInputStream(fis), "UTF-8");
        CSVFormat format;
        if (formatType == null) {
            format = hasHeaderRow ? CSVFormat.RFC4180.withHeader() : CSVFormat.RFC4180;
        } else {
            switch (formatType.toLowerCase()) {
            case "tsv":
                format = hasHeaderRow ? CSVFormat.TDF.withHeader() : CSVFormat.TDF;
                break;
            case "mysql":
                format = hasHeaderRow ? CSVFormat.MYSQL.withHeader() : CSVFormat.MYSQL;
                break;
            case "excel":
                format = hasHeaderRow ? CSVFormat.EXCEL.withHeader() : CSVFormat.EXCEL;
                break;
            case "rfc4180":
            default:
                format = hasHeaderRow ? CSVFormat.RFC4180.withHeader() : CSVFormat.RFC4180;
                break;
            }
        }
        final CSVParser parser = new CSVParser(reader, format);
        List<String[]> rows = new ArrayList<>();
        int numHeaderNames = parser.getHeaderMap().size();
        try {
            for (final CSVRecord record : parser) {
                String[] row = new String[record.size()];
                for (int j = 0; j < record.size(); j++) {
                    row[j] = record.get(j);
                }
                rows.add(row);
            }
        } finally {
            parser.close();
            reader.close();
        }

        VariableType[] actualTypes = computeColTypes(rows, numHeaderNames);

        Set<String> colNameSet = parser.getHeaderMap().keySet();
        String[] colNames = colNameSet.toArray(new String[colNameSet.size()]);
        if (colNamesOverride != null) {
            colNames = colNamesOverride;
        }
        if (colTypesOverride != null) {
            actualTypes = colTypesOverride;
        }
        return fromStrings(rows, actualTypes, colNames, false);
    } catch (Exception e) {
        throw new IllegalArgumentException("Can't open and/or read " + fileName, e);
    }
}