Solved: Re: QlikView keeps crashing when I try to load my ... - Qlik Community

Anonymous · ‎2015-12-30

I have data in an excel spreadsheet (*.xlsx) that consists of 1,213 rows of sensitive information (so, I'm sorry I can't share the data) and 35 columns. Every entry is a string (I don't know if that is screwing it up or not). The first row is the column names and I've never had a problem importing it with the column names embedded before (it's just easier to click that they're embedded so I don't have to name every column by hand). I put the path to the data in the quick start wizard and hit the next button and it doesn't do anything. I hit it again and it turns the mouse into the loader as if it's loading. I've waited for it for 15 minutes before, but every time I click on QlikView the program just crashes.

I have a deadline I have to meet here and I can't afford to not finish this project. It's extremely important that I get it working.

Just as a NB, I used Python to merge two Excel spreadsheets together so I don't know if that may be what's causing the problem either. I can open the file perfectly fine in Excel though.

Anonymous · ‎2015-12-30

I solved the issue by bypassing the initial walkthrough that will load your data and make your first chart for you. I just saved an empty QlikView file and edited the script. I actually asked the question on StackOverflow and provided the answer. I won't put the link to the question here but I will post the answer. See below-

I was using the walkthrough when creating a new file when I should have just made a script. First click on edit script in the menu bar. Click Table Files.... Choose your file, then make sure that the Labels section has Embedded Labels in the dropdown. It will create a query like the following-

LOAD 
[Resource name], 
[Employee ID], 
Vertical, 
[Contract Type *], 
Notes 
FROM [D:\path\to\file\*.xlsx] 
(ooxml, embedded labels, table is Sheet1);

That's one part of the solution, but then I ran into a new problem. It said that it was able to fetch all of the rows, but when I started making the charts and graphs it was only showing 6 data points. I recreated the file, did exactly what I did above but also added the transformation step. Now the problem is solved.

View solution in original post

Clever_Anjos · ‎2015-12-30

Hard to help without seeing your excel file, could you mayb scramble your data?

marcus_sommer · ‎2015-12-30

It sounds quite strange - are you loading further things in this app? Then please share your load-script.

As a workaround you could try to load your data with a first-statement like:

table:

First 5 Load ....

Further helpful - as a temporary respectively intermediate step - might be to copy those data into a txt-file and try to load the data from there.

- Marcus

Anonymous · ‎2015-12-30

I solved the issue by bypassing the initial walkthrough that will load your data and make your first chart for you. I just saved an empty QlikView file and edited the script. I actually asked the question on StackOverflow and provided the answer. I won't put the link to the question here but I will post the answer. See below-

I was using the walkthrough when creating a new file when I should have just made a script. First click on edit script in the menu bar. Click Table Files.... Choose your file, then make sure that the Labels section has Embedded Labels in the dropdown. It will create a query like the following-

LOAD 
[Resource name], 
[Employee ID], 
Vertical, 
[Contract Type *], 
Notes 
FROM [D:\path\to\file\*.xlsx] 
(ooxml, embedded labels, table is Sheet1);

That's one part of the solution, but then I ran into a new problem. It said that it was able to fetch all of the rows, but when I started making the charts and graphs it was only showing 6 data points. I recreated the file, did exactly what I did above but also added the transformation step. Now the problem is solved.

Report Inappropriate Content · ‎2015-12-30

the only thing I can share is that I had an issue with Excel files created by a program - there was only 35 rows of data but the way the excel was created programatically, it really had over 16,000 rows. The wizard did not freeze up but it did take a while to go to the next wizard step.

along the lines of Marcus' suggestion, can you copy the 1213 rows from the excel and paste into a new excel (I did the same thing with my issue to prove what was happeniong) - tht would ensure that the excel you are reading from is truly the size you are expecting

petter · ‎2015-12-30

It is most likely that the Python script that is creating the merged xlsx do this in a manner that Excel is tolerant about but that non-Microsoft software have some problems with. I would try to load it into Excel and then save it again with a new name just to see if this new Excel file cause the same problem. It might be that Excel will clean up the file.

As another option I would use Marcus approach but loop and load 10 rows at a time to find if there are some special rows that create the problem:

TotalBatchRows = 1213;

NoOfBatchRows = 10; // You can even turn it down to only 1 record/row per iteration if necessary

NoOfBatches = Ceil( TotalBatchRows / NoOfBathRows ) ;

FOR i=0 TO NoOfBatches

Min = i * NoOfBatchRows;

Max = i + NoOfBatchRows - 1;

TRACE Trying to load the rows $(Min) to $(Max);

table:

LOAD

*

FROM xxxxx.xlsx (ooxml,.....)

WHERE RecNo()>=$(Min) AND RecNo()<$(Max);

petter · ‎2015-12-30

You could also try to load just one or a few columns at a time and see if that works ... maybe it is a particular column from the spreadsheet that cause problems for you.

Lastly - MAKE SURE that all column headers in the Excel-file are unique... that could cause a problem if they are not.

Anonymous · ‎2015-12-30

Yea there's something wrong with the data. It's not loading everything. I don't know why. I'll try the mentioned approach.

Peter_Cammaert · ‎2015-12-30

If this fixes your issue, please mark your own answer as "Correct" to close the discussion. Thanks.

QlikView keeps crashing when I try to load my data and go to the next step