Has anyone encountered this? Any ideas on how to get it to recognize the field or why it doesn't recognize it?
FYI: I've even put in the range of the data in the Parameters and it still doesn't pick up the field. For example:
Field Names Row: 3
Cell Range (e.g. B2:G9): A4:CF16430
could you make this Excel-file available here for testing? Dropbox, Google Drive, or similar...
David
This reply is to Nexus788, David R., and anyone else who encounters this issue and stumbles upon this question in the future ...
We have discovered why the Excel Reader does this. It's because the field is considered a MIXED TYPE. It doesn't bring the field in because it is there for a safeguard against an error in your data. It is assuming that if a text value shows up somewhere far down the data, then the Reader thinks it is an integer field, and that the sudden appearance of text is an error. In my case, it is definitely not an error when text suddenly appears.
According to the FME Microsoft Excel Reader help documentation (UnderMixed-type Columns) it states you could change the values in your Registry Edit to have it look at more rows (16 is the max) until it hopefully encounters the first text field to aid it in determining the correct field type. As it stands now it only looks at the first 8 rows. This won't help us, because we do not have many GIS ID fields with the text value in them. But we always have at least a few, so we can't rely on changing the Registry Edit. Unless we have dumb luck and our text GIS ID record falls in those first 16 rows.
You might ask ... "Why don't we move one of the text fields to the top?" Well, we are looking for a way to automate the end processing of this data, and that will not be a feasible action for us to take. You might also ask "Why would you use a text value in an ID field?" Well ... We ask that question every day, and unfortunately, that decision was not ours to make. Sadly it cannot be changed now.
Our work around is easy for this issue. Since an individual has to save this as an Excel file before we use it, we've instructed them to save it as a CSV file instead. In fact, we've tested the CSV output, and there is no longer an issue with the GIS ID field not showing up. For us, this workaround adds no additional time to the process.
I hope this helps someone down the line who might encounter this same issue.
FYI: I have no desire to be the king of answered questions. I do not possess the amazing knowledge of FME as many of you do. However, I'm extremely proficient at using it and troubleshooting most of my own issues. I bring this up for one reason. I'm going to select my own answer as the best answer for now. Just in case someone stumbles upon this same issue they will see why this is happening and what our workaround was. If down the road someone should post a definitive way to get around this issue while keeping the data in Excel format I will change the best answer to theirs.
As always, I'd like to say thank you to this community. Your knowledge of FME astounds me on a daily basis.
Sincerely,
Tony
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.