Data Import - Part IX

More pointers on cleaning data in preparation for transferring (or “migrating”) them from one FileMaker Pro file to another, especially if they are in different versions:

1.                   Try RefreshFM from Goya (See: ^[i]), which is designed to help streamline data transition from one (old) FileMaker solution to another (new) FileMaker system.

2.                   When trying to find text in FileMaker, remember to work by the rules: Fields containg quotation marks can only be found if you prefix the text string with a backslash:\.

3.                   Before anything, clean the data first:

a.                   The text from MSWord will come across with an added – invisible character.

1.       In Excel: If the text results with a Question Mark (“?”) at the end of every Cell, copy and paste into Notepad, Find & Replace “?” with “” (no text, blank) and Copy & Paste back into the spreadsheet.

2.       In FileMaker: If the text results with a “ý” to the same as the above.

a.       Status Check: Is all the Data still intact?  Are the IDs in the proper order still?

b.      Save the changes in a separate Spreadsheet, so you can roll back to it if the changes in the next step go pear shape.

3.       In Chrome: it comes across as “‎”.

                                           ii.            Arrange physically entered blank lines:

                              iii.            Find the following: ^l

                             iv.            & Replace all Line Feeds* with   ^p^t

                        (*as opposed to Paragraph Returns or Hard Carriage Returns)

                               v.            Then Copy & Paste the text into a new Excel Spreadsheet

1.       Status Check: Is all the Data still intact?  Are the IDs in the proper order still?

2.       Save the changes in a separate Spreadsheet, so you can roll back to it if the changes in the next step go pear shape.

b.                  Quotation Marks

1.       Sometime there are quotation marks, which are unnecessary; delete them by doing a Find&Replace.

                                                             ii.      Space Invaders:

                                                                                          iii.                        Many Records have only visually blank Fields, as during Data Entry, the Clerk sometimes failed to hit the Carriage Return to ‘create’ a blank line, when the last item/s didn’t have anything to enter.  However this shouldn’t be a concern as if there is no data then it might as well be without a blank line.

                                                                                         iv.                        Check wherever the character “0” appears, which was entered in the Manufacturer, Kosher Org, Ingredients Columns - instead of a line entry by Suzy Schleider, as seen in 10857470 and hundreds more.

These blank ‘spaces’ need to stay in the correct place, level with all other corresponding pieces of data: Ingredients, Supplier, Manufacturer, Kosher Organisation, Class.

To solve this problem, we need to replace all occurrences of a Value with a Primary (or unique) ID / Serial Number / Code, so that the system can have a separate list of data sets with their Unique ID’s that are ‘consulted’ whenever this unique ID is looked up.

---

^[i] http://www.goya.com.au/refreshfm/about