AnsweredAssumed Answered

Data Normalization Upon Import From Excel

Question asked by user25627 on Aug 1, 2016
Latest reply on Aug 1, 2016 by 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!