hi all,
I want to know the excel file name and sheet used in that file from different folders,
I want to load all excel and all sheet from that excel which have data only,
i have one script which gives me all excel file name and sheet name, but it gives me sheet which don't have any data as well
i want to omit those sheet name which are blank or no data,
script ...........................
let FilePath='C:\Data\Shared Data\CB0\';
// enumerate files
for each vFile in filelist('$(FilePath)*.xls*')
// connect to eaach Excel file
OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];
// Read list of sheets
Temp_Tables:
sqltables;
NEXT;
for iSheet = 0 to NoOfRows('Temp_Tables') - 1
let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');
let vSheetName = replace(replace(vSheetName, '$', ''), chr(39), ''); // sqltables seems to add a random $ sign and single quote
// Load the data
Temp:
NoConcatenate
LOAD
'$(vFileName)' as [File Nametest],
'$(vSheetName)' as [Sheet Nametest]
FROM [$(vFile)]
(ooxml, embedded labels, header is lines, table is [$(vSheetName)]);
next
EXIT Script;
thanks for your help....
Vivek,
