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

Question

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

}

I was hoping to do something similar to this [https://stackoverflow.com/questions/45021501/scanner-and-pipe-delimited-file-java] — NormX, Mar 13 '20 at 17:17
Did you already ask this question [here](https://stackoverflow.com/questions/60673468/insert-parsed-pipe-delimited-data-into-h2-using-java#comment107348164_60673468), today? This looks like a duplicate. Let me know if I am missing something. — andrewJames, Mar 13 '20 at 18:31
@andrewjames I thought I deleted the previous question because it was written poorly — NormX, Mar 13 '20 at 18:39
OK - understood. For the DB part: You can use JDBC for this. There are many JDBC tutorials available online, for example [here](https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html). Also [here](http://www.h2database.com/html/tutorial.html#connecting_using_jdbc) for H2 notes. I have no doubt that Spring also has ways of making this easier for you (I don't use Spring). If you run into a specific problem you can always ask a specific question (assuming it has not been asked and answered already). — andrewJames, Mar 13 '20 at 18:44

score 0 · Answer 1 · answered Mar 25 '20 at 17:57

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);
    }
}

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

1 Answers1