Loading period field from exel header (multiple files)

[Meg00]

Hi!

I have the following problem:

I have to load data from multiple excel files (one file is created each period).

The period field is presented in a single cell in the header of each excel file. I want to add the field on every row.

Syntax now:

[Table 1]
LOAD *
filename() as file
FROM $(vG.ImportPath)10 *.csv
(txt, codepage is 28591, no labels, delimiter is ';', header is 4 lines);

//---------------------------------------------------------------------------------------------
JOIN ([Table 1])
LOAD
@2 AS Period,
filename() as file
FROM $(vG.ImportPath)10 *.csv

(txt, codepage is 28591, no labels, delimiter is ';', header is 1 lines)
WHERE(RowNo() < 1 );

drop Field file;

With only one file it worked. Unfortunately, as soon as I add more than one file in the Import folder it doesn't work and many rows are left without Period.

[jonathandienst · Accepted solution]

Using a wildcard load within a JOIN does not work well. Load into a temp table and then join that:

T_Period:
LOAD @2 AS Period,
	filename() as file
FROM $(vG.ImportPath)10 *.csv
(txt, codepage is 28591, no labels, delimiter is ';', header is 1 lines)
WHERE(RowNo() < 1 );

JOIN([Table 1])
LOAD * Resident T_Period;

DROP Table T_Period;

[beck_bakytbek]

Hi Meg00,

check this, i hope that helps:

https://www.youtube.com/watch?v=liIEUsZJhvk

[Meg00]

Worked perfect!

Thank you for your quick response!!