Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a directory with multiple files with the same name prefix. FieldGlass_History.xlsx, FieldGlass_0819.xlsx, FieldGlass_0719.xlsx, etc. Each contains 1 sheet with the same name. I tried the script below but it didn't work. How would I go about writing one load statement to iterate through the files? Any help would be much appreciated.
for each vFileName in Filelist ('\\nausp-wfil0001\ApplicationOwnerFiles\Global_Finance\North America Expenses\North_America_Expenses_Includes\FieldGlass*.xlsx')
I think this should work. One thing is, if you aren't running in Legacy Mode, your FileList path parameter needs to be off a Lib connection.
FACT_FIELDGLASS:
Load
Timestamp(Null()) as SOURCE_DATE
AutoGenerate
(0);
// Is Legacy Mode On? If not, FileList path should be a a lib connection.
for each vFileName in Filelist ('\\nausp-wfil0001\ApplicationOwnerFiles\Global_Finance\North America Expenses\North_America_Expenses_Includes\FieldGlass*.xlsx')
Concatenate(FACT_FIELDGLASS)
LOAD
"Invoice ID" as INVOICE_ID
, Supplier as VENDOR_NAME
, 'Job Posting by '&"Job Posting Owner"&' for a '&"Job Posting Title"&' '&"Labor Type"&' employee to be located at '&Site as DESCR
, date(floor("Invoice Submit Date")) as INVOICE_DATE
, num#(Date(MonthStart("Invoice Approved Date"), 'YYYYMM')) as DIM_ACTG_YR_MO_SK
, Currency as BASE_CURRENCY
, "Invoice Amount" as POSTED_TOTAL_AMT
, "Account Code Code" as DEPT_CD
, BUGL as BU_CD
, if(len(trim("GL Code (Professional)"))=0,"GL Code (Admin/Clerical)","GL Code (Professional)") as ACCOUNT_CD
, OU as OPER_UNIT_CD
, 'FIELDGLASS' as EXP_SOURCE
, 'PRE' as ALLOCATION_TYPE
, (filetime()) as SOURCE_DATE
FROM [$(vFileName)] // Needs brackets since there is a space in the path
(ooxml, embedded labels, header is 1 lines, table is [Spend_Report_-_AGS_Metrics])
;
next vFileName
store FACT_FIELDGLASS into 'lib://NA_Exp_BIFileshare_QVD/FACT_FIELDGLASS.qvd' (qvd);
drop table FACT_FIELDGLASS;
I think this should work. One thing is, if you aren't running in Legacy Mode, your FileList path parameter needs to be off a Lib connection.
FACT_FIELDGLASS:
Load
Timestamp(Null()) as SOURCE_DATE
AutoGenerate
(0);
// Is Legacy Mode On? If not, FileList path should be a a lib connection.
for each vFileName in Filelist ('\\nausp-wfil0001\ApplicationOwnerFiles\Global_Finance\North America Expenses\North_America_Expenses_Includes\FieldGlass*.xlsx')
Concatenate(FACT_FIELDGLASS)
LOAD
"Invoice ID" as INVOICE_ID
, Supplier as VENDOR_NAME
, 'Job Posting by '&"Job Posting Owner"&' for a '&"Job Posting Title"&' '&"Labor Type"&' employee to be located at '&Site as DESCR
, date(floor("Invoice Submit Date")) as INVOICE_DATE
, num#(Date(MonthStart("Invoice Approved Date"), 'YYYYMM')) as DIM_ACTG_YR_MO_SK
, Currency as BASE_CURRENCY
, "Invoice Amount" as POSTED_TOTAL_AMT
, "Account Code Code" as DEPT_CD
, BUGL as BU_CD
, if(len(trim("GL Code (Professional)"))=0,"GL Code (Admin/Clerical)","GL Code (Professional)") as ACCOUNT_CD
, OU as OPER_UNIT_CD
, 'FIELDGLASS' as EXP_SOURCE
, 'PRE' as ALLOCATION_TYPE
, (filetime()) as SOURCE_DATE
FROM [$(vFileName)] // Needs brackets since there is a space in the path
(ooxml, embedded labels, header is 1 lines, table is [Spend_Report_-_AGS_Metrics])
;
next vFileName
store FACT_FIELDGLASS into 'lib://NA_Exp_BIFileshare_QVD/FACT_FIELDGLASS.qvd' (qvd);
drop table FACT_FIELDGLASS;
It looks like it is not bringing in any records into the table. Before using the "Store" command, do you have any records in "FACT_FIELDGLASS" table ?
Ah that was it. I replaced the path with the Lib connection. I also didn't realize that the space in the path would require the From vFileName to need brackets.