File Time of multiple folders

[Karim_Khan]

Hi Guys,

Hope you are doing well.

I was trying to get the file date time from multiple folders using below code but seems like it deosn't work when i swicth between QAS and UAT enviornments whereas am getting correct database date time while switching between the enviornments.

IF '$(v.Environment)' = 'QAS' THEN

LET v.ExtractFolder = '\\....QAS\Inputs\KK\';

// QAS CONNECTION STRING
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User I....);

ELSEIF '$(v.Environment)' = 'UAT' THEN

LET v.ExtractFolder = '\\......\UAT\Inputs\KK\';

// UAT CONNECTION STRING
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User I......);

ENDIF;

EXCEL_FILES:
LOAD * INLINE [
EXCEL_FILE_NAME, ID
DUMMY_MASTER, 1
DUMMY_MASTER_KK, 2];

KK_TABLES:
LOAD * INLINE [
KK_TBL_NAME, ID
DUMMY_MASTER, 1
DUMMY_MASTER_KK, 2];

For Each ExtractFile in 'DUMMY_MASTER.xlsx','DUMMY_MASTER_KK.xlsx', 
DATA_EXCELS:
LOAD
FileBaseName() AS EXCEL_FILE_NAME,
COUNT(RowNo()) AS NUMBER_OF_RECORDS_IN_EXCEL,
filetime() AS EXCEL_FILE_DATE

FROM
[$(v.ExtractFolder)$(ExtractFile)]
(ooxml, embedded labels, table is DATA);

TEMP_DATA_TABLES:
SQL

SELECT TABLE_NAME AS KK_TBL_NAME,
TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM DUMMY'||TABLE_NAME)),'/ROWSET/ROW/C')) AS NUMBER_OF_RECORDS_IN_TABLE,
TO_CHAR(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT MAX(ROW_INSERT_DT) C FROM DUMMY'||TABLE_NAME)),'/ROWSET/ROW/C')) AS MAX_INSERT_DATE
FROM ALL_TABLES
WHERE OWNER = 'DUMMY_KK'
AND TABLE_NAME LIKE '%KK%';

Regards,

KK

[Karim_Khan]

Hi Guys,

Hope you are doing well.

I was trying to get the file date time from multiple folders using below code but seems like it deosn't work when i swicth between QAS and UAT enviornments whereas am getting correct database date time while switching between the enviornments.

IF '$(v.Environment)' = 'QAS' THEN

LET v.ExtractFolder = '\\....QAS\Inputs\KK\';

// QAS CONNECTION STRING
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User I....);

ELSEIF '$(v.Environment)' = 'UAT' THEN

LET v.ExtractFolder = '\\......\UAT\Inputs\KK\';

// UAT CONNECTION STRING
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User I......);

ENDIF;

EXCEL_FILES:
LOAD * INLINE [
EXCEL_FILE_NAME, ID
DUMMY_MASTER, 1
DUMMY_MASTER_KK, 2];

KK_TABLES:
LOAD * INLINE [
KK_TBL_NAME, ID
DUMMY_MASTER, 1
DUMMY_MASTER_KK, 2];

For Each ExtractFile in 'DUMMY_MASTER.xlsx','DUMMY_MASTER_KK.xlsx', 
DATA_EXCELS:
LOAD
FileBaseName() AS EXCEL_FILE_NAME,
COUNT(RowNo()) AS NUMBER_OF_RECORDS_IN_EXCEL,
filetime() AS EXCEL_FILE_DATE

FROM
[$(v.ExtractFolder)$(ExtractFile)]
(ooxml, embedded labels, table is DATA);

TEMP_DATA_TABLES:
SQL

SELECT TABLE_NAME AS KK_TBL_NAME,
TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM DUMMY'||TABLE_NAME)),'/ROWSET/ROW/C')) AS NUMBER_OF_RECORDS_IN_TABLE,
TO_CHAR(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT MAX(ROW_INSERT_DT) C FROM DUMMY'||TABLE_NAME)),'/ROWSET/ROW/C')) AS MAX_INSERT_DATE
FROM ALL_TABLES
WHERE OWNER = 'DUMMY_KK'
AND TABLE_NAME LIKE '%KK%';

Regards,

KK