Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sureshqv
Esteemed Contributor III

RE:Multi load with names

Hi all ,

I have a folder with 5 excels   files . i want to load  all into single table

but my requirment is  to load   excel filename as a column name in  result table.

Example.:  below data i  i have in 5 excel files   .Filename is  [ 20160101 .xlsx   ,20160102.xlsx ..... ]

Tags (2)
1 Solution

Accepted Solutions

Re: RE:Multi load with names

For your excel file as  seen check this code

LOAD Account,
OB,
Cr,
DB,
Date(Date#(FileBaseName(),'YYYYDDMM'),'YYYYDDMM') AS Date
FROM
[*.xlsx]
(
ooxml, embedded labels, table is Sheet1);

Date field as Gysbert suggest you.

Regards

Anand

8 Replies

Re: RE:Multi load with names

Check this link out:

Loops in the Script

Re: RE:Multi load with names

LOAD

     *,

     Date(Date#(FileBaseName(),'YYYYDDMM'),'YYYYDDMM') as Date

FROM

     *.xlsx (ooxml, embedded labels, table is Sheet1)

     ;


talk is cheap, supply exceeds demand

Re: RE:Multi load with names

Load your data like this

Load

*,

FileBaseName() AS Date

From <Excel Source>;

Load

*,

FileBaseName() AS Date

From <Excel Source Second File>;

Regards

Anand

sureshqv
Esteemed Contributor III

Re: RE:Multi load with names

thanks  . but in future  some more files will be adding to the folder  so is this will work?  or any other way to do this?

Re: RE:Multi load with names

If your excel file name column name are same and from single excel sheet  ie. Sheet1 then try this code

LOAD *
FileBaseName() AS Date
FROM

(
ooxml, embedded labels, table is Sheet1);

Regards

Anand

Re: RE:Multi load with names

Slightly unrelated, but I am seen HIC saying that it is better to use loops instead of wildcard to load multiple files because if you are using preceding load, wildcard won't work the way you would expect it to

Re: How to load from multiple text files

Re: RE:Multi load with names

For your excel file as  seen check this code

LOAD Account,
OB,
Cr,
DB,
Date(Date#(FileBaseName(),'YYYYDDMM'),'YYYYDDMM') AS Date
FROM
[*.xlsx]
(
ooxml, embedded labels, table is Sheet1);

Date field as Gysbert suggest you.

Regards

Anand

Re: RE:Multi load with names

One further advantage of using a loop rather than a wildcard to load multiple files, is that you can add a trace statement in the loop to include the source file in the log, so if an error occurs, the file being loaded can be identified.

With a wildcard, the log file does not show which file is being loaded it just shows "file*.xls"

Community Browser