Hi @gtrathen

As mentioned in the Excel reader documentation, setting the Field Names Row value to 0 (or leaving it blank) will set column names to column letters.

I hope this information helps.

Hi @debbiatsafe thanks for the reply! The field names row is defined per named sheet in the file as far as I can tell - so if I set the value to 0 and run it for a series of sheets it works for the first file (and any successive file which has a sheet named the same thing) but for any other sheets that weren't in the original definition it doesn't apply. So in the image below - where the sheets are CP4, CP5, CP6 in the file used for definition of parameters, the next might be CP1, CP2, CP3 - and for those sheets which weren't covered by the parameters, it reverts to using row 2 for the field names.

Hi @gtrathen

Try setting the Additional File Settings in the Multiple File Parameters section of the Excel reader parameter to 'Use Current Settings'. By default, this parameter is set to 'Use FME Default Settings'.

Hi @debbiatsafe, I get the same outcome with that toggled on - if you'll excuse my crude MS paint editing:

I wasn't able to replicate the behaviour you are seeing using 2020.0 (build 20200). What version of FME are you using? In addition, are you able to share your workspace and some sample data?

Thanks again for taking a look at this.

I'm using 2019.1.3.1 (build 19643). I've attached a stripped down workspace with a creator and two feature readers (dir/file path and then Excel) in the configuration I was using. I've also attached two sample files which are representative of the input files I am dealing with.

For me - the attributes for Sample_1 come out as B,C,D,E,F,G,H but for Sample_2 they come out as B,C,3,4,5,G,9 - taking the numeric values from the first row.

excel.fmwSample_1.xlsxSample_2.xlsx

Hi @gtrathen

Thank you for providing the data and workspace. I believe you are seeing different behaviour due to using a FeatureReader--I was using a regular reader.

I haven't been able to get the behaviour of the regular Excel reader within a FeatureReader in regards to the Additional File Settings parameter. I will file a bug report for this issue.

As a workaround, would you consider using the Excel reader instead? I see you're using a PATH FeatureReader to filter for Excel files. This can also be done within the Excel reader itself using the Select Multiple Folders/Files option.

Hi @debbiatsafe - apologies I should have mentioned I was using FeatureReaders in the original question.

Using Excel readers should do the trick if they don't suffer the same issue, but I also found a workaround that is suitable in this specific instance while I was playing around.

Because the numeric attribute names that sneak in are always sequential the attributes can be sorted and then renamed with a PythonCaller modified from another question here.

Resulting in:

The output is slightly misleading as "XL_A" may not actually be column A but it is at least consistent across every file and sheet that the FeatureReader encounters. If the column names could not be sorted then the above could not be guaranteed to work.

Again, thank you very much for all your help!

Hi @debbiatsafe thanks for the reply! The field names row is defined per named sheet in the file as far as I can tell - so if I set the value to 0 and run it for a series of sheets it works for the first file (and any successive file which has a sheet named the same thing) but for any other sheets that weren't in the original definition it doesn't apply. So in the image below - where the sheets are CP4, CP5, CP6 in the file used for definition of parameters, the next might be CP1, CP2, CP3 - and for those sheets which weren't covered by the parameters, it reverts to using row 2 for the field names.

Hi @debbiatsafe thanks for the reply! The field names row is defined per named sheet in the file as far as I can tell - so if I set the value to 0 and run it for a series of sheets it works for the first file (and any successive file which has a sheet named the same thing) but for any other sheets that weren't in the original definition it doesn't apply. So in the image below - where the sheets are CP4, CP5, CP6 in the file used for definition of parameters, the next might be CP1, CP2, CP3 - and for those sheets which weren't covered by the parameters, it reverts to using row 2 for the field names.

Hi @debbiatsafe thanks for the reply! The field names row is defined per named sheet in the file as far as I can tell - so if I set the value to 0 and run it for a series of sheets it works for the first file (and any successive file which has a sheet named the same thing) but for any other sheets that weren't in the original definition it doesn't apply. So in the image below - where the sheets are CP4, CP5, CP6 in the file used for definition of parameters, the next might be CP1, CP2, CP3 - and for those sheets which weren't covered by the parameters, it reverts to using row 2 for the field names.