Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
NelsonZyon
Contributor
Contributor

Routine to extract data from several xlsx files

I have a file that contains several xlsx spreadsheets I intend to create a routine that takes each file, reads it and stores it in QVD and the name of the QVD will be the name of the file. I created the following script, but it's giving NULL, it doesn't give an error, but it doesn't extract any xlsx. Someone can help me?

 

set vTableName = '';

For Each File In FileList ('$(vCaminhoOrigemDadosOi)/*.xlsx')

// LET vTableName = subfield(mid('$(File)', index('$(File)', '\',-1) +1), '.xlsx', 1);

LET vTableName = subfield(mid('$(File)', index('$(File)', '\',-1) +1), '.xlsx', 1);

// SubField(SubField(vArquivo, '\', -1), '.'&vExt, 1)
LET size = filesize('$(File)');

if not isnull(size) then

[$(vTableName)]:
LOAD [NUM. CHAMADO]&'|'&[DATA SERVICO]&'|'&HORA as Key,
[TIPO REG.],
[DESCRICAO TIPO],
[COD. FILIAL],
REFERENCIA,
[CNPJ-CPF],
CONTRATO,
AGRUPADOR,
TELEFONE,
NOME,
[COD. SERVICO],
[DESCRICAO DO SERVICO],
[NUM. CHAMADO],
[DATA SERVICO],
HORA,
DURACAO,
DUR.TARIFARIA,
[VALOR LIQUIDO],
[VALOR BRUTO],
[CNL-ORIGEM],
DEG.TARIFARIO,
[GRUPO HORARIO],
[EOT-ORIGEM],
[EOT-DESTINO],
CARRIER,
TRAFEGO,
CICLO
FROM
[$(vCaminhoOrigemDadosOi)\$(vTableName)].xlsx
(ooxml, embedded labels, table is Plan1);

Store [$(vTableName)] into $(vCaminhoDestino)\[$(vTableName)].qvd(qvd);

drop Table [$(vTableName)];

END IF

Next File;

Labels (1)
0 Replies