Load same sheet from multiple files

[carlcimino]

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.

[treysmithdev · Accepted solution]

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;

[trdandamudi]

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 ?

[carlcimino]

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.