I have a folder that contains several monthly .xlsx files. Most of these files contain a field called "Grade Category". In the newer months, this field is renamed to "Career Band/Level Category". I am trying to create a process to loop through each of these files and rename the field if needed and store into a qvd. The 2 processes are broken out below. I've also included my script. This script works fine for all of the files that contain "Grade Category". Once it gets to a file where the field is "Career Band/Level Category" I am getting an error stating 'FinalTable' is not found.
Any suggestions on what I'm doing wrong? Thanks in advance!
1. If the field in the file is called "Grade Category", rename it to "Career Band/Level Category" and then store it into a qvd.
2. If the field in the file is called "Career Band/Level Category", no renaming is necessary and the file can be stored into a qvd.
My Script:
// Define the source folder containing the raw files and the QVD target folder LET vSourceFolder = 'lib:/.../'; LET vTargetFolder = 'lib://.../';
// Get a list of the files in the source folder FOR Each vFile in FileList('$(vSourceFolder)*Global Audit Reports Active *.xlsx') // Extract the file name without the extension LET vFileName = SubField(vFile, '/', -1); LET vFileBaseName = SubField(vFileName, '.', 1); // Remove the extension
// Construct the QVD file name LET vQvdFileName = '$(vTargetFolder)' & '$(vFileBaseName).qvd';
// Debugging: Trace the generated QVD file name TRACE Checking QVD file: $(vQvdFileName);
// Check if the QVD already exists IF NOT IsNull(FileSize('$(vQvdFileName)')) THEN // If FileSize returns a number, that means the file exists TRACE QVD [$(vQvdFileName)] already exists, skipping loading of [$(vFile)]; ELSE // Load the data from the Excel file into a temporary table TempTable: LOAD * FROM [$(vFile)] (ooxml, embedded labels, table is [RPT033]);
// Check for the existence of the Career Band/Level Category field using FieldIndex LET vFieldExists = FieldNumber('Career Band/Level Category', 'TempTable'); TRACE Checking vFieldExists: $(vFieldExists);
// Create the final table based on the field existence IF vFieldExists > 0 THEN FinalTable: LOAD * RESIDENT TempTable; ELSE FinalTable: LOAD *, "Grade Category" AS "Career Band/Level Category" RESIDENT TempTable; Drop Field "Grade Category" from FinalTable; ENDIF;
// Store the data from the final table into QVD STORE FinalTable INTO [$(vQvdFileName)];
// Drop the temporary tables to free up memory DROP TABLE TempTable; DROP TABLE FinalTable; ENDIF NEXT vFile;
Log:
QVD [lib://.../Global Audit Reports Active 2024-11.qvd] already exists, skipping loading of [lib://.../Global Audit Reports Active 2024-11.xlsx] Checking QVD file: lib://.../Global Audit Reports Active 2024-12.qvd
I think you may be overthinking it. I would think using just an ALIAS statement would work. The Alias statement will automatically rename the field if it exists.
Alias "Career Band/Level Category" as "Grade Category"; LOAD * From lib://source/*.xlsx;
You can adapt this to a For loop if you actually need to read and store each file -- but I was guessing maybe you were just doing that for the field rename.
