Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load from URL (results excell with multiple sheets)

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...

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.

2 Replies
marcus_sommer

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

noman212
Creator III
Creator III

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;