Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

Find out which files exist in a directory?

Hi,

there is a directory where there are eleven files, sent by the customer every morning and just saved in that directory by the people in another team - I cannot see it.

In QlikView, there used to be a LOAD with wildcard to load all of these files - they are all named in the format

>>> *für* <<< (there is one > F2_Montag für Mittwoch <, one >F2_Mittwoch für Freitag < and so on, and one >Gesamt für Packrückstand< - so the formulation with the asterisk loaded them all.

<=> The issue used to be that every so often, this LOAD failed and because I didn't see any details in the app-log, I - or my colleague - then had to look in the directory and open every one of those 11 lists to see in which one the error was ...

I got fed up with this and resolved the LOAD into 11 individual ones which was supposed to save us a lot of time.

The issue now is, one of the 11 lists that, acc. to the schema, should have been there, was not - and there is no one available today to tell me whether it is usually there or not. I deactivated that particular LOAD for now so it worked, but tomorrow that file might be there again ...

A solution would be just deactivating the ErrorMode altogether, but then that work would have been to no end, I would not get any info about errors ... So I have to first find out (in a LOAD with ErrorMode=0) which of the files are there and which are not

and then LOAD (with ErrorMode=1) only those which are available.

I have already had a look at the available file_functions, but none seems to do just what I need. Can anybody help me there,l please?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
shane_spencer
Valued Contributor

Re: Find out which files exist in a directory?

The most obvious answer is that here you are using relative location

'..\99_Resources\MPL\F2\$(v_fileformat_F2)'


The ".." tells you to go up a level in Unix and Dos. You should always fully qualify your path


'\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\$(v_fileformat_F2)'


So try updating this line to read:


For each File in FileList('\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\$(v_fileformat_F2)')


If that doesn't work check you path is correct by copying the address in to explorer \\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\

If you're still having issues please upload your script / document and I'll try to run it.


Please hit the button on any of my posts that are helpful, as it will enhance my "reputation". Cheers

17 Replies
shane_spencer
Valued Contributor

Re: Find out which files exist in a directory?

Would an if command provide a solution.

i.e.

IF (NOT isNull(qvdCreateTime('F2_Montag für Mittwoch.csv'))) THEN

Highlighted
shane_spencer
Valued Contributor

Re: Find out which files exist in a directory?

Or a quick load of the first line of all available files and save the output to another file?

i.e.

for each Directory in DirList('\\Sharename\*')

for each File in FileList('$(Directory)\*fur*')

//sets the location of the files

all_file_names:

First 1 //loads the first line only for quick load

LOAD *,

'$(File)' as filename

FROM $(File);

datanibbler
Esteemed Contributor

Re: Find out which files exist in a directory?

Hi Shane,

thanks for trying to help!

I have come up with this solution:

- First I set the ErrorMode to 0 (and after this code I reset it to 1)

- Then I use this code

F2_Lists:

LOAD
     1
as List_counter,
   
FileBaseName() as Dateiname
From
[S\MPL\05-Liefertreue_PR_Listen\F2_*für*.xlsx]
;

LET Nr_F2_Tables_available = NoOfRows('F2_Lists');

For some reason, however, that does not load anything. Can you imagine why?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Oh - I just realize, that wouldn't work anyway. I'll have to load the 1st line of every table that should be there and then count the nr. of rows - that will give me an idea of how many tables are there, but not yet which ones are there and which ones are missing - but I guess I can do that.

datanibbler
Esteemed Contributor

Re: Find out which files exist in a directory?

Hi,

I have edited the code (I haven't implemented a LOOP, I'll try to do that in a second step once this works in general)

The code now reads like this - and still nothing is loaded at all, so it logically doesn't work:

SET errormode=0;
F2_Lists:
FIRST 1
   
LOAD
    1
as TableCounter,
   
FileBaseName() as Dateiname
From
[S\MPL\05-Liefertreue_PR_Listen\F2_Montag für Mittwoch.xlsx];
// concatenate automatically
FIRST 1
   
LOAD
    1
as TableCounter,
   
FileBaseName() as Dateiname
From
[S\MPL\05-Liefertreue_PR_Listen\F2_Montag für MittwochV2.xlsx];

(etc., I have this 11 times, but every reiteration looks the same, only the exact table_name changes)

Can you imagine why it isn't loading anything? It's running without an error, just reporting "... 0 lines fetched" every time.

datanibbler
Esteemed Contributor

Re: Find out which files exist in a directory?

Hahaa!

Now I've made it.

I just looked at the "real" LOAD statements and I realized I missed the > (oomxl, embedded labels) < part necessary when loading from Excel files - when loading without that, I just got illegible crap because the headers are somewhat strange.

With that, the code loaded one line per table, just as it should.

Who says it denn ;-)

shane_spencer
Valued Contributor

Re: Find out which files exist in a directory?

Should it be something like this (check if the directory name includes a ":" - ie "s:\")?

SET search='*fur*';

for each File in FileList('S:\MPL\05-Liefertreue_PR_Listen\$(search).xlsx')

//sets the location of the files

File List:

First 1 //loads the first line only for quick load

LOAD

1 as TableCounter,

'$(File)' as filename

FROM $(File);

next File

datanibbler
Esteemed Contributor

Re: Find out which files exist in a directory?

Hi Shane,

now I've got it. It's quite complex - and doesn't work on the real directory, but I guess there's something stuck in my laptop's memory, I just copied the files to another location and the code works.

I now have a variable holding the exact name of the file that is missing.

=> How can I now use this variable to deactivate that one (out of 11, still no loop) LOADs?

Best regards,

DataNibbler

datanibbler
Esteemed Contributor

Re: Find out which files exist in a directory?

Ah - I'm going to do it the other way round:

Instead of using that variable to NOT load that one file which is not there, I will build a loop to load all those files which are there.

datanibbler
Esteemed Contributor

Re: Find out which files exist in a directory?

Hi Shane,

I just realize that for what I have now - a list of the names of all the files (out of those 11) which are there, my code is quite complicated. I guess I could have that much easier by just doing what you proposed and building a loop for loading the filenames - or even one step easier still, by building a loop to load all the files - which are available - matching that file_format.

=> Is that >>> File in FileList(...) <<< just what I need to write in there? Is that the correct syntax? That point is not very precise in the inline_help.

Thanks a lot!

Best regards,

DataNibbler

Community Browser