3 Replies Latest reply on Feb 17, 2012 7:57 AM by philmodjunk

    Change multiple Excel cells (column) into one field instead of multiple rows? - beginner question

    Xiaxian

      Title

      Change multiple Excel cells (column) into one field instead of multiple rows? - beginner question

      Post

      Hello,

       

      I've been tasked with taking a dump from a computer inventory program and creating a searchable FM database from the data. The problem is how the inventory program exports some fields. Fields with one piece of data (model, memory, serial number) are easy. Fields with a lot of data are not so easy; the data is exported in multiple rows (under the same column header). The result, when I import the Excel document, is hundreds of records with most fields blank instead of one record.

      I can manually select the data and merge the fields to fix this one file, but I need to import a dump that will contain dozens if not hundreds of computers. I'm looking for a method that might help me simplify this process - especially if/when I have to update the information with an updated import.

      I've attached a screenshot of the Excel 'dump' so you can see the problem. When importing the file I'm getting a record for every row. Since I cannot modify the dump from the inventory program I need to work with the data.

       

      Any help would be greatly appreciated.

      Thank you

      Screen_shot_2012-02-17_at_8.29.58_AM.png

        • 1. Re: Change multiple Excel cells (column) into one field instead of multiple rows? - beginner question
          philmodjunk

          This looks like data that should be imported into more than one FileMaker Table. The left hand column that identifies the computer would populate a "computers" table and then these columns would populate one or more related tables. This looks like data that should be imported into a temporary table and then a script would loop through the records and fields moving the data into new records in the relational structure, linking them by ID numbers as it goes.

          How much experience/skill do you have in writing scripts in FileMaker?

          • 2. Re: Change multiple Excel cells (column) into one field instead of multiple rows? - beginner question
            Xiaxian

            Thanks so much for your quick response. Sadly my script writing skills are limited to very basic 'go to this layout' and 'perform find' - I have no talent in tables or relationships yet. 

            • 3. Re: Change multiple Excel cells (column) into one field instead of multiple rows? - beginner question
              philmodjunk

              To Make proper use of this data, you'll need to train up to fuller use of FileMaker and relational database design or find some one who can set this up for you.

              At the very least, the data in your spreadsheet should have a structure like this once the data is correctly imported and linked:

              Services>------Computers-------<Components
                                            |
                                           ^
                                     Processes

              Computers::__pk_ComputerID = Components::_fk_ComputerID
              Computers::__pk_ComputerID = Processes::_fk_ComputerID
              Computers::__pk_ComputerID = Services::_fk_ComputerID

              And the looping script to put the data imported into the temporary table in the correct spots would create a new record in Computers each time a non empty field is encoutered in the computers column--moving data from the temp table to this table for any fields specific to the computer such as a location, asset tag number, etc. It would then set the serial number from __pk_computerID and then uses the data in the other fields in this same record and each record following it until the next non-empty computer field is reached to generate records in the other tables.

              I understand that this isn't enough detail for you to just go out and do this, but it should give you a more complete picture of the scope of the problem.