topic Re: Load selected files that listed in another table in App Development

Load selected files that listed in another table

A7R3 — Sat, 16 Nov 2024 17:36:16 GMT

Hi experts,

I would like to know if there is a way to load selected files that are listed in another table, and ignore others.

My situation is as follow.

The QS is loading files from the folders organized by date.
Ex. C:\Data\YYYYMMDD\BOM_ProductNumberXXX.xls
The users will upload the files as when the new files are available.
There is MasterPlan.xls that contain all the in-scope products, along with other info for each products. This spreadsheet is loaded into the QS.

What happen is that the users are not following the list of the products in the MasterPlan and there are files that not required being uploaded as well.

Here is my load scrip.

TableMasterPlan:

LOAD
Site,
Business,
PLC,
Trim(Products) as Products,
ProductDescription,

FROM [$(vRefTablePath)/MasterPlan.xlsx]
(ooxml, embedded labels, table is MasterPlan);

FOR Each vDir in DirList('$(vDataPath)/*')

FOR each vFile in filelist (vDir&'\*.xls' )

Table:

LOAD Distinct
TRIM(Mid(filebasename(), 16)) as Products,
Date(Floor(FileTime())) as ReportDate,
@13 as SpecID,
@2 as MatlNumber,
@3 as MatlDescription,
@14 as "Spec. Type"
FROM [$(vFile)] (biff, no labels, header is 10 lines)
Where @14='MAT_PART_E';

Next vFile

Next vDir

Appreciate any suggestion that you may have to modify the load script to load just the products listed in the MasterPlan table. For ex., if there are 50 files in the folder, and the Masterplan has 20 products, the load script will load the files matching the Products in Masterplan, and skip others.

Re: Load selected files that listed in another table

Ksrinivasan — Tue, 19 Jan 2021 05:44:47 GMT

hi, may this workaround steps give result

1. Master Product Table and Standard file name.

2. Today Master plan list may be 1,4,5,6,9 and 10,

3. we have to Arrive the below product and file list by Script

4. Convert product and file name as variable with row count

5. set loop total file to be uploaded count=6

set loop for loading file from count 1 to 6 by respective file variable name.

Note daily we have to run daily Master plan script, before file load script. daily product may vary, so we need to capture real list of product.

Ksrinivasan.

Re: Load selected files that listed in another table

A7R3 — Tue, 19 Jan 2021 06:18:23 GMT

Hi @Ksrinivasan ,

Looks like we have the similar situation to handle. Thanks for your suggestion. In my case, the MasterPlan rarely changes, but the approach may still work.

I think the current script already cater for for#1 and #2. I can pick up the correct file name to load. Could you help to suggest the changes for #4 to #5?

BTW, each folder may not have all the files. For ex., in the 20201201 folder, it may have 2 out of 6 files required. In the 20201215 folder, it may another 5 out of 6 files required. So the "upload count" needs to be flexible.

Greatly appreciate your advice.

Re: Load selected files that listed in another table

stascher — Fri, 18 Apr 2025 19:53:30 GMT

Perhaps this?

FOR Each vDir in DirList('$(vDataPath)/*')

FOR each vFile in filelist (vDir&'\*.xls' )

Table:

Next vFile

Next vDir