I'm at a loss on why this field will not show up with its contents in the Reader. Has anyone ever seen this happen?
This has me stumped. I thought I had resolved this last week (I posted this in another question and thought it was resolved). The solution I thought would work was me clicking the buttons in Excel regarding Read Formulas and Read Hyperlinks. That doesn't work. I can't post the actual Excel file itself because it has information not allowed outside the company. But I've reviewed the field over and over and over again ... and there is nothing wrong with the field. For example, there are no hyperlinks or formulas in the field. The GIS ID is actually a text field because some GIS ID's have a letter in them. Like you might have 08786651 or you could have 08786677a01, 08786677a02, 08786677a03 for example. I'm attaching an image of the Reader parameters this time so you can see that the Reader does show column CF (GIS ID) in the Preview pane. But you can clearly see that a blank field is present for CF directly under CE (Lease Grid#) in the Attributes pane. Like it's trying to include column CF but it is invisible to the Reader.
Page 1 / 1
Could there be a column limit? CF is the 84th column in the spreadsheet. Although when I move the column (GIS ID) to a different location (Like first in the spreadsheet) the Reader still ignores it. Strange!!!!
Hi,
strange indeed, I've never had this problem myself. How about trying to copy-paste the contents into a new Excel-document and save it under a different name?
David
Excell 2000 had 256 column limit, so i imagine 2010 version has at least that count.
What excell version is the file, maybe if old try open it in newer and save to a current version?
Does navigator pane offer any clues?
Also maybe the column has mixed data format? Maybe set it to string in Excell?
Mixed/wrong character encoding? You can change this with "File->save as_>tiny dropdown arrow next to save button "Web options" etc.)
Maybe try copy content and paste in a fresh worksheet?
This reply is to 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 (Under Mixed-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
.. in my answer i mentioned mixed type...not stumbled upon... as it is indeed a WKI (Well Known ISSUE) ;)
Anyways, mixed type data in excell is not that uncommon..fyi
Because of the Mixed Type mention (i missed that) I changed you to the best answer. I'm not real comfortable choosing myself as best answer anyway. Thanks for the answer!!!
