topic Re: Find out which files exist in a directory? in QlikView

Find out which files exist in a directory?

datanibbler — Thu, 28 Aug 2014 13:47:14 GMT

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

Re: Find out which files exist in a directory?

shane_spencer — Thu, 28 Aug 2014 13:50:23 GMT

Would an if command provide a solution.

i.e.

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

Re: Find out which files exist in a directory?

shane_spencer — Thu, 28 Aug 2014 13:59:11 GMT

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);

Re: Find out which files exist in a directory?

datanibbler — Thu, 28 Aug 2014 14:15:53 GMT

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.

Re: Find out which files exist in a directory?

datanibbler — Thu, 28 Aug 2014 14:35:55 GMT

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.

Re: Find out which files exist in a directory?

datanibbler — Thu, 28 Aug 2014 14:57:50 GMT

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 😉

Re: Find out which files exist in a directory?

shane_spencer — Thu, 28 Aug 2014 14:59:15 GMT

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

Re: Find out which files exist in a directory?

datanibbler — Thu, 28 Aug 2014 16:00:29 GMT

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

Re: Find out which files exist in a directory?

datanibbler — Fri, 29 Aug 2014 06:36:42 GMT

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.

Re: Find out which files exist in a directory?

datanibbler — Fri, 29 Aug 2014 06:57:12 GMT

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

Re: Find out which files exist in a directory?

shane_spencer — Fri, 29 Aug 2014 07:19:53 GMT

Yeah, just use the code I provided above but instead of loading just the First 1 do a Load * of the files. To give more specific advice I would have to see your raw data and script but if all the files are the same you can load them using a Loop. If they are difference then us the If command above to load each file in turn only if it exists and use the Loop to create a log file with a list of the file names.

p.s. Don't forget to "Like" my comments if they've been helpful.

Re: Find out which files exist in a directory?

datanibbler — Fri, 29 Aug 2014 08:31:59 GMT

Hi Shane,

one more question:

The code is now finally working fine - I cannot check the exact figures, but it looks ok - just the same as the old "Wildcard-LOAD".

The remaining issue is, when I fill in the original directory where those lists usually are (into that FileList, I will post it), then nothing happens - the code is just not executed, the log continues straight away with the next table in line.

Yesterday I copied those lists to a directory on the QlikView_server that I can access - there it works fine and all the information is in the log.

(the access, though, is not an issue since the reloading is being done by a "QlikView_user" who does have access to the original directory.

The original code (which used to work, but gave me no detailed debugging information) looked like this:

BMW_Bedarfe:

LOAD

[list_of_fields]
FROM
[\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\F2_*für*.xlsx]
(ooxml, embedded labels);

The new code (which works, but not out of that directory) looks like this:

v_fileformat_F2 = '*für*';
For each File in FileList('..\99_Resources\MPL\F2\$(v_fileformat_F2)')

BMW_Bedarfe:
LOAD
[list_of_fields]
FROM
$(File)
(ooxml, embedded labels);

Next File

The code, such as I would like to use it (but which, for some reason, doesn't yet work) only differs in the first line, so I post that here:

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

Can you make a rhyme of this?

Thanks a lot!

Best regards,

DataNibbler

Re: Find out which files exist in a directory?

shane_spencer — Fri, 29 Aug 2014 08:47:05 GMT

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

Re: Find out which files exist in a directory?

datanibbler — Fri, 29 Aug 2014 09:09:33 GMT

Hi Shane,

relative or absolute path actually makes no difference - all the paths in the "live" apps are absolute - but when I replace that

>> '..\99_Resources_MPL\F2' <<

>> '\\rgb1app202\production\resources' <<

it works just the same

<=> but it does not work with the original directory.

I have already posted my code. It just lacks some sample files - but that should be easy. I can make you some lists matching this name_format with something in them.

Thanks a lot!

Best regards,

DataNibbler

Re: Re: Find out which files exist in a directory?

shane_spencer — Fri, 29 Aug 2014 09:57:46 GMT

QlikView can be very fickle but I've got this working. It creates two tables, one from you files and the other a list of files loaded.

Re: Find out which files exist in a directory?

datanibbler — Fri, 29 Aug 2014 10:05:06 GMT

Cool!

If I understand that correctly, that >directory< command tells QlikView in which directory to look for that FileList in the following LOAD_statement.

I'll try this.

P.S.: One thing is not quite clear to me, though: The order - does that >directory< command have to go before or after the >For each ...< command? You seem to have that >For each ...< twice, with the >directory< inbetween, that confuses me ...

P.P.S.: And one more thing: Can I somehow reset that >directory< command? Apparently, there were some relative paths in the tables loaded afterwards, and they got mixed up with that and broke. I'm replacing them with absolute paths now. Still ...

Re: Find out which files exist in a directory?

shane_spencer — Fri, 29 Aug 2014 13:03:18 GMT

Before. You tell QlikView which directory to start in then the For Each actions that directory.

Replace the relative paths with absolute paths, it's a much better practise.

Re: Find out which files exist in a directory?

datanibbler — Fri, 29 Aug 2014 13:37:38 GMT

Hi Shane,

now this is solved at long last. In the meantime, we had a complete Crash - nothing loaded anymore, the automated reload_schedule went on strike and I couldn't start anything manually in QMC, either - then my boss restarted the QDS_service and all was well again 😉 We had that a few times before, but twice today - that should just not happen.

Now, however, the code executes fine.

So this chapter is finally closed.

Have a nice weekend!