8 Replies Latest reply on Aug 26, 2014 7:58 AM by beverly

    Importing a file with an incosistant format

    siddhant530

      Hi. I'm really new to filemaker. i have used other databases such as SQL and mySQl in the past but im not very familiar with filemaker. My internship requires me to use filemaker and im struggling with importing the data into the databse. I have certain fields with the layout and i have also been provided with an excel spreadsheet that needs to be imported into the database. But the porblem with the spreadsheet is that it is really badly formatted with missing columns and skewed rows as well. The first line is not the names of the columns and there are some blank rows as well. Also i would like to ignore some of the records since they are repeats. I know that the question might be too generic but is there a way to tackle this and ignore the blank rows and columns and also to ignore the repeat records. According to what i have been reading, this would be possible through scripts. What woud be a sample script for this and what would be the best resource for finding the code for this certain script?

      Thanks

        • 1. Re: Importing a file with an incosistant format
          ariley

          Deleting the repeating data is not necessarily your issue. You are faced with what we've all been faced with: bad data.

           

          So, first you'll have to do some data massage in Excel, put your headers in and then you can import them into FileMaker. Or you can import it into a table as it is and then write scripts to loop through and get your data into your current table.

           

          If you don't have FileMaker knowledge, you're better off spending the time cleaning up the Excel sheet.

           

          I've written a solution that does exactly this: bad Excel in, good Excel out. But it was a bit of work and I have no idea what your Excel sheet looks like whether it can be converted or not. I'm sorry, but there's no magic pill here.

           

          But that's just my two cents.

          1 of 1 people found this helpful
          • 2. Re: Importing a file with an incosistant format
            siddhant530

            TThanks for that information. I had one follow up question if you could help me out with that. In the excel sheet the first row does not have the names of the particular columns. It is actually a couple of rows of other information and then The names of the columns are mentioned. Is it possible to still match the field names to the correct columns or would I need to remove the first few lines from the excel sheet before importing?

            • 3. Re: Importing a file with an incosistant format
              ariley

              You need to remove the lines containing garbage.

               

              Best regards,

               

              agnes b. riley

              filemaker and web development

              zeroblue

              download vCard

               

              TWO-TIME MAD DOG AWARD WINNER

              FileMaker Certified in 10 and 11 • Member, FileMaker Business Alliance

              T: 877 917-9079 . C: 917-660-7221

              Store | Blog | Facebook | Twitter | LinkedIn

              • 4. Re: Importing a file with an incosistant format
                beverly

                I would make it so that the data is cleaned up in Excel. But rather than deleting, let excel do  much of the work as Agnes suggested the first reply. You can get the "good" parts in the worksheet into another worksheet simply with formulas. You may even sort if such a way as to determine the "duplicates". Import from the good worksheet.

                 

                -- sent from myPhone --

                Beverly Voth

                --

                • 5. Re: Importing a file with an incosistant format
                  beverly

                  I would make it so that the data is cleaned up in Excel. But rather than deleting, let excel do  much of the work as Agnes suggested the first reply. You can get the "good" parts in the worksheet into another worksheet simply with formulas. You may even sort if such a way as to determine the "duplicates". Import from the good worksheet.

                   

                  -- sent from myPhone --

                  Beverly Voth

                  --

                  • 6. Re: Importing a file with an incosistant format
                    PeterWindle

                    actually, I usually find filemaker is a great tool at weeding out the "bad data" in situations like this, but what I normally do is create a filemaker file specifically for importing data, finding blank or bad records, running a script to delete those records, then the 'target' filemaker database can import data from this file.

                     

                    This helps especially when sometimes the data needs to be split into different tables as well, plus I often use calc fields to marry up field names from the imported Excel columns to match the field names in the target database, thus making the final import easy, by just matching field names.

                     

                    I hope this helps?

                    • 7. Re: Importing a file with an incosistant format
                      gdurniak

                      you can also import everything into FileMaker,  then find, and delete,  the lines ( records ) you don't want

                       

                      e.g.  where the first field is blank

                       

                      Then use Manage Database,  to set the proper field types

                       

                      greg

                      • 8. Re: Importing a file with an incosistant format
                        beverly

                        Peter and Greg. Yes, import and then clean up is a great way. My concern is that OP is new to FM (functions, etc.) and perhaps prior cleanup within Excel is preferred.

                         

                        Beverly