Friday, February 10, 2012

Line breaks in FileMaker Pro exports

We have a FileMaker Pro database as our reference database that is about to be retired. I exported the data from the database in order to see what was there and how it could be handled in order to get it into another database. What I found when I opened it up in Microsoft Excel and Microsoft Access was that some of the fields contained line breaks that show up as little boxes with arrows. I needed to either hide or get rid of these visible line breaks. Neither Excel or Access would find these using the Find function or the Find and Replace function, even using “^l” as the search term. The question was: How to get rid of those line breaks.

My solution was to open the file in Excel, then save it as a csv file. The csv file was then opened in Microsoft Word, which will find the line breaks as “^l”. I did a Find and Replace for all the line breaks, replacing them with a unique character so that I could later parse the data, putting the data from the separate lines into separate fields. In searching the data I found that any character I could both use and later parse on was already being used. I finally settle on the tilde “~” since it was used in the data to signify an approximate date. First I replaced the tilde with “ca.” then I replaced the line breaks with tildes. The file was then saved as a text file and reopened in Excel. No line breaks and ready to parse.