how can I read a XLS with different

Wed, 23 May 2012 21:49:09 GMT

I got a XLS book with different leaves and I need to read, somebody can tell me how can I do that.

Best regards.

Re: how can I read a XLS with different

stevedark — Wed, 23 May 2012 22:29:54 GMT

Are each of the tabs identical, or is each one different? Is there a logical naming of the tabs - or is it ad-hoc? Are each of the tabs relatively simple (eg. titles on Row 1 and data below) or are there complexities?

These things will determine to quite an extent what the best solution will be.

One approach is to create an ODBC connection to the spreadsheet, and there is then a syntax will allows you to enumerate from 1 to the count of 'tables' in the ODBC connection. Another is to create a list of tabs and load that into a temporary table and then enumerate around this table.

This code does the later:

Temp_BudgetSheets:

LOAD

[Budget Sheet]

FROM $(vDataFolder)2012Budgets.xlsx

(ooxml, embedded labels, table is [Budget Sheets])

;

for vSheetNo = 0 to NoOfRows('Temp_BudgetSheets') -1

let vBudgetSheet = peek('Budget Sheet', vSheetNo, 'Temp_BudgetSheets');

Temp_BudgetValues:

LOAD

A as GLCode,

B as M1,

C as M2,

D as M3,

E as M4,

F as M5,

G as M6,

H as M7,

I as M8,

J as M9,

K as M10,

L as M11,

M as M12

FROM [$(vDataFolder)2012Budgets.xlsx]

(ooxml, no labels, header is 6 lines, table is [$(vBudgetSheet)]);

In the instance above a CROSS TABLE load was then required on the temporary table.

If each of the sheets do not have exactly the same columns you will need to be quite careful about how each of the tables join or concatenate.

Hope that helps.

Steve

topic Re: how can I read a XLS with different in QlikView

how can I read a XLS with different

Re: how can I read a XLS with different