Find out which files exist in a directory?

[datanibbler]

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

[shane_spencer]

Would an if command provide a solution.

i.e.

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

[shane_spencer]

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]

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]

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]

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]

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]

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]

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]

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