Possible Bug: LOAD @1 FROM [*.xls] (biff, no labels, table is Sheet1$);

[RSvebeck]

Hi

I'm running QV11 and I've discovered that Qlikview seems to handle this load code wrong:

LOAD @1 FROM (biff, no labels, table is Sheet1$);

If the folder "c:\temp\" contains only *.xls files it works fine, but if the folder contains both *.xls and *.xlsx files then Qlikview tries to load also the *.xlsx files which will not work since *.xlsx files requires another specification (ooxml, no labels, table is Sheet1).

What I expected from Qlikview was to only load the XLS files and ignore all XLSX files.

With this problem, I must make sure that no XLS and XLSX files are located in the same folder.

Can anyone verify this bug?

Best Regards

Robert Svebeck

[Miguel_Angel_Baeyens · Accepted solution]

Hi Robert,

Yes, it seems that when QlikView gets to the "*" it stops reading the following chars, and load all files. But does that not happen if you use the "?" instead of the "*", meaning that if your files are File0.xls to File9.xls and likewise but with XLSX extension, using

LOAD *
FROM File?.xls
(biff, no labels, table is Sheet1$);

Will work fine, loading only those files that have only one character before the extension. My assumption here is that for QlikView your example is equal to:

FROM  (biff, no labels, table is Sheet1$);

I think it's worth opening a Support case with support@qlik.com to see if this is a WAD or a bug.

Hope that makes sense.

Miguel

Revision 1: If there are some other files in the same folder named alike but with different extensions, they are not loaded when using the "*". But if these files use "*.xls100" as extension, they are loaded, so it's not that QlikView ignores the extension (as I thought in the first place) rather than it takes only the three first characters of the extension and therefore the error. Again, this must be some OS related error, but anyway worth checking with Support.

[RSvebeck]

Thanks Miguel.

The ? solution is a good workaround  - but I can't use that since my filenames are not under my control, and I will also keep getting data in both XLS and XLSX into my folder. I will write a batch file to split the files into separate folder until I get a fix from Qliktech. 

Brg Robert

[jonathandienst]

Robert

I know this has been answered, but what about using a ForEach zFile in FileList(...) to loop over the files. zFile will get the filname, which you can analyse whether it is xls or xlsx and load using the correct load attributes.

Just my 2c worth.

Jonathan

[RSvebeck]

Thats a great suggestion! I will do that instead. Thanks! //Robert

[RSvebeck]

For future references: Here is my complete script with that work-around:

for each zFile in filelist (c:\temp\*')

if right('$(zFile)',4) = 'xlsx' then

LOAD Field1 FROM $(zFile) (ooxml, embedded labels, table is Sheet1);

end if;

if right('$(zFile)',3) = 'xls' then

LOAD Field1 FROM $(zFile) (biff, embedded labels, table is Sheet1$);

end if;

next zFile;

[#7]

Excellent Robert

Solution worked first time

I used it to define xls, xlsm, and xlsx files in the same folder

Many thanks