Underscore appears in field name - excel 2010 table exported to filemaker 12 pro

I am trying to use a database manager called FileMaker 12 Pro to import a large Excel 2010 table into it. The file does import the data nicely, and the field headings from Excel 2010, using the FileMaker creation facility do show up as the new field names. The problem is that in the transfer from Excel 2010 to FileMaker 12, the field headings as they appear in FileMaker now have an underscore before the field heading (as: _field heading). This is incorrect but no matter how many times I do it, the _ still appears. FileMaker tech support can find no reason that this should happen and says that it is not their product that is causing the problem. I also tried, on the theory that perhaps something was wrong with the Excel structure, to cut-and-paste the data from the original Excel file into a new Excel structure to see if that would export differently; it didn't. When I was talking to FileMaker tech support they asked me to try a simple test using about a half dozen cells and converting it to FileMaker. This worked fine with no underscores visible. I don't think it has anything to do with the problem, but it is a very large spreadsheet, consisting of about 1500 columns across and about 100 rows down. Since FileMaker is the only product that I could find that could handle this many fields in one table, I would like to try to get this to work. The error involved (the _) is certainly not fatal, just cumbersome and requiring unnecessary/extraneous keystrokes that I would like to avoid having to do. Any help offered would be appreciated and points awarded. Thanks.
mzimermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresidentCommented:
The underscore character appears in field names when converting if you have the same field name more than once. For example, if you have the First "Company" in one column and another column further down that also has "Company" as the header or field name. The first column will get the field name "Company", the second column will be named "_Company".

The solution is to cleanup the field names in your spreadsheet before converting to FileMaker so that they are all unique. You can see the effect by converting the attached test file.

ConversionTest.xls
0
mzimermanAuthor Commented:
That's very interesting. Unfortunately there are many field names within the structure that have similar beginnings, in that they start with a numeric that should be unique, such as 1fieldname, 2fieldname, etc. every field after export to FileMaker has the underscore, including the very first one. I'm not sure what it's looking for or what order its looking for them in. An actual example is: 707Reserved      708Reserved      709Reserved      710Reserved
Any ideas?
0
Will LovingPresidentCommented:
Then your issue is that FileMaker does not allow field names created in a conversion to begin with a number. You can manually create a field name in FileMaker that begins with a number but you cannot use that field in any calculations which is a pretty severe limitation.

You'll need to edit the field names by prefixing them with a non-numeric character, e.g. "A707Reserved" or "A_707Reserved".

See this knowledgebase article on field naming:
http://www.filemaker.com/12help/html/create_db.8.9.html
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

mzimermanAuthor Commented:
You have been so much more informative than the people at FileMaker, and thank you for that. You are right, that is a severe limitation. Just out of curiosity, is there a way to modify the field names with any kind of batch process? Barring the possibility of doing any such batch process to modify the field names, I may have to live with the under score.
0
Will LovingPresidentCommented:
If you are familiar with and have access to an automation program like QuicKeys (which is mostly a Mac tool but there are many similar Windows tools) you can rename fields within FileMaker's Manage Database -> Fields dialog. I've used Quickeys and similar tools to setup a loop to edit field, script and layout names. The loop goes to the first field, double-clicks, goes to the beginning of the field name, adds a prefix (or removes text, whatever), hit's "OK" and then moves onto the next field. It can take a little time to setup and get familiar with but it works well once you get the steps right.

Alternately, if you're handy with Excel, you could create a Macro that edits the column names prior to import, adding a prefix to any one that begins with a number. As noted, if you have fields in Filemaker that begin with a number, they may fail to produce the correct results when used in a calculation so if all or most of your fields begin with a number you probably want to add a non-numeric prefix to them. Removing the underscore so they begin with a number will not fix this issue. It must be a non-numeric character.

If you want to go the renaming route, sometimes it's simplest to just find or pay someone else to do it. It's a mindless task that a child could do, just renaming over and over. I've sometimes farmed out this kind job to someone who didn't have other DB skills and wanted the work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mzimermanAuthor Commented:
Excellent! Thanks for all your help. Fastest, best response from EE ever. Wish I could award you more points.
0
mzimermanAuthor Commented:
Best advice ever.
0
Will LovingPresidentCommented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Apple Software

From novice to tech pro — start learning today.