Load selected files that listed in another table

[A7R3]

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.

[Ksrinivasan]

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.

[A7R3]

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.