topic Re: Load Data using GetfolderPath() function in QlikView

Load Data using GetfolderPath() function

vittorianad — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I'm trying to load data from files stored into a server shared directory, whose path is clearly differentent from user to user.

I'm actualy using this:

DIRECTORY ;

for each File in filelist('*.xlsx')

LOAD *

FROM

[$(File)]

(ooxml, embedded labels, header is 1 lines, table is Contatti);

next File;

it works... ok

The problem is that one of my collegue has to change the directory to it's own path

DIRECTORY ;

to make it works.

Is there any set of instructions to set the path dinamicaly?

I tried with

load

Getfolderpath('DashBoard') as dropBox

AutoGenerate 1;

DIRECTORY [$(dropBox)\Contatti\Piani];

but doesnt'work...

any suggestion?

thanks to all

Re: Load Data using GetfolderPath() function

ali_hijazi — Thu, 08 Feb 2018 07:15:31 GMT

my_table:

load

Getfolderpath('DashBoard') as dropBox

AutoGenerate 1;

let vDropBox = peek('dropBox',0);

drop table my_table;

DIRECTORY [$(vDropBox )\Contatti\Piani];

....

Re: Load Data using GetfolderPath() function

vittorianad — Thu, 08 Feb 2018 07:30:59 GMT

HI Ali,

it return me

\\server\user\v.dambruoso\Documents

instead of

C:\Users\v.dambruoso.MANGINI\Dashboard

why?

what can I do?

Re: Load Data using GetfolderPath() function

ali_hijazi — Thu, 08 Feb 2018 07:50:33 GMT

may you please show me what you wrote in your script?

Re: Load Data using GetfolderPath() function

vittorianad — Thu, 08 Feb 2018 09:04:23 GMT

hi,

myTable:

load

Getfolderpath('Dropbox') as dropBox

AutoGenerate 1;

let vDropBox = peek('dropBox',0);

drop table myTable;

DIRECTORY [$(vDropBox)\Contatti\Piani];

I think the problem is on the Dropbox folder, with is on my local user profile.

QlikView search in \\server\user\v.dambruoso\Documents .... !!

Is there a function to get the system users to buid the path, or is there a way to choose from a list of path and reload the data after?

Thank you

Re: Load Data using GetfolderPath() function

ali_hijazi — Thu, 08 Feb 2018 09:20:47 GMT

you got the same folder structure on your local machine and on the server

if yes then you can use relative paths

relative to the QVW file in which you have the script

suppose your folder structure is as follows:

Application (main folder)

DropBox (sub-folder) ->Contatti->Piani (2 sub folders)

QVW (subFolder)

suppose your file (qvw) is inside a sub-folder under QVW folder called myApp

then you can access the xml file as ..\..\DropBox\Contatti\Pianni\your_xml_file

hope this helps

Re: Load Data using GetfolderPath() function

vittorianad — Thu, 08 Feb 2018 09:48:31 GMT

I tried with this, but I didn't sorted out.

I got the solution mean time..

I used this

myUser:

load

'C:\Users'&Mid(OSUser(),Index(OSUser(),'\'))&'.'&left(OSUser(),Index(OSUser(),'\')) as User

AutoGenerate 1;

let vUser = peek('User',0);

drop table myUser;

DIRECTORY [$(vUser)Dropbox\Contatti\Piani];

it works, but now the myUser table is not really dropped and the load statement after defined loads data into this table

Re: Load Data using GetfolderPath() function

ali_hijazi — Thu, 08 Feb 2018 10:33:09 GMT

can you show me in some way the folder structure of your application

may I can figure out

Re: Load Data using GetfolderPath() function

marcus_sommer — Thu, 08 Feb 2018 11:54:10 GMT

To get the user you could try it with: osuser() or getregistrystring().

- Marcus

Re: Load Data using GetfolderPath() function

vittorianad — Thu, 08 Feb 2018 13:37:52 GMT

"Cruscotto_Piani_Commerciale" is the dashboard I'm working on.

"Piani" is the folder I use to store xlsx files.

and both are on Dropbox folder

Re: Load Data using GetfolderPath() function

ali_hijazi — Thu, 08 Feb 2018 13:57:44 GMT

then you can directly read from xlsx in the script as follows:

load *

from Piani\name_of_excel_file

you don't need to go to root then access folder; it is already near the qvw file

Re: Load Data using GetfolderPath() function

richard_blakemore — Fri, 09 Feb 2018 03:42:39 GMT

I use a variable set up i.e.

Set vUser= 1; // 1 = Bob, 2 = John

if $(vUser) = 1

Let vFolderPath = '\\Bob Server\Bob directory\';

ElseIf $(vUser) = 2

Let vFolderPath = '\\John Server\John directory\';

End If

Then I make sure that everywhere that uses a file path uses $(vFolderPath) instead.

All the user has to do is make sure that the vUser value is changed prior to reloading.