Loading multiple files having same structure (except for a column)

[SerSwagster]

Hi everyone,

I'm loading multiple files of a folder with this method:

For each vFileName in Filelist ('hello_*.xlsx')

LOAD

id,

name,

address,

.

.

.

Next vFileName

These files have the same structure, except for one column, "phone_number": some files have this column, others not. If I put phone_number as a loaded column, I have an error. Since I can't modify source files and add phone_number column to those missing, How can i behave? Is there a syntax like: If(filename = 'hello_1', phone_number, null)?

My goal is to load phone_number column if it is already in the file, otherwise to create the phone_number column and set it to a default value for files missing it.

Thank you in advance.

[SerSwagster]

Hi, I don't understand how should I load phone_number column, that is missing in some input files and so it return me the error. I'm trying to follow your method in this way:

A:
LOAD * INLINE [

id, name, address, phone_number

];

For each vFileName in Filelist ('hello_*.xlsx')

User:

LOAD

id,

name,

address,

phone_number

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

I can't reach the last step of your method because phone_number column sometimes is missing. How can I overtake this issue?

[mikaelsc · Accepted solution]

A:
LOAD * INLINE [

id, name, address, phone_number

];

For each vFileName in Filelist ('hello_*.xlsx')

concatenate(A)

LOAD

*

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

A_Reworked: 

load 

alt(phone_number,'555-555-...')  as FinalPhoneNo,

id,

name,

address

resident A; 

drop table A;

[SerSwagster]

Yes, it is just what i wanted!!!

One last thing: i had another field common to every sheet (that i left out in the previous messages), which is insert_date. This is the code I used for it:

date(date#( LEFT(insert_date,10) & ' ' & mid(insert_date,12,8),'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') as insert_date

If I insert this code in the table A_reworked, the field is not recognized as a date, while using it in a simple LOAD it works. Can you tell me why?

[mikaelsc]

no idea... maybe one occurence not exactly in that format? 

also, there's a timestamp#() function that should do what you want: 

timestamp(timestamp#(insert_date,'YYYY-MM-DD hh:mm:ss'))