Importing data from an unsupported file format

[boyntonfirefighter]

Hi, I live in Palm Beach County...home of the butterfly ballot and now the National Little League Champs. I bought Filemaker Pro 6 3 days ago, and after several hours of frustration plus a useless phonecall to the company techinical support, I stumbled across this forum and I am hopeful somebody has some advice.

My problem is the same Supervisor of Elections office which did the butterfly ballot will sell a bonafide person a list of voters on a disk. I have 10,700 voters for the Boynton Beach City Commission District 1. It is in the format which the SofE worker calls "Fixed length record". Each record is exactly 541 bytes. There is a list of fields which lists the "range". CERTNUM is range or bytes 1-10. Name is bytes 11-50. Party is bytes 51-54. And so on.

How can I get this into Filemaker Pro (so I can add phone numbers, generate walking lists, etc.) ? Thanks for any thoughts.

Mike Fitzpatrick

[ernst]

Hi Mike,

I think you would first have to import the data into one field and then split it into separate fields with a script like:

goto record [first]

loop

set field [CERTNUM, Middle( YourImportedData, 1, 10 )]

set field [Name, Middle( YourImportedData, 11, 40 )]

:

etc.

:

goto record [next, exit after last]

end loop

Hope this helps,

Ernst.

[SteveB]

Another often used technique is to get the data into Excel, clean it up, after which getting it into FM is simple.

Steve

[boyntonfirefighter]

Of course. Early Alzihiemer's setting in. I might dig through the basement for an old college Basic, Fortran, or COBOL textbook with a more complete example. I need to add a sub loop to suck out all the blank spaces. Now to find a compilier... I talked to an ex IBMer friend who said he is reformatting his second computer and as soon as he has done that I can use the OS2 compilier on it. Thanks a lot for replying. It helped.

[boyntonfirefighter]

Thanks for responding. The FileMaker Pro6 Bible by Steven Schwartz had a couple of pages on this topic. I tried it without much success because I had messed up the preceding step. This was to open the file into a word processor program (Wordperfect in my case)and do about twenty global find and replaces. To get the records to line up I changed 50 spaces to none twice and then the third time changed it to 3 commas. This got rid of most blank second address fields. Then I went through and changed 5 spaces to two spaces. I did this a couple of times until there were no more hits. Then I changed 4 spaces to two spaces, then three spaces to 2 spaces. Then I changed all the two spaces to commas. This left most of my single spaces correctly in the address field spacing. Then I had to change "1 " to "1," up through all the digits to put commas between the numbers representing different elections. I imported this mess into FileMaker and got a fairly good database. Except in the precincts where there were a lot of condo apartments which meant an extra address line. I am guessing in thirty years fifty different clerks inputted these addresses and each one did it a little different with spacing, hyphens, commas, order and capitals.

So then I put this mess in Quattro Pro to do what you suggested and found I should have been substituting "","" instead of "," because the value in each box began with a comma. Then I saw the messed up condo addresses were up to 10% in a 7000 plus record file. No way was I going to fix each individually. Plus, it is a minor thing, but if I went back and substituted "","" I did not know what to substitute for the beginning and end of each file because I do not know how to represent a hard return in the search and replace field. A minor thing, but in this case the last straw. That was when I called FileMaker office and got no help and then came to this forum and got some. Thanks again.

[BobWeaver]

There is a time and a place for doing those type of global search/replaces to reformat a file. But this isn't the time. Your file is already in a very precise easy to read format which can be easily converted using either Ernst's or Steve's method. If you start removing the spaces you will end up with a mess that will be difficult to import reliably.

If you have Excel, it can quickly import fixed column type files which is what you have. When importing these, Excel has a handy little graphical tool that lets you set the field positions just like setting tabs in a ruler bar. It then separates the fields into individual spreadsheet columns. Then you just save the Excel file and open it in Filemaker.

if you don't have access to Excel, then Ernst's method will let you bring the file in without much effort either.

Adding a few more details to his method:

1. Set up all of your fields in your FM database plus one additional one called YourImportedData.

2. Import the file into your database as tab delimited into the field YourImportedData.

3. Run the script that he gave above.

[Fitch]

Getting rid of extra spaces would be as Ernst said, but add the Trim function:

Set field [Name, Trim( Middle( YourImportedData, 11, 40 ) ) ]

[goanna300]

How Do I post?

Am I going blind? Is there no "Post a message " Link?

Steve

[stuj1026]

You absoulutley want to bring in the data into excel first and then import into FM. This will work as I have over the years purchased the voter records for Broward County which which goes into Excel quite easily. In windows save the excel file directly to the desktop and then just drag the icon (saved excel sheet) directly over the filemaker shortcut. FM will then do the rest.

Im sure as in broward county you can after each election get the updates for palm beach district 1 at alot cheaper cost.

FM will handle these records quite easily. I have 101,000 voters in FM. Just try to avoid to many calculations as this will slow things down on FM when creating calc fields.

Need anything concerning this let me know

Stu