topic Re: Renaming CSV files using variables in QlikView

Renaming CSV files using variables

Fri, 26 Feb 2016 13:35:01 GMT

Hello everyone,

I am working in a project where the database are CSV files and it is updated year by year.

The CSV name files are described as follow:

Report.xlsx

Report 2014.xlsx

Report 2015.xlsx

Report 2016.xlsx

I need QlikView LOAD all the files above and when the 2017 file is included in the folder, it will be automatically loaded.

I could easily use * in the name of the file, but i am loading my database from a web repository, so my FROM command is an URL and it is not possible to use * in the file name like that:

LOAD *

FROM

[http://la2.collaboration..com/team/20124c20/Documents/Report*.xlsx]

In that case, anyone could help me to solve that using variable?

Regards,

Re: Renaming CSV files using variables

Nicole-Smith — Fri, 26 Feb 2016 21:52:28 GMT

What about something like this?

FOR i=2014 TO year(today())
     LOAD *
     FROM [LINK/Report $(i).xlsx];
NEXT

SET i =;

Re: Renaming CSV files using variables

Mon, 29 Feb 2016 21:17:20 GMT

Hello Nicole,

Thanks for your tip.

The script was executed with no errors, but it just loaded the spreadsheet from 2014.

I need that files from all years be loaded.

Thanks,

Re: Renaming CSV files using variables

petter — Mon, 29 Feb 2016 21:22:54 GMT

Are you sure you didnt get all the years in? They would be stored in one single table - they would be auto concatenated by QlikView during load since the column names would be similar...

Re: Renaming CSV files using variables

Nicole-Smith — Mon, 29 Feb 2016 21:26:18 GMT

They should all be in there. Are the files definitely named the same except for the year?

Re: Renaming CSV files using variables

Tue, 01 Mar 2016 14:44:52 GMT

I have checked the data and unfortunately it was loaded only info from 2014.

The column names are exactly the same in all the spreadsheets, the only difference is in the name of the files.

I didn´t understand what this code is for "SET i =; "

What is intended to be done here?

Re: Renaming CSV files using variables

Tue, 01 Mar 2016 14:47:14 GMT

Yes Nicole, i have checked again, and the File name are the same, except for the year, see the print screen below:

PS: I used the name "Report" just as an example.

Re: Renaming CSV files using variables

daveamz — Tue, 01 Mar 2016 15:06:45 GMT

Hi Rodrigo,

Try to convert the spaces in %20 -> http://www.w3schools.com/tags/ref_urlencode.asp

...

FROM [LINK/Indicadores%20Talent%20%S%E3o%20Paulo$(i).xlsx] (here I converted ã as well)

Check the actual link to the file.

Regards,

David

P.S. SET i=; deletes the variable as it is no longer needed. Similar with LET i=Null();

Re: Renaming CSV files using variables

rwunderlich — Tue, 01 Mar 2016 17:00:35 GMT

Look in the document log. How many times was the LOAD statement executed?

-Rob

Re: Renaming CSV files using variables

Tue, 01 Mar 2016 17:31:36 GMT

Hi David!

Thanks for your answer.

The spaces are already with %20... I converted ã to %E3 but nothing changed.

Re: Renaming CSV files using variables

Tue, 01 Mar 2016 17:35:18 GMT

Hi Rob,

I am trying to LOAD 2 tables from different tabs of the same spreadsheet.

Both LOAD statement was executed only one time.

Take a look the log attached.

Regards,

Re: Renaming CSV files using variables

rwunderlich — Tue, 01 Mar 2016 18:53:54 GMT

Rodrigo,

It looks like you may have left out the NEXT keyword. Look back at Nicole's example to see that you need a NEXT statement to close the loop.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Renaming CSV files using variables

Anonymous — Tue, 01 Mar 2016 18:58:42 GMT

Or to do it bluntly maybe try a wildcard *

LOAD * FROM [LINK/Report*.xlsx] ;

Re: Renaming CSV files using variables

Tue, 01 Mar 2016 19:04:48 GMT

You are right Bob!

I forgot the NEXT statement!

Thanks for the note!