topic Re: Changing file path of data source (xlsx) in Connectivity & Data Prep

Changing file path of data source (xlsx)

canerkan — Wed, 14 Nov 2018 16:05:15 GMT

Hi everyone! I want to put my data sources from my C drive on the server of our company. Unfortunately I don't get to change the file path for my sources in my app. Can anyone help me? I guess I should be able to do that with the data editor but as i don't really know how to use it, I am afraid to damage anything. I am using Qlik Sense Desktop. Thank you very much in advance!

Re: Changing file path of data source (xlsx)

Thiago_Justen_ — Wed, 14 Nov 2018 17:26:12 GMT

I'm not sure if it will work for you, but think about it...

Cheers

Re: Changing file path of data source (xlsx)

acbishop2 — Wed, 14 Nov 2018 20:21:24 GMT

It really isn't that difficult to change the file path, so if you have access to the data load editor, go for it!

First, if you don't already have Qlik looking at the location on your server where the file will be, you'll have to create that connection. Do so by clicking on "Create new connection" at the top right of the data load editor and choose the file path by clicking on "All files." You'll have to give your connection a name so that it can be referenced in the load script.

Once that's created, you should be able to just go to the part of the script that is looking at the file and change whatever is between "lib://" and your file name, e.g. if your current connection looks like this:

[Data]:
LOAD
[Column 1],
[Column 2]
FROM [lib://OldConnectionName/FileName.xlsx]
(ooxml, embedded labels, table is [Data]);

then you'll just need to change the part in bold below:

[Data]:
LOAD
[Column 1],
[Column 2]
FROM [lib://NewConnectionName/FileName.xlsx]
(ooxml, embedded labels, table is [Data]);

Qlik will now be looking for your file in the new folder you pointed it to (with the connection you set up). Hope this helps!

Re: Changing file path of data source (xlsx)

acbishop2 — Wed, 14 Nov 2018 20:22:08 GMT

It really isn't that difficult to change the file path, so if you have access to the data load editor, go for it!

[Data]:
LOAD
[Column 1],
[Column 2]
FROM [lib://OldConnectionName/FileName.xlsx]
(ooxml, embedded labels, table is [Data]);

then you'll just need to change the part in bold below:

[Data]:
LOAD
[Column 1],
[Column 2]
FROM [lib://NewConnectionName/FileName.xlsx]
(ooxml, embedded labels, table is [Data]);

Qlik will now be looking for your file in the new folder you pointed it to (with the connection you set up). Hope this helps!

Re: Changing file path of data source (xlsx)

canerkan — Thu, 15 Nov 2018 08:27:35 GMT

Thank you! I won't be able to try this solution until tomorrow but i'll come back at you after i tried it.

Have a good one

Re: Changing file path of data source (xlsx)

canerkan — Mon, 19 Nov 2018 08:35:40 GMT

Hi Thiago_justen,

thank your for your data. I tried to unpack it but unfortunately the IT in my company didn't let me download a programm to unpack such files. Would you be as kind as to upload it as a .zip file (if possible). I am able to unpack these without needing another programm. You would help me a lot!

Thanks again,

Can

Re: Changing file path of data source (xlsx)

Thiago_Justen_ — Mon, 19 Nov 2018 10:32:55 GMT

Here it is. If you have any doubts, please let me know.

Cheers

Re: Changing file path of data source (xlsx)

canerkan — Mon, 19 Nov 2018 11:39:36 GMT

Hi Thiago_justen,

Your gif was great! But I'm afraid this goes right over my head.. I don't understand why I need a Connection for a Transformation. Can you explain me, what is getting transformed?

I can see at the LOAD_TABLES Statemant, that you are loading a .txt file.. In my case it is a .xlsx file.. Do I stil need to write

(txt, utf8, no labels, delimiter is '\t', msq);

under FROM [$(vLoad)/... or should it be

(xlsx, utf8, no labels, delimiter is '\t', msq); ?

I guess, after I understand more of it, there will be coming a few more questions..

I am very happy, someone's helping me. So thank you

Re: Changing file path of data source (xlsx)

Thiago_Justen_ — Mon, 19 Nov 2018 11:44:36 GMT

If you have no transformation folder, then you may not create that connection. In your case you don't need to write (txt, utf8, no labels, delimiter is '\t', msq);.

Keep your from string just the way it is.

Cheers

Re: Changing file path of data source (xlsx)

canerkan — Mon, 19 Nov 2018 12:29:59 GMT

Ok, i understand it.. Could you explain me what kind of file should be loaded in the TRANSFORM_TABLE Statement?

Re: Changing file path of data source (xlsx)

Thiago_Justen_ — Mon, 19 Nov 2018 12:38:11 GMT

Do you make any transformation in the data you have? If yes, the table transformed should be there in the TRANSFORMA_TABLE statement.

Re: Changing file path of data source (xlsx)

canerkan — Mon, 19 Nov 2018 12:53:44 GMT

No, i don't make any transformations..

Would it be enough to copy the Script from the automatically generated Script and to just change the file path into the one you showed me?

Re: Changing file path of data source (xlsx)

Thiago_Justen_ — Mon, 19 Nov 2018 13:50:29 GMT

I recommend you follow these steps:

- Create a new app and rename it ;

- Then, follow the steps that I've present in a file sent you ( .gif);

If you have any trouble, please let me know.

Re: Changing file path of data source (xlsx)

canerkan — Mon, 19 Nov 2018 16:07:16 GMT

Alright, I'll try it tomorrow. I am comming back at you after it.

Thanks alot, Thiago_justen!

Re: Changing file path of data source (xlsx)

canerkan — Tue, 20 Nov 2018 12:42:48 GMT

Hi Thiago_justen!

I tried it and it worked! Many thanks to you!

As I was writing the script to upload my data, I found out that it probably isn't possible to upload data from other drives but your C: drive since you couldn't choose any other.. or, at least, it won't be done easily..

Again thank you for helping me so patiently, Thiago!

Have a nice day!

Re: Changing file path of data source (xlsx)

Thiago_Justen_ — Tue, 20 Nov 2018 20:55:23 GMT

Don't mention it.

Best Regards!