Loading multiple sheets from excel

[#1]

Hi,

I'm trying to load multiple sheets from an excel document.  I know there are already many tutorials available. I checked out a lot, but still I don't managed to get it fixed. I tried to replicate these steps from a youtube video (Qlikview FOR EACH... Load Multiple Excel Sheets by RFB 200 - YouTube). I have the feeling that I'm close.

The issue at hand is:

The script only loads one sheet (the first sheet). When i check the script with the debugger it tells me that it can't find the next value because its a <null>. But it definitly has a valid name. I think it's a quote / '[]' issue.

Anyone got an idea?

Thanks in advance guys.

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Stefan\Downloads\Diensten_2015.xlsx];

Let vfile = 'C:\Users\Stefan\Downloads\Diensten_2015.xlsx';

exceltables:

SQLTABLES;

DISCONNECT;

For i = 0 to NoOfRows('exceltables')-1

Let vExcelSheets = purgechar(peek('TABLE_NAME', i, 'EXCELTABLES'),chr(36));

LOAD *,

'$(vExcelSheets)' as Sheet

FROM

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

next

[maxgro]

try replacing the Let with

Let vExcelSheets = left(Peek('TABLE_NAME', i, 'EXCELTABLES'), len(Peek('TABLE_NAME', i, 'EXCELTABLES'))-1);

[maxgro]

this is a full load many files, many sheets

DIRECTORY;

Table:

load '' as field autogenerate 0;

For Each vFile in FileList('excel load files load sheets*.xlsx')

     trace file=$(vFile);

    ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

    Sheets:

    SQLTABLES;

    DISCONNECT;

    For i = 0 To NoOfRows('Sheets')-1

    Let vSheet = PurgeChar(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1), chr(39) & '$');

        trace $(vSheet);

  concatenate(Table)

        LOAD '$(vSheet)' as [Tab Name],

        *

        From [$(vFile)]

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

  Next;

  DROP Table Sheets;

NEXT;

[#4]

Just replacing unfortunately doesn't work. It stills gives me only the first result of my sheets.

[settu_periasamy]

Hi,

Try this..

LET vFolder = ''C:\Users\Stefan\Downloads\'; 

FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx') 
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)]; 

tables:
SQLtables; 
DISCONNECT; 
LET vSheetCount = NoOfRows('tables'); 
FOR iSheet = 0 to $(vSheetCount) -1 
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.'); 
[$(vSheetName)]: 
LOAD *,1 AS SampleCount, 
'$(vSheetName)' as Sheet, 
'$(vFile)' as FileName 
FROM [$(vFile)] 
(ooxml, embedded labels, table is [$(vSheetName)]); 
NEXT iSheet 
DROP TABLE tables; 

Next vFile

Load all Excel files and all sheets in a folder

[#6]

This seems to be working. When I follow the steps in the 'executing script window' the sheet are properly fetched. Unfortunately i ran in some trouble in this fase. Al the lines are fetched but the scripts doesn't finish. It keeps running, without results. The windowns doesn't closes aswell.

I think that the script hastrouble creating synthethic tables. For every field / column a relation is created. Is there any way I can prevent this or check this? Perhaps concatenating the table could work, but i'm not sure how to execute that correctly.

Would you mind helping me?

Gr,

Stefan

[settu_periasamy]

Sure. Is it possible to provide the exact script you are working and sample of excel files ?

[jonathandienst]

If the fields in the sheets are not identical, you will land up with multiple sheets, and a mess of synthetic keys (ie composite keys). This is not what you want. But the solution will depend on the natures of the data you are trying to load. Lots of people may suggest QUALIFY *, and it may work for you, but it is rarely the best solution.

[jonathandienst]

It is important to do some analysis up front and load to a data model design, rather than a 'load and hope' approach. That way you will know whether the excel sheets represent data that should be loaded into a common table - when the loop will be a good solution, but do an explicitly concatenate - or whether the data represents more than one logical table, in which case the loop is not a good solution.

[jonathandienst]

See these articles by Marcus Sommer for more information:

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

More advanced topics of qlik datamodels