3 Replies Latest reply on Feb 7, 2012 1:12 PM by philmodjunk

    data type changes on excel import > filemaker pro 11

    StephenSkoutas

      Title

      data type changes on excel import > filemaker pro 11

      Post

      Hello, I'm brand new here and brand new to FMP11.

      I am attempting to create an item database that is fed by existing excel data.  On any given day, my vendors would complete an excel spreadsheet that I have provided to them.  This spreadsheet details common item details (cost, master pack, dimensions, weights, fob points, etc.).  When we receive this spreadsheet from a venodr (essentially a quote), we would like to use the FM import function to pull that excel data into the database so that we can manipulate it further and turn it into our sellable items.

      Everything is working wonderfully with the exception of 2 columns (2 out of 30).  Both of those columns are weights in kilograms (one is master carton, the other is net weight of the individual piece).  Both of these columns are defined in excel as a number with 2 digits behind the decimal.  They are formatted in excel using 0.00" KGS" so that if a user enters "2.4" the cell shows "2.40 KGS" and the data entry bar shows "2.4"

      When they get imported, they are getting converted to dates.  An Excel value of "5" is showing in FM as 151900.00, but when I click on that result in FM, the value changes to 1/5/1900.  The field type in FM is defined as "number," not "date" so I'm not sure what I'm missing.

      Any help would be greatly appreciated.  Thanks in advance.

        • 1. Re: data type changes on excel import > filemaker pro 11
          philmodjunk

          If you remove the formatting from the cell, does the data import correctly as a number? That might narrow down the possible issues and suggest a work around if it does.

          The field type in FM is defined as "number," not "date"

          Just to confirm the obvious, the field is defined as number in Manage | database | fields? (sometimes people think that the data formatting selected on the inspector tab determines a fieild's data type and this is not the case...)

          • 2. Re: data type changes on excel import > filemaker pro 11
            StephenSkoutas

            Thanks for the quick reply.  I took your suggestion and removed formatting in excel, and the data for those two fields imported correctly.  The answer to your second question is "yes," the fields are defined as numbers at the Manage> Database> Fields level.

            While your solution works perfectly, I'd like to understand what cause the problem so I can solve similar issues in the future (not to mention that I'd love to reintroduce the proper cell-level formating to my vendor item spreadsheet.  Any ideas?

            • 3. Re: data type changes on excel import > filemaker pro 11
              philmodjunk

              That would take someone with access to the source code to answer. I'm just a fellow user.

              Please post this issue in the Report an Issue section. You can save typing by posting a link to this thread in that report. FileMaker techs should respond to that post and they may be able to provide more info about this issue. If you can upload a sample excel file to a file sharing site and include the download link in your post, it may help them better replicate the issue on their systems.