Question
Apostorphe in excel field name - how to remove
When I generate an excle table as an output in FME, the field name appears to have an apostrophe as the first character in each of the field names but you can only see this when you click on the field cell...... 'TOTAL NUMBER OF BUILDINGS. I say appears, because you can not do a search and replace in excel to remove it. The only way I found to remove it is by running a trim function in excel to remove blanks. Is it possible to produce an excel table, in FME, without this apostrophe/blank as the first character in a field name?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+25- Supporter
Some suggetions:
1. In FME on the WRITER - try to change that Attribute to type: text to see if it helps.
2. Or use the StringFormatter to format your text to the desired output.
3. Or change the writer from XLS to XLSX and try different formats (2007 vs the other ones)
Thanks for the reply:
1. The writer contains text, double and decimals and all have a ghost ‘ character in the field name.
2. The StringFormatter will format the attributes, but it is the field name that is the issue not the attributes.
3. I’m assuming this is most likely the reason. We are running Excel 2003!!!! (and FME 2011) We are a federal government department running 10 year old software….frustrating to say the least.
I will just have to post process the spreadsheets by creating new field names by running a trim function. I had hoped to avoid this step.
Thanks again, I will just have to wait for the upgrade.
I have observed the same myself, regardless of the data types specified on the Writer. My hunch is that this is caused by the somewhat ornery way that FME writes to Excel files. I have not found a way to avoid it, unfortunately.
I have heard that the Excel reader and writer is undergoing a major reworking for FME 2013 SP2 (probably out around summer). Let's hope this version fixes this issue and that this can help you push through an upgrade in your department :-)
David
MarkD
Did you have any luck in removing the apostrophes with FME? I have the same problem and I have tried what has been suggested and no success. Or is it bug with the excel writer?
Thanks,
Anthony
Hi Anthony
My apologies for the slow reply but the year has just got away from me. We have finally upgraded to Win 7 and Excel 2010 but still no luck. The only work around I could find to fix the header was to create a macro that inserted a new row above, then copy the original header row, then paste special into the new row. This removes the apostrophe from the beginning of each text header. You can also do this for the first column if it is text but for some reason this procedure does not work if the column is not the first. If you try and do it in the 2nd, 3rd...... it retains the apostrophe.....why I don’t not know.
I'd still be happy to hear if you or anyone else has had any luck.
cheers
Mark