5 Replies Latest reply on Aug 1, 2016 4:02 PM by user25627

    Data Normalization Upon Import From Excel

    user25627

      Hi there,

       

      I'm new to FileMaker and two problems I have with importing data from Excel drive me crazy ... I've tried to search the forum for a solutions but wasn't lucky. Maybe some of the more experienced FileMaker community members can help me out:

       

      a) All empty cells in the Excel file contain a blank space instead of just being empty (see column A in the sample file).

      How can I get rid of those blanks in fields containing nothing else upon import into FileMaker?

       

      b) Time values in the Excel file are formated XX.XX and get messed up upon import into FileMaker (see colum B in the sample file).

      When importng the time values as is some are imported as is, but to some entries changes are applied leading to wrong times when I try to clean up things later, e. g. 00.10 stays the same wheras 10:10 becomes 10,1.

       

      My attempts to clean up the values using Substitute ( Table::Problem B ; [ "." ; ":" ] ; [ "," ; ":" ] ) lead in the latter case to a time value of 10:01:00 instead of 10:10:00.

       

      I have no influence on the Excel source files so I'm looking for a solution inside FileMaker to deal with normalizing the data.

       

      Any help is very much appreciated!

      Pete

        • 1. Re: Data Normalization Upon Import From Excel
          David Moyer

          Hi,

          the FM functions Trim and Replace can come in handy cleaning up your imported data.

          Are you importing the data into FM, or are you in FM and using File>Open to open the .xlsx file?

          I opened your file using File>Open and FM brought both columns in as text fields.  So, you might need to use some conversion functions, like GetAsTime and/or Time, along with text manipulation functions.

          As another route, can you get a .CSV file instead?

          There are many ways to go about forcing this to work.

          • 2. Re: Data Normalization Upon Import From Excel
            rouelf

            User25627,

            don’t know where your comas ( “ , ” ), or colons ( “ : ” ) are coming from.  I have simply dragged the xlsx file unto the OS X, FM 15 Adv icon, set the first row to labels, and the file opens as per the figure below.

             

            If there is a space only in column “Problem A”, then you can create a new field (Fix Problem A) with the following calculation:

              = If ( Left ( Problem A ; 1 ) = “ “ ; “” ; Problem A )

             

            As for values in column Problem B, I think you are suggestion that 10.1 is actually 10:10:00.

            Are you sure it is not 10:06:00 (10 hrs, 6 min, 0 sec). Where 06 min = .1 hrs)

             

            Need more info to properly interpret you data.

             

            Data.tiff

            • 3. Re: Data Normalization Upon Import From Excel
              carlosilvia0

              Hello,

              look at the attached database. I used field options to enter a calculation that solves both problems.

              • 4. Re: Data Normalization Upon Import From Excel
                user25627

                Hi David, hi rouelf,

                 

                Thank you very much for pointing me in the right direction! I've found a solution to both problems. I'm not sure whether they are the most elegant approaches, however, they work:

                 

                a) I've ended up using

                 

                TrimAll ( Table::Problem A ; 0 ; 0 )

                 

                It seems to do the job perfectly without causing any unwanted results.

                 

                b) Rouelf, yes, I'm sure that the imported 1.1 is supposed to be 1:10:00. Putting it together I've came up with:

                 

                GetAsTime ( Substitute ( If ( Length ( Table::Problem B ) - Position ( Table::Problem B ; "," ; 1 ; 1 ) < 2 ; Table::Problem B & "0" ; Table::Problem B ) ; ["."; ":"];[","; ":"] ) )

                 

                Thanks a lot!

                Pete

                • 5. Re: Data Normalization Upon Import From Excel
                  user25627

                  Hi carlosilvia0,

                   

                  Thanks to you too - especially for the comfortable solution file!

                   

                  a) Your approach for problem A works fine:

                  Case ( Length ( Self ) = 1 and Self = " "; ""; Self )

                   

                  b) Regarding problem B I extened it from

                  Substitute ( Self ; "." ; ":" ) & ":00" to Substitute ( Self ; ["."; ":"]; [","; ":"] ) & ":00"

                  since I forgot to mention that I'm working on an International system and thus I needed to take care of the decimal coma vs. point problem. Nevertheless I've ended up with wrong values for 10.1, 10.2, etc. being transformed to 10:01:00 resp. 10:02:00 instead of 10:10:00 or 10:20:00.

                   

                  Anyway, your help was very much appreciated!

                  Pete