How to remove a space from column names

[jdaniels]

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;

[rubenmarin · Accepted solution]

Hi, you can try with Trim() or RTrim().

Or an expression like:

If(Index(FieldName,' ',-1)=Len(FieldName)
,Left(FieldName,Index(FieldName,' ',-1)-1)
)