Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Read different sheets of excel file

Hi,

I have an excel file containing data for each fruit code; the sheet name is formated by fruit code. For example the sheet for the apple is 9678 and it contains sales information for each fruit.

I have a loop that look for all fruit codes and read de excel file taking as sheet the code fruit but i want to validate if the code existing in my loop exist in the excel file; it is to avoid error during the reading data. it happend when there's not data for a fruit code.

I just want to read all sheet name to compare with fruit code in the bucle.

Any one help me?

1 Solution

Accepted Solutions
Not applicable
Author

Thanks,

Already i solved; I just read de file using excel driver conector and it reads all file sheets.

Here you are the code: the file path is specified in driver conector using a variable

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;

View solution in original post

9 Replies
settu_periasamy
Master III
Master III

Can you provide the sample data?

amit_saini
Master III
Master III

Julisy,

Follow below steps:

Step1:

Then press Alt F11, double click on any sheets in the left of the opened window and copy the below code as shown in the below picture.

Sub renameSheets()
Dim iSheetCount
For iSheetCount = 1 To Sheets.Count
Sheets(iSheetCount).Name = iSheetCount
Next iSheetCount
End Sub

Step2:

Then run by clicking the run button like below

Step3:

Save as the file in xlsx fomat.

Step4:

Then Open QVW, Press Cntrl E, go to the code marked in red as shown in the below picture and change the number as equal to the sheets available in the file "Fruit Sheet" after running the macro. Assuming the file contain 290 sheets.
Eg. for loop=1 to 290 

Hope this will helps u!

Thanks,

AS

jagan
Luminary Alumni
Luminary Alumni

HI,

Try like this

Load Multiple excel sheets using For loop

// Generate Empty table

Data:

LOAD

'' AS Dummy

AutoGenerate(0);

FOR Each sheet in ('Apple', 'Banana', 'Etc')

Concatenate(Data)

LOAD *

FROM [FileName.xlsx]

     (ooxml, embedded labels, table is [$(sheet)]);

NEXT

DROP FIELD Dummy;

HOpe this helps you.

Regards,

jagan.

jonathandienst
Partner - Champion III
Partner - Champion III

Its a lot easier to help you if you provide sample data to illustrate your spreadsheet and your requirements. Do you want to dynamically load all the sheets in a spreadsheet, and the actual sheet names are not known ahead of time? And compare them with a list of names/codes? Where is this list coming from? Do you just need the sheet names, or do you want to load all the data? Do all the sheets contain the same set of columns? etc etc

I also have no idea what this might mean:

>>I just want to read all sheet name to compare with fruit code in the bucle.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
avinashelite

please provide the same data so that we can help you out on the same.

Not applicable
Author

the problem here is you are setting the fruit name as fixed way and i can't to do it thats.

I have a table fruit that fill as dinamic way and it is the problem. i need to confirm if the fruit code exists in the excel file before to read the information, because if it's doesn't exists, the application won't work correctly.

There is only one excel file containing one sheet per fruit and the sheet name is a code fruit.

I hope you understand.

Not applicable
Author

If you try this code, it won't work correctly because when the application try to read a sheet with name Apple (for example) and it doesn't exists in excel file, will happend an error in the application

jagan
Luminary Alumni
Luminary Alumni

Hi,

You can read the Excel sheet names using below URL, adjust the code according to your requirement.  It will read all the Sheets in the Excel file.

Load all Excel files and all sheets in a folder

Regards,

Jagan.

Not applicable
Author

Thanks,

Already i solved; I just read de file using excel driver conector and it reads all file sheets.

Here you are the code: the file path is specified in driver conector using a variable

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;