Load all csv files from a web address

[brunolelli87]

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

[brunolelli87]

Any help is more than welcome guys!

[rwunderlich]

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

[brunolelli87]

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

[rwunderlich]

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

[brunolelli87]

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