I am trying to load a file which people upload in a folder. These files have all the same folder. However, some will have quoted comma headers (e.g. "ID ", "Reference ", ...) and others will have unquoted semicolumn headers (e.g. ID; Reference; ...).
What is the easiest ways to load these files as close as possible?
I already managed to fix the different delimiters, but I am struggling with the additional spaced in the quoted version. I tried a trick with a trail variable, but that doesn't work. I could use counted columns, but since I have no guarantee that the column names come in the same order, I would like to avoid that option.
FOR EACH File in FieldValueList('FieldValueList')
Header:
First 1
LOAD [@1:n] as Header
FROM XXXXX
(fix, utf8);
Let Sep=Left(KeepChar(Peek('Header',0),',;'),1);
Let Trail = ' ';
If Sep = ',';
Trail = '';
EndIf;
QuoteHistoryDuplicate:
LOAD DISTINCT
$(='ID' & Trail) as ID,
FROM XXXXX
(txt, utf8, embedded labels, delimiter is '$(Sep)', msq);
Drop Table Header;
NEXT;
