Import Excel

[#1]

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!

[#2]

Anyone have any ideas?

[Anonymous]

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

[#4]

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.

[#5]

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!

[Anonymous · Accepted solution]

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

[#7]

Michael,

This works great. Thank you!

I wish I could understand it more though.

-Ethan

[#8]

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.

[#9]

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"

[Anonymous]

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