topic Re: Load all csv files from a web address in QlikView

Load all csv files from a web address

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

Hello guys,

I created the following script in order to load all the excel files I have in my web address. But as you can see it's not a "clean code", onde I have to write all the Stickers I have like (B3SA3, BRAP4, WEGE3...). Stickers are the file's name.

How could I convert it to a cleaner code?

Like:
Lood all files from the following website: [http://lelli.000webhostapp.com/InvestmentsCSV/$(Sticker).csv]

Is there any way to do it?
An important point is that I really need the name of the file as a columnin my table. As you can see in the code below:

For each Sticker in 'B3SA3', 'BRAP4', 'WEGE3', 'LCAM3', 'EMBR3', 'MRVE3', 'HAPV3', 'LIGT3', 'BRFS3', 'AMAR3', 'USIM5', 'HGTX3', 'MEAL3', 'JBSS3', 'CEAB3', 'COGN3', 'AZUL4', 'GOLL4', 'PETR4', 'GGBR4', 'RENT3', 'MOVI3', 'VALE3', 'CMIG4', 'CIEL3', 'ELET6', 'CVCB3', 'IGTA3', 'CCRO3', 'DTEX3', 'CSMG3', 'CPFE3', 'ABEV3', 'LREN3', 'MRFG3', 'RLOG3', 'RAIL3', 'POMO4', 'BRDT3', 'ELET3', 'BRKM5', 'SMLS3', 'GUAR3', 'VVAR3', 'TOTS3', 'BTOW3', 'CYRE3', 'HYPE3', 'QUAL3', 'SBSP3', 'LAME4', 'NTCO3', 'UGPA3', 'VIVT4', 'ECOR3', 'SUZB3', 'TIMP3', 'KLBN11', 'GNDI3', 'MGLU3', 'YDUQ3', 'PETR3', 'FLRY3', 'CSNA3', 'RADL3', 'PCAR3', 'BRML3', 'ENBR3', 'TAEE11', 'CSAN3', 'GOAU4', 'EQTL3', 'MULT3', 'CRFB3' BalancoPatrimonial: LOAD Date(Date#(F1, 'DD/MM/YYYY')+1) as [Periodo], '$(Sticker)' as [Sticker], [Ativo Total], [Ativo Circulante], [Outros Resultados Abrangentes], [Adiantamento para Futuro Aumento Capital1] FROM [http://lelli.000webhostapp.com/InvestmentsCSV/$(Sticker).csv] (txt, utf8, embedded labels, delimiter is ',', msq, filters( Remove(Row, Pos(Top, 1)), Transpose(), Remove(Row, RowCnd(CellValue, 4, StrCnd(null))) ));

Thanks
Bruno Lelli

Re: Load all csv files from a web address

brunolelli87 — Mon, 11 May 2020 14:33:23 GMT

Any help is more than welcome guys!

Re: Load all csv files from a web address

rwunderlich — Mon, 11 May 2020 20:03:28 GMT

It's fortunate that that website will return a directory list of you don't specify a filename. So you can do something like:

Files:
LOAD
Name
FROM
[http://lelli.000webhostapp.com/InvestmentsCSV/]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1)
Where Name like '*.csv';

for i = 0 to NoOfRows('Files')
Let vFile = Peek('Name', $(i), 'Files');
// Your load code here
Next i

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Load all csv files from a web address

brunolelli87 — Tue, 12 May 2020 03:49:07 GMT

Hello!

Thanks for your attention, but it's not working.
The Table called Files: contains all csv files, and it's correct, but the when I'm asking to LOAD ALL MY FILE, the system is not even opening the CSV file to load as you can see on $SysTable 1.

What should I do?
Am I doing something wrong?

Thanks
Bruno

Re: Load all csv files from a web address

rwunderlich — Tue, 12 May 2020 17:33:18 GMT

Sorry, I should have pointed out that you should use the vFile variable in the FROM within your loop:

FROM
[http://lelli.000webhostapp.com/InvestmentsCSV/$(vFile)]
(txt, utf8, embedded labels, delimiter is ',', msq, filters

-Rob

Re: Load all csv files from a web address

brunolelli87 — Wed, 13 May 2020 16:51:09 GMT

Thanks for all your assistance.

I'm learning a lot with you guys, and your experience is very helpful!

Btw, I was thinking about my project and I believe the best thing to do is to create a list of companies on an Excel Spreadsheet and read the files based on this list.

Company	File
AZUL	AZUL4
GOLL	GOLL4

So, how can I execute a Foor Loop based on the companies of my Excel Spreadsheet?

Thank you so much for your assistance,
Best regards

Bruno Lelli