topic Re: Loading Data in QlikView

Loading Data

Tue, 23 Dec 2014 07:42:46 GMT

Hi all,

How to load data if One excel sheet itself got 4 tables.

Janaki.

Re: Loading Data

amit_saini — Tue, 23 Dec 2014 07:50:13 GMT

Hi Janaki,

Please share your excel sheet.

Thanks,
AS

Re: Loading Data

ecolomer — Tue, 23 Dec 2014 07:50:54 GMT

You need to load each sheet

Re: Loading Data

Tue, 23 Dec 2014 08:00:40 GMT

Hi Amit

PFA

Re: Loading Data

jagan — Tue, 23 Dec 2014 08:00:56 GMT

Hi,

Check this links

Load Multiple excel sheets using For loop

Loading Multiple Excel sheets

Regards,

Jagan.

Re: Loading Data

its_anandrjs — Tue, 23 Dec 2014 08:12:55 GMT

Hi,

You can try this expression and and specify the excel file name here.

For Each vFile in FileList('Data.xlsx')

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

Sheets:

SQLTABLES;

DISCONNECT;

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

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

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

From [$(vFile)]

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

Next;

Regards

Anand

Re: Loading Data

Tue, 23 Dec 2014 08:15:02 GMT

thank you Anand..

Re: Loading Data

Tue, 23 Dec 2014 08:18:19 GMT

Hi Jagan,

I can not open second link, it says access denied.

Thanks.

Re: Loading Data

jagan — Tue, 23 Dec 2014 08:21:41 GMT

Hi,

This is the script in the second link

// Configure sheet names to this variable

SET vSheetNames = 'Jan Sales', 'Feb Sales','Mar Sales','Apr Sales','May Sales','Jun Sales','Jul Sales';

// Loop through the sheets and load the data

for each vSheetName in $(vSheetNames)

Data:

LOAD *

'$(vSheetName)' AS SheetName

FROM

[test.xlsx]

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

The second is no need to mention the Sheet name just remove the table is *** .

Note: This method only works for XLSX.

Data:

LOAD *

'$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, header is 2 lines);

Regards,

Jagan.

Re: Loading Data

Tue, 23 Dec 2014 08:23:15 GMT

Thanks jagan.

Re: Loading Data

Peter_Cammaert — Tue, 23 Dec 2014 08:30:42 GMT

Janaki,

I saw a lot of answers that deal with loading all sheets from a single Excel (IMHO that was not your question)

But I'm interested in knowing how you managed to load those multiple freely formatted tables from a single Sheet.

Peter

Re: Loading Data

Tue, 23 Dec 2014 08:42:37 GMT

Yes Peter i haven't got answer for the question, however i got a requirement to load multiple sheets from Single Excel.I am trying to find out answer to load those multiple freely formatted tables from a single Sheet. i will get back to you soon.

Thanks

Re: Loading Data

Peter_Cammaert — Tue, 23 Dec 2014 08:52:18 GMT

I think I may have found a solution, but it requires some work. And I don't know if it will fit your situation.

In Excel, you can assign Names to ranges of cells. For example, if you assign a name UNITS to the range A2 to F14 in your sheet, you will discover that the name pops up in the list of tables in the Table Files... dialog in QV Script Editor and together with the sheet names. If you do this for all three tables on the single sheet, you can still use Jagan's code to load the tables instead of any sheets. Just change the sheet names in vSheetNames into table names.

FYI I did prepare some excel data (see attachment).

Peter

Re: Loading Data

Tue, 23 Dec 2014 09:14:08 GMT

I couldn't found any attachment peter.

Thanks

Re: Loading Data

nikhilgarg — Tue, 23 Dec 2014 09:33:59 GMT

Hey,

In your follwoing script :

Data:

LOAD *

'$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, header is 2 lines);

What does header is 2 lines means ?? As it doesnot give same output as the alternate script does.

Re: Loading Data

jagan — Tue, 23 Dec 2014 09:38:07 GMT

Hi,

header is 2 lines means, if header starts in second row in your excel file and in first there is some other text, then this will consider 2 row as header row.

Regards,

Jagan.

Re: Loading Data

Peter_Cammaert — Tue, 23 Dec 2014 09:50:13 GMT

Here again.

Re: Loading Data

Tue, 23 Dec 2014 10:10:56 GMT

No it is not there.plz double check

Re: Loading Data

Peter_Cammaert — Tue, 23 Dec 2014 10:50:35 GMT

Please open the full thread. Do not expect attachments to be visible in your Inbox, because they won't.

Best,

Peter

Re: Loading Data

Tue, 23 Dec 2014 10:56:49 GMT

got it thanks