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:org.wso2.carbon.event.simulator.csvFeedSimulation.core.CSVFeedEventSimulator.java

/**
 * This method must be called within a synchronized block to avoid multiple file simulators from running simultaneously.
 * Read the values from uploaded CSV file and convert those values into event and send those events to
 * input handler//from   w  w  w.java 2s  .co  m
 * <p>
 * <p>
 * To read the CSV file It uses CSV parser Library.
 * {@link <a href="https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVParser.html">CSVParser</a>}
 * </p>
 * <p>
 * <p>
 * CSV file can be separated by one of these fallowing character , , ; , \t by default
 * It has capability to have user defined delimiter
 * Any field may be quoted (with double quotes)
 * Fields with embedded commas or delimiter characters must be double quoted.
 * </p>
 * <p>
 * Initialize CSVParser
 *
 * @param executionPlanDto ExecutionPlanDto
 * @param csvFileConfig    CSVFileSimulationDto
 */
private void sendEvent(ExecutionPlanDto executionPlanDto, CSVFileSimulationDto csvFileConfig) {

    /*
      return no of events read from CSV file during ever iteration
     */
    long noOfEvents = 0;
    int delay = csvFileConfig.getDelay();
    /*
    Reader for reading character streams from file
     */
    Reader in = null;
    /*
    CSVParser to read CSV Values
     */
    CSVParser csvParser = null;
    if (delay <= 0) {
        log.warn("Events will be sent continuously since the delay between events are set to " + delay
                + "milliseconds");
        delay = 0;
    }

    try {
        /*
        Initialize Reader
         */
        in = new FileReader(String.valueOf(Paths.get(System.getProperty("java.io.tmpdir"),
                csvFileConfig.getFileDto().getFileInfo().getFileName())));

        /*
        Initialize CSVParser with appropriate CSVFormat according to delimiter
         */

        switch (csvFileConfig.getDelimiter()) {
        case ",":
            csvParser = CSVParser.parse(in, CSVFormat.DEFAULT);
            break;
        case ";":
            csvParser = CSVParser.parse(in, CSVFormat.EXCEL);
            break;
        case "\\t":
            csvParser = CSVParser.parse(in, CSVFormat.TDF);
            break;
        default:
            csvParser = CSVParser.parse(in, CSVFormat.newFormat(csvFileConfig.getDelimiter().charAt(0)));
        }

        int attributeSize = executionPlanDto.getInputStreamDtoMap().get(csvFileConfig.getStreamName())
                .getStreamAttributeDtos().size();

        /*
        Iterate through the CSV file line by line
         */

        for (CSVRecord record : csvParser) {
            try {
                synchronized (this) {
                    if (isStopped) {
                        isStopped = false;
                        break;
                    }
                    if (isPaused) {
                        this.wait();
                    }
                }

                if (record.size() != attributeSize) {
                    log.warn("No of attribute is not equal to attribute size: " + attributeSize + " is needed"
                            + "in Row no:" + noOfEvents + 1);
                }
                String[] attributes = new String[attributeSize];
                noOfEvents = csvParser.getCurrentLineNumber();

                for (int i = 0; i < record.size(); i++) {
                    attributes[i] = record.get(i);
                }

                //convert Attribute values into event
                Event event = EventConverter.eventConverter(csvFileConfig.getStreamName(), attributes,
                        executionPlanDto);
                // TODO: 13/12/16 delete sout
                System.out.println("Input Event " + Arrays.deepToString(event.getEventData()));
                //

                //send the event to input handler
                send(csvFileConfig.getStreamName(), event);

                //delay between two events
                if (delay > 0) {
                    Thread.sleep(delay);
                }
            } catch (EventSimulationException e) {
                log.error("Event dropped due to Error occurred during generating an event" + e.getMessage());
            } catch (InterruptedException e) {
                log.error("Error occurred during send event" + e.getMessage());
            }
        }

    } catch (IllegalArgumentException e) {
        // TODO: 02/12/16 proper error message
        throw new EventSimulationException("File Parameters are null" + e.getMessage());
    } catch (FileNotFoundException e) {
        throw new EventSimulationException(
                "File not found :" + csvFileConfig.getFileDto().getFileInfo().getFileName());
    } catch (IOException e) {
        throw new EventSimulationException("Error occurred while reading the file");
    } finally {
        try {
            if (in != null && csvParser != null)
                in.close();
            csvParser.close();
        } catch (IOException e) {
            throw new EventSimulationException("Error occurred during closing the file");
        }
    }
}

---

From source file:parser_owl.ParserOwl.java

public static void startParisWithData(String inputData) {
    try {/*w w w.ja va  2s  . c  o m*/
        File csvData = new File("/Users/gautier/Documents/web_sem/sources/paris_tournages2.csv");
        CSVParser parser = CSVParser.parse(csvData, Charset.defaultCharset(), CSVFormat.EXCEL.withHeader());
        System.out.println("Starting Paris (input file: " + csvData.getAbsolutePath() + ")...");
        for (CSVRecord csvRecord : parser) {
            Film film = new Film(csvRecord.get("titre"), csvRecord.get("realisateur"),
                    csvRecord.get("date_debut_evenement"), csvRecord.get("date_fin_evenement"),
                    csvRecord.get("cadre"), csvRecord.get("lieu"), csvRecord.get("adresse"),
                    csvRecord.get("arrondissement"), csvRecord.get("adresse_complete"),
                    csvRecord.get("geo_coordinates"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("imdbRating"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Runtime"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Country"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Actors"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Year"));
            MANAGER.store(film);
        }
        System.out.println("Ending Paris.");
    } catch (IOException e) {
        System.out.println("error: " + e);
    }
}

---

From source file:parser_owl.ParserOwl.java

public static void startMontpellierWithData(String inputData) {
    try {//from   ww  w .jav  a2s.  co m
        System.out.println("Starting Montpellier (input file: " + inputData + ")...");
        File csvData = new File(inputData);
        CSVParser parser = CSVParser.parse(csvData, Charset.defaultCharset(), CSVFormat.EXCEL.withHeader());
        for (CSVRecord csvRecord : parser) {
            Film film = new Film(csvRecord.get("titre"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Director"),
                    csvRecord.get("date_debut_evenement"), csvRecord.get("date_fin_evenement"), "",
                    csvRecord.get("lieu"), csvRecord.get("lieu"), "", csvRecord.get("lieu"), "",
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("imdbRating"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Runtime"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Country"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Actors"),
                    OMDB_PROXY.getMovieInfos(csvRecord.get("titre")).get("Year"));
            MANAGER.store(film);
        }
        System.out.println("Ending Montpellier.");
    } catch (IOException e) {
        System.out.println("error: " + e);
    }
}

---

From source file:pl.hycom.pip.messanger.service.GreetingService.java

@Override
public void afterPropertiesSet() throws Exception {
    try (Reader in = new InputStreamReader(getClass().getResourceAsStream("/messenger-locale.csv"))) {
        Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
        for (CSVRecord record : records) {
            availableLocale.put(record.get(0), record.get(1));
        }// w  w w  .  jav a2s. c o  m
    }

    log.info("Locale loaded: " + availableLocale);
}

---

From source file:ru.sbt.bit.ood.hw1.parse.CSVRecordParser.java

public Iterable<Trade> parse(String fileName) {
    try {/*from  w  ww  . jav  a  2s.c  o m*/
        Reader in = new FileReader(fileName);
        Collection<Trade> trades = new ArrayList<>();
        CSVFormat.EXCEL.parse(in).forEach((csvRecord) -> trades.add(new Trade(csvRecord.toMap())));
        return trades;
    } catch (IOException e) {
        e.printStackTrace();
        throw new RuntimeException("There was an error while parsing CSV file");
    }
}

---

From source file:sg.edu.ntu.hrms.service.EmployeeEditService.java

public void uploadEmp(List<FileItem> fields) throws Exception {

    Session session = sessionFactory.openSession();
    Transaction txn = session.beginTransaction();
    try {//from ww  w .  j  ava2s  .  co  m
        userDAO.setSession(session);
        leaveDAO.setSession(session);
        accessDAO.setSession(session);
        deptDAO.setSession(session);
        titleDAO.setSession(session);

        Iterator<FileItem> it = fields.iterator();
        while (it.hasNext()) {
            FileItem fileItem = it.next();
            //store in webserver.
            String fileName = fileItem.getName();
            if (fileName != null) {
                File file = new File(fileName);
                fileItem.write(file);
                System.out.println("File successfully saved as " + file.getAbsolutePath());

                //process file
                Reader in = new FileReader(file.getAbsolutePath());
                Iterable<CSVRecord> records = CSVFormat.EXCEL.withHeader().parse(in);
                for (CSVRecord record : records) {
                    String name = record.get("<name>");
                    System.out.println("name: " + name);
                    String login = record.get("<login>");
                    String title = record.get("<title>");
                    String email = record.get("<email>");
                    String role = record.get("<role>");
                    String dept = record.get("<department>");
                    String joinDate = record.get("<joinDate>");
                    String probDate = record.get("<probDate>");
                    String annLeaveEnt = record.get("<leave_entitlement>");
                    String annBal = record.get("<leave_bal>");
                    String annMax = record.get("<leave_max>");
                    String annCF = record.get("<leave_cf>");
                    String med = record.get("<med_taken>");
                    String oil = record.get("<oil_taken>");
                    String unpaid = record.get("<unpaid_taken>");
                    String child = record.get("<child_bal>");

                    TitleDTO titleDto = titleDAO.getTitleByName(title);
                    RoleDTO roleDto = accessDAO.getRole(role);
                    DeptDTO deptDto = deptDAO.getDepartment(dept);
                    //create the user first
                    UserDTO user = new UserDTO();
                    user.setName(name);
                    user.setLogin(login);
                    user.setTitle(titleDto);
                    user.setEmail(email);
                    user.setDateJoin(Utility.format(joinDate, "dd/MM/yyyy"));
                    user.setProbationDue(Utility.format(probDate, "dd/MM/yyyy"));
                    //store in user table.
                    userDAO.createUser(user);
                    //assign role
                    userDAO.assignRole(user, roleDto);
                    //assign dept
                    deptDAO.assignEmployee(user, deptDto);

                    //leave ent
                    LeaveTypeDTO lvtypeDTO = leaveDAO.getLeaveType("Annual");
                    LeaveEntDTO annualentDTO = new LeaveEntDTO();
                    annualentDTO.setCurrent(Double.parseDouble(annLeaveEnt));
                    annualentDTO.setBalance(Double.parseDouble(annBal));
                    annualentDTO.setMax(Double.parseDouble(annMax));
                    annualentDTO.setCarriedOver(Double.parseDouble(annCF));
                    annualentDTO.setLeaveType(lvtypeDTO);
                    //assign annual leave
                    annualentDTO.setUser(user);
                    leaveDAO.addLeaveEnt(annualentDTO);
                    //medical ent
                    LeaveTypeDTO medTypeDTO = leaveDAO.getLeaveType("Medical Leave");
                    LeaveEntDTO medentDTO = new LeaveEntDTO();
                    medentDTO.setBalance(medTypeDTO.getDays() - Double.parseDouble(med));
                    medentDTO.setCurrent(medTypeDTO.getDays());
                    medentDTO.setUser(user);
                    medentDTO.setLeaveType(medTypeDTO);
                    leaveDAO.addLeaveEnt(medentDTO);
                    //oil ent
                    LeaveTypeDTO oilTypeDTO = leaveDAO.getLeaveType("Off-in-Lieu");
                    LeaveEntDTO oilentDTO = new LeaveEntDTO();
                    oilentDTO.setBalance(oilTypeDTO.getDays() - Double.parseDouble(oil));
                    oilentDTO.setCurrent(0);
                    oilentDTO.setUser(user);
                    oilentDTO.setLeaveType(oilTypeDTO);
                    leaveDAO.addLeaveEnt(oilentDTO);
                    //unpaid
                    LeaveTypeDTO unpaidTypeDTO = leaveDAO.getLeaveType("Unpaid");
                    LeaveEntDTO unpaidentDTO = new LeaveEntDTO();
                    unpaidentDTO.setBalance(unpaidTypeDTO.getDays() - Double.parseDouble(unpaid));
                    unpaidentDTO.setCurrent(0);
                    unpaidentDTO.setUser(user);
                    unpaidentDTO.setLeaveType(unpaidTypeDTO);
                    leaveDAO.addLeaveEnt(unpaidentDTO);
                    //child
                    LeaveTypeDTO childTypeDTO = leaveDAO.getLeaveType("Child Care");
                    double cur = childTypeDTO.getDays();
                    LeaveEntDTO childentDTO = new LeaveEntDTO();
                    childentDTO.setBalance(cur - Double.parseDouble(child));
                    childentDTO.setCurrent(cur);
                    childentDTO.setUser(user);
                    childentDTO.setLeaveType(childTypeDTO);
                    leaveDAO.addLeaveEnt(childentDTO);

                    //txn.commit();
                }

            }

        }
        txn.commit();
    } catch (Exception ex) {
        txn.rollback();
        ex.printStackTrace();
    } finally {
        session.close();
    }
}

---

From source file:StatisticLearningProject.DataGrid.java

public DataGrid(File f) {
    Reader in = null;//w ww  .  ja v a2  s .  co  m

    try {
        in = new FileReader(f.getAbsolutePath());
    } catch (FileNotFoundException e) {
        loadError = e.getMessage();
        return;
    }

    try {
        ArrayList<CSVRecord> records = Lists.newArrayList(CSVFormat.EXCEL.parse(in));
        CSVRecord headerRec = records.remove(0);
        int columns = headerRec.size();
        String[] headers = new String[columns];
        for (int i = 0; i < columns; i++) {
            headers[i] = (String) headerRec.get(i);
        }
        int rows = records.size();
        Object[][] data = new Object[rows][columns];
        int count = 0;
        for (CSVRecord record : records) {
            for (int i = 0; i < columns; i++) {
                data[count][i] = record.get(i);
            }
            count++;
        }
        tableModel = new DefaultTableModel(data, headers);
    } catch (IOException e) {
        loadError = e.getMessage();

    }
}

---

From source file:stonelake.fleet.FleetDataParser.java

public static void main(String[] args) {
    try {/*from ww w  .  j av  a2 s  .c  o  m*/
        DataSource dataSource = new SimpleDataSource("org.hsqldb.jdbc.JDBCDriver",
                "jdbc:hsqldb:hsql://localhost/purchasedb", "SA", null);

        Reader in = new FileReader(
                "C:/Users/localadmin/ca-project/GreenProject/data/CA_State_Fleet___2011-2014_.csv");
        Iterable<CSVRecord> records = CSVFormat.EXCEL.withHeader().parse(in);

        //Clear table before re-populating
        JdbcHelper jdbc = new JdbcHelper(dataSource);

        //Clear the table 
        /**************88
        try {
           jdbc.holdConnection();
           jdbc.execute("delete from ca_state_fleet");
        } finally {
           jdbc.releaseConnection();
        }
        **************/
        //Insert new records 
        for (CSVRecord record : records) {
            //Generate record ID
            String recordId = UUID.randomUUID().toString();

            //Add data elements to database

            jdbc = new JdbcHelper(dataSource);
            //Insert new record         
            try {
                jdbc.holdConnection();

                jdbc.execute(
                        "insert into ca_state_fleet (record_id,agency,report_year,disposed,equipment_number,category,model_year ,make_model,vin,license_plate_number,postal_code,vehicle_type_desc,weight_class,passenger_vehicle,payload_rating,shipping_weight,wheel_type,tire_size,fuel_type,engine_configuration,emissions_type,primary_application,secondary_application,acquisition_delivery_date,suv_justification,id4x4_justification,acquisition_method_reason,purchase_price,annul_lease_rate,acquistion_mileage,disposition_date,transferred_to,disposition_method,disposition_reason,disposition_mileage,disposition_sold_amount,fuel_total_fuel_average,total_miles,dec_prior_year ) values"
                                + " (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?)",
                        recordId, record.get("Agency"), record.get("Report Year"), record.get("Disposed"),
                        record.get("Equipment Number"), record.get("Category"), record.get("Model Year "),
                        record.get("Make Model"), record.get("VIN"), record.get("License Plate Number"),
                        record.get("Postal Code"), record.get("Vehicle Type Desc"), record.get("Weight Class"),
                        record.get("Passenger Vehicle On Off Road Owned Leased"), record.get("Payload Rating"),
                        record.get("Shipping Weight"), record.get("Wheel Type"), record.get("Tire Size"),
                        record.get("Fuel Type"), record.get("Engine Configuration"),
                        record.get("Emissions Type"), record.get("Primary Application"),
                        record.get("Secondary Application"), record.get("Acquisition Delivery Date"),
                        record.get("SUV Justification"), record.get("ID4X4 Justification"),
                        record.get("Acquisition Method Reason"), record.get("Purchase Price"),
                        record.get("Annul Lease Rate"), record.get("Acquistion Mileage"),
                        record.get("Disposition Date"), record.get("Transferred To"),
                        record.get("Disposition Method"), record.get("Disposition Reason"),
                        record.get("Disposition Mileage"), record.get("Disposition Sold Amount"),
                        record.get("Fuel Total Fuel Average"), record.get("Total Miles"),
                        record.get("Dec Prior Year "));

            } finally {
                jdbc.releaseConnection();
            }
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

---

From source file:su90.etl.controller.SlaveController.java

private void _do_imps() {
    Reader in = null;/*  w  w w  .  j  a va  2  s  . c om*/
    try {
        in = new FileReader(INPUTPATHSTR + System.getProperty("file.separator") + "facts"
                + System.getProperty("file.separator") + "imps" + System.getProperty("file.separator")
                + FILENAME);
        Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
        for (CSVRecord record : records) {
            Result tmpresult = parse_imps(record.iterator());
            if (results.containsKey(tmpresult.hashCode())) {
                Result previousresult = results.get(tmpresult.hashCode());
                previousresult.setImps(previousresult.getImps() + tmpresult.getImps());
                previousresult.addOneDeviceType(tmpresult.getDevice_type());
                results.put(previousresult.hashCode(), previousresult);
            } else {
                results.put(tmpresult.hashCode(), tmpresult);
            }
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(SlaveController.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(SlaveController.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (in != null)
            try {
                in.close();
            } catch (IOException ex) {
                Logger.getLogger(SlaveController.class.getName()).log(Level.SEVERE, null, ex);
            }
    }
}

---

From source file:su90.etl.controller.SlaveController.java

private void _do_clicks() {
    Reader in = null;//  w ww. j av  a  2s  .co m
    try {
        in = new FileReader(INPUTPATHSTR + System.getProperty("file.separator") + "facts"
                + System.getProperty("file.separator") + "clicks" + System.getProperty("file.separator")
                + FILENAME);
        Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
        for (CSVRecord record : records) {
            Result tmpresult = parse_clicks(record.iterator());
            if (results.containsKey(tmpresult.hashCode())) {
                Result previousresult = results.get(tmpresult.hashCode());
                previousresult.setClicks(previousresult.getClicks() + tmpresult.getClicks());
                results.put(previousresult.hashCode(), previousresult);
            } else {
                results.put(tmpresult.hashCode(), tmpresult);
            }
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(SlaveController.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(SlaveController.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (in != null)
            try {
                in.close();
            } catch (IOException ex) {
                Logger.getLogger(SlaveController.class.getName()).log(Level.SEVERE, null, ex);
            }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• »