Group of excel files with same data, but not necessarily all fields in common
Hi, I have these excel sheets that are being autogenerated such that if a field has no values in it, it will not appear on the sheet. Otherwise, the fields are all the same. In this case, the 'followup promised' field is the only one that seems to be populated for some sheets and not populated (and thus not appear) on others, and it is tripping the load when I try to load from *.xlsx with field not found errors.
I tried a concatenate load to see if that would help get around it, but it still trips. Is there something I can do, besides loading the files with the field in one batch and without the field in another batch? (Loading in separate batches would require me to check each file as they come in, and keep me from 100% automation,) Here's my script:
LOAD ApplyMap('ClientNameMap', F1) as Skill,
[Waiting on External],
[Waiting on Internal],
[Waiting on Tier 2],
[Waiting on User],
left(Right(Filename(),15),4) as FileYear,
left(Right(Filename(),22),2) as FileDay,
ApplyMap('MonthMap', left(Right(Filename(),19),3)) as FileMonth,
ApplyMap('MonthMap', left(Right(Filename(),19),3))&'/'&left(Right(Filename(),22),2)&'/'&left(Right(Filename(),15),4) as FileDate,
Re: Group of excel files with same data, but not necessarily all fields in common
Thanks, I didn't have time to test but as I was realizing another solution, I realized your solution was it. I recognize the hack of loading one file, then concatenate loading all the rest to get around the differing fields and the subsequent let and if statement for the field in question look straight to me. Thanks again!
PS> Boss said that field is unneeded, so that with 9pm on a Friday told me no more need to test. If anyone comes back and says your answer is wrong, I can remove the correct answer credit, but it really does look straight to me.