Still having Excel Issues ... Column disappears

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

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!!!

:)