Question

Excel Reader, order of columns

  • 4 December 2019
  • 5 replies
  • 11 views
michaelhaertel
Badge

Hello all,

 

this question is related to question 83147, I have the same problem.

 

I use a generic reader and refer to the attribute values in the Excel file using the attribute dereferencer. This wiorked fine with my first tests. But now I have an Excel file which shows the columns completely at a different position than in FME!

Excel:

 

FME:

 

The Kennung, in Excel the last column is the second column in FME. The specification to the service I provide via FME server is that the colum 1 and 2 in Excel (so 0 and 1 in FME) shall be used as X and Y. From what I see in Excel it should work but my workspace fails because the "Kennung" value is no valid coordinate.

Any suggestions? I can't find a reason why FME changes the column order

 

File is attatched:

1 Attachment

5 replies

chrisatsafe
Rank
Badge +2

Hi @michaelhaertel,

Interesting one, I was able to reproduce this on my machine as well. One thing I noticed is that the order seems to be incorrect (alphabetical for some reason) if you don't open the Excel reader parameters before adding the reader as shown here:

https://www.screencast.com/t/fIe42osP

Unfortunately, as you are using a Generic Reader, this doesn't quite work for you but if you are able to replace the generic reader with a FeatureReader it might help in the short term. I'm going to look further into this and file a bug report for so our team can further investigate the issue. I have included a template that shows how the reader brings attributes when you open the Reader parameters and when you don't open the reader parameters.

Thanks for reporting this! I'll follow up on this post.

Excel_Attribute_Order.fmwt

michaelhaertel
Badge

Sorry, one sentence in the original post didn't make sense because it doesn't reflect the example:

 

replace:

The specification to the service I provide via FME server is that the colum 1 and 2 in Excel (so 0 and 1 in FME) shall be used as X and Y.

 

with:

The specification to the service I provide via FME server is that the colum B and C in Excel (so 1 and 2 in FME) shall be used as X and Y coordinates.

michaelhaertel
Badge

Hi @michaelhaertel,

Interesting one, I was able to reproduce this on my machine as well. One thing I noticed is that the order seems to be incorrect (alphabetical for some reason) if you don't open the Excel reader parameters before adding the reader as shown here:

https://www.screencast.com/t/fIe42osP

Unfortunately, as you are using a Generic Reader, this doesn't quite work for you but if you are able to replace the generic reader with a FeatureReader it might help in the short term. I'm going to look further into this and file a bug report for so our team can further investigate the issue. I have included a template that shows how the reader brings attributes when you open the Reader parameters and when you don't open the reader parameters.

Thanks for reporting this! I'll follow up on this post.

Excel_Attribute_Order.fmwt

Hello Chris,

thank you for looking into this. It's indeed interesting. Switching to static reader would work in our case but as you also mentioned I consider it a bug that for a dynamic reader it sorts all the fields alphabeically instead of preserving the original order from the input file.

my idea was that if I use a dynmic reader I can let the user decide how the columns are named and just use the index to assign values to the only three attributes I need during the processing.

if the general idea about dynmic inputs is that the data is unknown and therefore the field order shouldn't play a role at all (Which I would also accept as reasoning), there would be no way to rely on the position and therefore would have to rely on column names instead.

But I will also wait for the result of the bug report (if it is considered a bug), thank you for reporting it.

Regards,

Michael

chrisatsafe
Rank
Badge +2

Hello Chris,

thank you for looking into this. It's indeed interesting. Switching to static reader would work in our case but as you also mentioned I consider it a bug that for a dynamic reader it sorts all the fields alphabeically instead of preserving the original order from the input file.

my idea was that if I use a dynmic reader I can let the user decide how the columns are named and just use the index to assign values to the only three attributes I need during the processing.

if the general idea about dynmic inputs is that the data is unknown and therefore the field order shouldn't play a role at all (Which I would also accept as reasoning), there would be no way to rely on the position and therefore would have to rely on column names instead.

But I will also wait for the result of the bug report (if it is considered a bug), thank you for reporting it.

Regards,

Michael

Hi @michaelhaertel,

This is a bit of an odd one. I have been testing other Excel files (such as Alphabetical_Test.xlsx) and haven't been able to reproduce the same behavior. Are you able to try another Excel workbook to see if the issue still exists? Alternatively, can you try re-saving the excel sheet with a different name or copying the contents of the workbook to a new workbook and saving it again? This will help us narrow down if the issue is reading that particular file.

Not sure why we are incorrectly reading the excel workbook you provided but after testing other workbooks it looks like the readers (generic and Excel) are behaving as expected.

Let me know if the issue still exists with other files for you and after re-saving the workbook.

michaelhaertel
Badge

Hi @michaelhaertel,

Interesting one, I was able to reproduce this on my machine as well. One thing I noticed is that the order seems to be incorrect (alphabetical for some reason) if you don't open the Excel reader parameters before adding the reader as shown here:

https://www.screencast.com/t/fIe42osP

Unfortunately, as you are using a Generic Reader, this doesn't quite work for you but if you are able to replace the generic reader with a FeatureReader it might help in the short term. I'm going to look further into this and file a bug report for so our team can further investigate the issue. I have included a template that shows how the reader brings attributes when you open the Reader parameters and when you don't open the reader parameters.

Thanks for reporting this! I'll follow up on this post.

Excel_Attribute_Order.fmwt

Hello Chris,

sorry for the delay but I eventually I tried some other things:

  1. save my excel file from above to a different file name
  2. save excel file to .xls (Excel 2003-2007)
  3. Copy the content of sheet "Tabelle1" to the clipboard and paste it to sheet "Tabelle2". Delete sheet "Tabelle1" and rename "Tabelle2" to "Tabelle1"

All lead to the same result. The columns are ordered alphabetically and read in that order.

Unfortunately the excel was not created by myself but by the user who was supposed to test the FME Server application (With the result I posted here).

I also tested with your "alphabetical-test.xlsx" and I can confirm that it works well: Column C becomes x-coordinate and column B y-coordinate as intended. So this issue somehow only applies to the file the customer created. I will ask him how he managed to create such a file but for now I think we can leave this issue and don't believe in a real FME bug.

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.