Hello,
Here is your corrected text:
I can do simple treatments in Pentaho, like reading a CSV file and filling the database, but I can't find the right method to manage the resumption of the calculation of an identifier between 2 import sessions.
In a Pentaho treatment, I need to perform the following operations:
Read the lines of a CSV file. Fill an Oracle database with the lines from the CSV. To fill the Oracle database, I must fill in, for each line of the CSV, a field whose data must be unique. So I need a counter composed as follows: "ID_" + a sequence of the alphabet (A, B, C, etc.) + 2 digits. Examples: ID_A01, ID_A02, ID_A03 … ID_A99, ID_B01, ID_B02.
To calculate the ID, I use this Javascript code:
var lastSeq = LAST_SEQ;
var newSeq = "";
if (lastSeq === null || lastSeq === "") {
newSeq = "ID_A01";
} else {
var regex = /ID_([A-Z])(\d\d)/;
var match = lastSeq.match(regex);
if (match) {
var letter = match[1];
var number = parseInt(match[2]);
number++;
if (number > 99) {
var newLetter = String.fromCharCode(letter.charCodeAt(0) + 1);
number = 1;
newSeq = "ID_" + newLetter + ("0" + number).slice(-2);
} else {
newSeq = "ID_" + letter + ("0" + number).slice(-2);
}
}
}
The sequence fills the IDFILE field in the Oracle FILE table.
Be careful, the sequence must continue between the different import sessions.
I can use this SQL query to find the previous value that was in FILE:
SELECT MAX(IDFILE) AS LAST_SEQ FROM FILE
WHERE IDFILE LIKE 'ID_%'
AND Rownum = 1
I have tried different approaches (like a call to a sub-transformation) to do this treatment, but for the moment, I get nothing good.
Thank you in advance for your advice.
------------------------------
Whaouu Whaouu
IT Support Manager
Whaouu
------------------------------