Looping Excel Files after Looping each Excel files Sheets

Fri, 09 Nov 2012 07:19:10 GMT

Good Day All,

I have a folder with excel files, i need to loop each sheet with different name of the excel file

after looping it, i need to loop the excel files inside the folder.

Any idea how to loop inside a looping excel?

Thank you very much.

Regards,

Raem

Re: Looping Excel Files after Looping each Excel files Sheets

Fri, 09 Nov 2012 07:55:11 GMT

There is an explaination and example regarding your problem

Wed, 03 Feb 2016 15:30:23 GMT

here you are the code do this: vArchivo is a variable name containing the file path: examples C:\Prueba.xls

			OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vArchivo);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

			// Read list of sheets
			Temp_Tables:
			sqltables;



			// Enumerate las hojas del archivo
			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 quotes

		If(len('$(vSheetName)') = 5) then
			Concatenate(Metas)
			LOAD Sociedad as ID_Sociedad,
			Org.Ventas as ID_OrgVta,
			Left(Vendedor,8) as ID_Vendedor_Cte,
			SubField(ApplyMap('MapAV', Sociedad&'000'&[Cod Cliente]),'\|',1) as ID_SocOV_Key,
			SubField(ApplyMap('MapAV', Sociedad&'000'&[Cod Cliente]),'\|',2) as ID_Cliente_Key,
//				'000'&[Cod Cliente] as ID_Cliente,
			[Saldo USD],
			Sociedad&Factura as NoDocumento_Key,
			FINANZAS as SaldoFI,
			Fecha as FechaDocumento,
			Vence as FechaVencimiento,
//			MakeDate(2016,01,01) as FechaAplicacion
				'$(vFile)' as FechaAplicacion,
				'$(vFile2)' as ArchivoOrigen
			//// Sociedad&Org.Ventas&'000'&[Cod Cliente] as ID_Cliente_Key,
				FROM [$(vArchivo)]
					(biff, embedded labels, table is [$(vSheetName)]);
	ENDIF
		next
		DROP TABLE Temp_Tables;