topic Re: Load from URL (results excell with multiple sheets) in QlikView

Load from URL (results excell with multiple sheets)

Wed, 13 Aug 2014 21:50:54 GMT

Hi,

I need to load data from a URL that results in a excel file with multiple sheets

https://www.keysurvey.com/servlet/ExportResults?SurveyID=682896&ReportID=623016&pw=29f37d09&exportTo=1&labelsValues=1

Each row holds the answer of a person. The code currently has only brings the information from the first sheet, but I'm missing the responses of sheets 2, 3, and 4.

CUESTIONARIO_RESPUESTAS_TMP: //:: Se extraen las respuestas de cada archivo fuente

CrossTable(SUBPREGUNTAS, RESPUESTAS, 4)

LOAD '$(vMODULO)' AS MODULO, '$(vURL)' as URL, '$(vIdCatalogo)' as ID_CATALOGO, *

FROM [$(vURL)]

(biff, embedded labels, header is 2 lines, table is [Results$], filters(

Remove(Col, Pos(Top, 12)), Remove(Col, Pos(Top, 11)), Remove(Col, Pos(Top, 10)), Remove(Col, Pos(Top, 9)),

Remove(Col, Pos(Top, 8)), Remove(Col, Pos(Top, 7)), Remove(Col, Pos(Top, 6)), Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 4)), Remove(Col, Pos(Top, 3)), Remove(Col, Pos(Top, 2)), Remove(Col, Pos(Top, 1)),

Transpose(), Replace(1, top, StrCnd(contain, 'COMENTARIOS', case)),

Replace(1, top, StrCnd(contain, 'COMENTÁRIOS', case)), Replace(1, top, StrCnd(null)),

Replace(2, left, StrCnd(null)),

Transpose(), Remove(Row, Pos(Top, 1)) //:: Transformaciones de la fuente para extraer con una estructura predeterminada

));

Greetings.

Re: Load from URL (results excell with multiple sheets)

marcus_sommer — Fri, 15 Aug 2014 09:13:23 GMT

For a normal filename you could use a wildcard to loop through severals files but this didn't worked with sheets inside a xls. Therefore you need some loop-logic for the sheets - have a look on these example: Load Multiple excel sheets using For loop

- Marcus

Re: Load from URL (results excell with multiple sheets)

noman212 — Fri, 15 Aug 2014 10:14:05 GMT

For each vtxtPath in filelist ('*.txt')

TextFile:

LOAD Dept.,

[User ID],

Name,

[Enroll ID] ,

[Device ID],

Place,

[Clock Time],

[Att. Type],

Remark

FROM

$(vtxtPath)

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Next vtxtPath;