How about external tables feature? Its benefit is that you can write queries against it (and simply filter rows you're interested in). Drawback? You have to have access to database server; no problem - from my point of view, though; it's just that you can't run everything locally.
Here's a walkthrough:
As that feature requires access to directory (Oracle object that points to a filesystem directory), we'll have to create it first, connected as SYS:
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create directory ext_dir as 'c:\temp';
Directory created.
SQL> grant read, write on directory ext_dir to scott;
Grant succeeded.
Connect as scott (use which will be working on that problem):
SQL> connect scott/tiger
Connected.
Target table (I refuse to use reserved word - DATE - for column name. That's a bad practice, I suggest you avoid it):
SQL> desc tbl_interface
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
DATUM DATE
Data is stored in C:\TEMP\TEXT.TXT file; note that I'm running Oracle on my laptop which - therefore - acts as if it was a database server. If your database server isn't on your PC, you'll have to talk to DBA.
12345678JUAN CARLOS0 PEREZ0 20211029
23456789JUAN CARLOS1 PEREZ1 20201029
34567890JUAN CARLOS2 PEREZ2 20181029
45678901JUAN CARLOS3 PEREZ3 20171029
Just setting date format (you don't have to do that):
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
Let's, finally, create external table:
SQL> create table ext_table
2 (id number,
3 firstname varchar2(20),
4 lastname varchar2(20),
5 datum date
6 )
7 organization external
8 (type oracle_loader
9 default directory ext_dir
10 access parameters
11 (records delimited by newline
12 fields (id position(01:08) char(8),
13 firstname position(09:28) char(20),
14 lastname position(29:48) char(20),
15 datum date mask "yyyymmdd"
16 )
17 )
18 location('text.txt')
19 )
20 reject limit unlimited;
Table created.
Is there anything there?
SQL> select * From ext_table;
ID FIRSTNAME LASTNAME DATUM
---------- -------------------- -------------------- ----------
12345678 JUAN CARLOS0 PEREZ0 29.10.2021
23456789 JUAN CARLOS1 PEREZ1 29.10.2020
34567890 JUAN CARLOS2 PEREZ2 29.10.2018
45678901 JUAN CARLOS3 PEREZ3 29.10.2017
SQL>
Yes, there is - all rows from text.txt.
As we're using SQL, no problem in applying any filter to data, such as the one you wanted - to get the most recent data (today's):
SQL> select * From ext_table
2 where datum = trunc(sysdate);
ID FIRSTNAME LASTNAME DATUM
---------- -------------------- -------------------- ----------
12345678 JUAN CARLOS0 PEREZ0 29.10.2021
Obviously, now it is a simple matter of inserting desired values into the target table:
SQL> insert into tbl_interface (id, firstname, lastname, datum)
2 select id, firstname, lastname, datum
3 from ext_table
4 where datum = trunc(sysdate);
1 row created.
SQL> select * from tbl_interface;
ID FIRSTNAME LASTNAME DATUM
---------- -------------------- -------------------- ----------
12345678 JUAN CARLOS0 PEREZ0 29.10.2021
SQL>
