CODEWITHSUNDEEP
javamysqlsqlload-data-infile
gtiwari333
gtiwari333

Reputation: 25146

MySQL - load data infile - how to read and insert line number into table?

I am using MySQL's LOAD DATA LOCAL INFILE feature to load lines of record from text file into table. Everything works fine but I am wondering how can I insert line number from the text file into LINE_NUMBER column of my table?

public String getLoadDataSQL() throws Exception {

    StringBuffer sb = new StringBuffer();
    sb.append( "LOAD DATA LOCAL INFILE '"+ filePath +"' INTO TABLE "+ this.tableName );
    sb.append( " LINES TERMINATED BY '\\n'");
    sb.append( " (@line) ");
    sb.append( " SET ROLL_NUMBER = substr(@line,1,9),  ");
    sb.append( " LAST_NAME = substr(@line,10,30),  ");
    ....
    sb.append( " LINE_NUMBER = "+ ????? +",  "); // WHAT TO DO HERE??
    ...
    System.out.println(sb.toString());
    return sb.toString();
}

Could you please suggest me how to do this?

Upvotes: 0

Views: 865

Answers (1)

Sujay
Sujay

Reputation: 6783

Not so sure if you can do that directly when you are loading data from a file. One hack would be to keep your LINE_NUMBER as an AUTO_INCREMENT column.

Upvotes: 1

Related Questions