topic Re: Import Excel in QlikView

Import Excel

Wed, 08 Aug 2012 15:02:03 GMT

So I have a series of excel files that I need to import into Qlikview. They hold times for CPU busy ranges based on day and hour as you can see in my sample.

I need to sort these out though. All the dates need to come in, along with the hours that they happened. I'm not sure how to capture the dates and hours correctly though, as some are in a column and others are in a row.

Thanks!

Re: Import Excel

Wed, 08 Aug 2012 17:19:49 GMT

Anyone have any ideas?

Re: Import Excel

Anonymous — Thu, 09 Aug 2012 00:30:14 GMT

I tried. Created a table like yours, with dates from Aug 1 to Aug 15. Used wizard to create crosstable, and here is what I got:

Data_src:
CrossTable(Date, Amount)
LOAD F1 as Hour,
     [41122.000000],
     [41123.000000],
     [41124.000000],
     [41125.000000],
     [41126.000000],
     [41127.000000],
     [41128.000000],
     [41129.000000],
     [41130.000000],
     [41131.000000],
     [41132.000000],
     [41133.000000],
     [41134.000000],
     [41135.000000],
     [41136.000000]
FROM CrossTime.xlsx (ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(Remove(Col, Pos(Top, 1))));

// some manual manipulation:
Data:
LOAD
left(Date,5) as Date, // making date out of the format i got from Excel
Hour,
timestamp(left(Date,5) + Hour/24, 'MM/DD/YYYY hh') as Time,
Amount
RESIDENT Data_src;
DROP TABLE Data_src;

File attached.

Regards,

Michael

Re: Import Excel

Mon, 13 Aug 2012 18:27:11 GMT

Thanks for the reply. The wizard is kind of confusing. I tried it but it doesnt come out correctly. Plus this data im using is in the middle of a sheet.

I also will have to load data from separate excel sheets for each month but the format should be the same.

I guess an explanation of how you got this is what I need.

Re: Import Excel

Mon, 13 Aug 2012 18:39:32 GMT

Maybe this will help instead of a screen shot. Here is the sheet I'm working with.

I want all the data from row 46 to row 70. I really dont need column 'A' either.

So coming from that excel file, how would I do this load? Keep in mind I want to automate this process so a new excel can be added to the folder each month and have it automatically import the data.

Thanks so much for your help!

Re: Import Excel

Anonymous — Tue, 14 Aug 2012 01:54:15 GMT

Here is a version based on your file. Important points:

First load, Data_src:
FIRST 25 - load 25 rows,
FROM [Excel Sample.xlsx] (ooxml, explicit labels, header is 38 lines, table is Sheet1); - starts from your line 46 in Excel, and this line is the headers in this load. It is 38 not 46 because empty lines are not counted.
Using explicit labels (C, D, E...) because we don't know the actual dates there. Renaming to Date1, Date2, ... - to be used later in the loop.

Second load, Data_tmp:
Loop through date and hour. Max number of date, I assume, is 32. ErrorMode=0 to ignore errors if it is less than 32.

Last load, Data:
Mostly to cretae Time field

Every new Excel file must be in the same exact format, except the number of columns (dates maybe less than 32).

Hope it helps...

Regards,
Michael

Re: Import Excel

Wed, 15 Aug 2012 13:07:49 GMT

Michael,

This works great. Thank you!

I wish I could understand it more though.

-Ethan

Re: Import Excel

Wed, 15 Aug 2012 13:22:01 GMT

Although when I try to use the real path to the full file, I changed the path and the sheet name, it tells me I have a bad zip file.

Why am I getting this error?

If I use the file I gave to you, it works correctly.

The only difference between the two files is I gave you the one sheet I plan on using and made it a different excel file.

Re: Import Excel

Wed, 15 Aug 2012 20:29:00 GMT

As an update, I replaced the ooxml with biff at the bottom of the select statement because I found online that ooxml is for newer excel files. The think the original one is a different version of excel so I need to use biff.

When I use biff, it doesn't throw the "bad zip file" error, but it now says it can't find field "B"

Re: Import Excel

Anonymous — Thu, 16 Aug 2012 00:04:19 GMT

Ethan,

Thats correct, it is a little different with .xls files. I tried to save your file in an older format, and used wizard to see how it looks. It shouild work for you if you change in the script:
B to @2

C to @3

...

AH to @34

Here is how it looks for me, before adding "if" logic to the 1st field, and renaming to "DateN":

FIRST 25

LOAD

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11,

@12,

@13,

@14,

@15,

@16,

@17,

@18,

@19,

@20,

@21,

@22,

@23,

@24,

@25,

@26,

@27,

@28,

@29,

@30,

@31,

@32,

@33,

@34

FROM [Excel Sample.xls] (biff, no labels, header is 37 lines, table is Sheet1$);

Maybe you have evem older Excel version - just try wizard, and see what syntax it creates. After this, adjust as needed.

Regards,

Michael

Re: Import Excel

Thu, 16 Aug 2012 11:39:40 GMT

So when loading a new version of excel, the fields come in as letters? Vs numbers with older versions?

Re: Import Excel

Anonymous — Thu, 16 Aug 2012 22:56:04 GMT

Ethan,

there are several diffrences. just try using wizard, you'll see.

Regards,

Michael

Re: Import Excel

schivet — Wed, 26 Sep 2012 10:16:55 GMT

Hello,

I have the same problem with several excel loads. I use to read them everyday and since a few days, QlikView cannot find the last column! I have values in the column (xls). If I open the excel file, put a 0 in the last column and save it, it's working!?

Did you find the solution to your problem?

Thanks

Regards

Stéphane

Re: Import Excel

schivet — Wed, 26 Sep 2012 10:17:11 GMT

Hello,

Did you find the solution to your problem?

Thanks

Regards

Stéphane

Re: Import Excel

schivet — Wed, 26 Sep 2012 10:17:20 GMT

Hello,

Did you find the solution to your problem?

Thanks

Regards

Stéphane