Table A loads data from an excel file, Table B loads different data from the same excel file.
Table A contains fields with default names (@1, @2, @3, etc), table B contains the actual names for the fields in table A.
I have multiple excel files to go through, so I have the renaming process in the loop that goes through all of the my files.
Mapping load didn't work for me, so I am working on this workaround.
Within the file loop, I have the RENAME loop, which looks like this:
FOR i=0 to NoOfRows('FieldNameMap')-1
LET vNewFieldName = Peek('Field Name',$(i),'FieldNameMap');
LET vOldFieldName = '@' & ($(i)+1);
RENAME Field '$(vOldFieldName)' to '$(vNewFieldName)';
This process works fine for the first excel file; however, the second time around, I can see in the debugger that the RENAME loop takes in the correct field names, but after the loop, my table still has @1, @2, @3, etc as the names of the fields!
Another thing I should mention is that the tables are created at the beginning of the file loop, then one table is used as a resident table and then both the table and the map are dropped before the loop gets to NEXT, so I'm esentially starting with new tables every time I loop through with a new excel file.
To sum up: why do the fields keep their names after the first file is processed?
I already tried the mapping load, as I described before, but that option doesn't work for me. I am also trying to work with the rename field option, which I also described before, which is giving me a lot of trouble...
I was reading about other people having the issue of not being able to rename fields, and I was told that there might be an issue with me trying to rename a field with a name that is already used in another table. Don't see why that would be a problem, though.
I did, however, confirm that the problem is trying to rename a field to create an association in a table that has already been loaded.
My workaround, to keep the field names dynamic is to create a whole bunch of variable, which keep the new field names, then when I load the table with fields that I want to rename, I use the following:
@1 as $(v1)
This works in my case, when I can load the new field names BEFORE I load the fields that I need to rename, and of course, if I know the exact number of fields that I will be renaming.