topic Re: Load same sheet from multiple files in App Development

Load same sheet from multiple files

carlcimino — Sat, 16 Nov 2024 20:15:36 GMT

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')

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)

(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;

Here's the error I get.

Re: Load same sheet from multiple files

treysmithdev — Thu, 15 Aug 2019 14:41:07 GMT

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;

Re: Load same sheet from multiple files

trdandamudi — Thu, 15 Aug 2019 14:41:29 GMT

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 ?

Re: Load same sheet from multiple files

carlcimino — Thu, 15 Aug 2019 15:01:44 GMT

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.