CODEWITHSUNDEEP
springparsingjdbccsvfile.readalllines
NormX
NormX

Reputation: 125

Parsing Pipe delimited file and Storing data into DB using Spring/Java

I have a pipe delimited file (excel xlsx) that I need to parse for certain data. the data is all in column A. the first row has the date, the last row has the row count, and everything in between is the row data. I want to take the first three fields of each row and the date from the header and store it into my H2 Table. There is extra data in my file in each row. I Need help creating code that will parse the file and insert it into my db. I have a Entity and some code written but am stuck now.

My file 

20200310|
Mn1223|w01192|windows|extra|extra|extra||
Sd1223|w02390|linux|extra|extra|extra||
2

My table 

DROP TABLE IF EXISTS Xy_load ;

CREATE TABLE Xy_load (
  account_name VARCHAR(250) NOT NULL,
  command_name VARCHAR(250) NOT NULL,
  system_name VARCHAR (250) NOT NULL,
  CREATE_DT date (8) DEFAULT NULL
);

entity class

public class ZyEntity {

    @Column(name="account_name")
    private String accountName;

    @Column(name="command_name")
    private String commandName;

    @Column(name="system_name")
    private String systemName;

    @Column(name="CREATE_DT")
    private int createDt;

    public ZyEntity(String accountName, String commandName, String systemName){
        this.accountName=accountName;
        this.commandName=commandName;
        this.systemName=systemName;

    }

    public String getAccountName() {
        return accountName;
    }

    public void setAccountName(String accountName) {
        this.accountName = accountName;
    }

    public String getCommandName() {
        return commandName;
    }

    public void setCommandName(String commandName) {
        this.commandName = commandName;
    }

    public String getSystemName() {
        return systemName;
    }

    public void setSystemName(String systemName) {
        this.systemName = systemName;
    }

    public int getCreateDt() {
        return createDt;
    }

    public void setCreateDt(int createDt) {
        this.createDt = createDt;
    }

}

Upvotes: 0

Views: 1561

Answers (1)

NormX
NormX

Reputation: 125

i was able to figure it out with some help 

List<DataToInsert> parseData(String filePath) throws IOException {

        List<String> lines = Files.readAllLines(Paths.get(filePath));

        // remove date and amount
        lines.remove(0);
        lines.remove(lines.size() - 1);

        return lines.stream()
                .map(s -> s.split("[|]")).map(val -> new DataToInsert(val[0], val[1], val[2])).collect(Collectors.toList());
    }


public void insertZyData(List<ZyEntity> parseData) {
    String sql = "INSERT INTO Landing.load (account_name,command_name,system_name)"+
            "VALUES (:account_name,:command_name,:system_name)";

    for (ZyEntity zyInfo : parseData){
        SqlParameterSource source = new MapSqlParameterSource("account_name", zInfo.getAccountName())
                .addValue("command_name", zyInfo.getCommandName())
                .addValue("system_name", zyInfo.getSystemName());
        jdbcTemplate.update(sql, source);
    }
}

Upvotes: 0

Related Questions