Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jekbauita
Contributor III
Contributor III

Load data from multiple Excel files adding a custom column

Hi everybody, I'm very new to QlikView and to the forum. I tried to look around for a solution to my issue, but couldn't find any, so I'm looking forward for someone to help me.

I need to load data from 2-3 different excel files, each with the same columns.

Each files contains statistic data for a single year of sells (2013, 2014, etc.), so what I need is to also add a custom costant column for each file I'm adding that contains the year.

What I managed so far is to add the custom costant column for a single file, but I really can't reach the result to add multiple files.

Can anybody help me solve the issue?

Plese look below for the load script I'm using at the moment. Note that it adds just one file and that it's working.

Thanks in advance,

Giacomo

[Statistics]:

LOAD
[Area Commerciale],
[Zona Commerciale],
[Zona Venditore],
[Punto Vendita],
Articolo,
Bottiglie,
Confezioni,
Produttore
FROM
E:\datasource2014.xlsx
(
ooxml, embedded labels);
LOAD
*,
'2014'
as [Anno Vendita]
Resident statistics;

1 Solution

Accepted Solutions
jekbauita
Contributor III
Contributor III
Author

The propor function is FileBaseName() and not BaseFileName() as written in the code part of the reply.

It works just as neede.

Thank you very much.

-

Giacomo

View solution in original post

5 Replies
ashwanin
Specialist
Specialist

Hi,

If you have files in a folder and the name of files start with Datasource* and want to load all in same qvd the use below

[Statistics]:

LOAD
[Area Commerciale],
[Zona Commerciale],
[Zona Venditore],
[Punto Vendita],
Articolo,
Bottiglie,
Confezioni,
Produttore
FROM
E:\datasource*.xlsx
(
ooxml, embedded labels);

alexandros17
Partner - Champion III
Partner - Champion III

Try with this code,


let path = '..\..\qlik\MyFile_*.xlsx';

for each File in filelist (path)
Let Anno = left(right(File,9),4);
// Carico i dati
MyTable:
LOAD
*,
$(Anno) as Year
FROM $(File) (ooxml, embedded labels, table is Foglio1);
ENDIF
next File

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Assuming the file name is always of the format "E:\datasource*.xlsx", then this will do it:

Statistics:

LOAD

  [Area Commerciale],

  [Zona Commerciale],

  [Zona Venditore],

  [Punto Vendita],

  Articolo,

  Bottiglie,

  Confezioni,

  Produttore,

  Num(Right(BaseFileName(), 4)) As Year

FROM E:\datasource*.xlsx

(ooxml, embedded labels);

This will load all files matching the file mask. The year expression extracts the year from the base name of the file being loaded (FileBaseName()).

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jekbauita
Contributor III
Contributor III
Author

The propor function is FileBaseName() and not BaseFileName() as written in the code part of the reply.

It works just as neede.

Thank you very much.

-

Giacomo

jekbauita
Contributor III
Contributor III
Author

I've choosen @jonathan dienst's reply because, as a new user to Qlik View, I understood it better.

Thank you anyway for the effort, I will try your solution in the future.

-

Giacomo