Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have Multiple files in directory.Say file per day.
And there is a change in the Source file structure.Additional fields has been added. From say starting of Apr 3rd.
I have to handle this while reading it self, since while Load instead of * field names are used.
Any suggestions on how to concatenate data from Apr 3rd. File names has date on it as well.
If you do something like below, you can iterate through excel files and still use LOAD *. However, it will be obvious after you do this that the "additional" field values will be null for rows that came from files that did NOT contain the "additional" fields in them.
MyFinalTable:
LOAD * inline [
dummyfield
]
;
FOR EACH vFile in FileList('c:\MyDataFiles\MyDataFile_*.xlsx') // where filename is MyDataFile_<YYYYMMMDD>
Concatenate (MyFinalTable)
LOAD
*,
'$(vFile)' as SourceFile
FROM
$(vFile)
(ooxml, embedded labels, table is Sheet1);
NEXT
DROP FIELD dummyfield from MyFinalTable;
In these cases you couldn't use a wildcard-load anymore and needed to change to load your data within a filelist-loop and force within them a concatenation:
Re: Load multiples files with different headers
- Marcus
If you do something like below, you can iterate through excel files and still use LOAD *. However, it will be obvious after you do this that the "additional" field values will be null for rows that came from files that did NOT contain the "additional" fields in them.
MyFinalTable:
LOAD * inline [
dummyfield
]
;
FOR EACH vFile in FileList('c:\MyDataFiles\MyDataFile_*.xlsx') // where filename is MyDataFile_<YYYYMMMDD>
Concatenate (MyFinalTable)
LOAD
*,
'$(vFile)' as SourceFile
FROM
$(vFile)
(ooxml, embedded labels, table is Sheet1);
NEXT
DROP FIELD dummyfield from MyFinalTable;