Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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;
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?
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'))