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: 
Chanty4u
MVP
MVP

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

1 Solution

Accepted Solutions
its_anandrjs

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

View solution in original post

8 Replies
sunny_talwar

Check this link out:

Loops in the Script

Gysbert_Wassenaar

LOAD

     *,

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

FROM

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

     ;


talk is cheap, supply exceeds demand
its_anandrjs

Load your data like this

Load

*,

FileBaseName() AS Date

From <Excel Source>;

Load

*,

FileBaseName() AS Date

From <Excel Source Second File>;

Regards

Anand

Chanty4u
MVP
MVP
Author

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?

its_anandrjs

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

sunny_talwar

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

its_anandrjs

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

Colin-Albert

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"