I think you need to create a Country field but you didn't do it manually. With file-functions like filebasename() you could read the filename from currently loaded file and with additional string-functions like subfield(), mid() and so on you could separate the Country value if there are further prefix/suffix within the filename.
Also the sheet-names could be read but for this you will need to load xls per ODBC like in this example: Loading Multiple Excel Sheets Dynamically along with file name and sheet name.
Hi Edil, I once did something similar to that; I had folder in which I had 3 excel files, each one contains the client, and its sales (sthing like this)
But each file have a name of a country : Tunisia.xls, Algeria.xls and so on; so every file was specific to a country;
What I did, is creating a new country field based on the name of the excel file :
here how it goes:
let vDir='D:\Formation\Flags examples\FlagConcat';
LOAD '' as SalesMan,
null() as Sales,
'' as Pays
for each vF in filelist (vDir & '\*.xls' )
let name= SubField('$(vF)', '\',5) ;
let vName= '$(name)' ;
SubField('$(vName)','.' ,1) as Pays
(biff, embedded labels, table is Feuil1$);
DROP Table test;