RE:Multi load with names

[Chanty4u]

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

[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)

     ;

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

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 · Accepted solution]

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"