7 Replies Latest reply on Jan 31, 2011 3:23 PM by philmodjunk

    Importing

    MikeProcopio

      Title

      Importing

      Post

      I had a tool developed that gives me data on an excel sheet. I have records that I assigned id numbers for. Problem I am having is on the excel sheet one record may have two lines of data on the excel sheet. Now I have the same ID number on both lines, when I import to my database the database only gets one line of the data. Is there anyway I can pickup that second line of data and store it somewhere in my database where it shows up??

        • 1. Re: Importing
          philmodjunk

          What is the final result that you want to see in your Filemaker table. 2 records with the same ID number? If so, it sounds like you will need to open Manage | Database | Fields and modify the ID field's validation settings to permit non-unique ID numbers.

          If you want a different result, please specify what you want and we'll take another swing at it.

          • 2. Re: Importing
            MikeProcopio

            Hey Phil,

             

            I tried emailing you last week didn't get a response so I figured you were busy. Basically I have an excel file that has stats for basketball players that I have in my database. Some of the players have two lines of data in the excel spreadsheet. Each player has an ID number that matches up with my database records so thats how I sort the inport. Filemaker only imports 1 line of data. SO what I want is somehow I can take the records with the second line od data and put it somewhere in my database to have that data stored.

            • 3. Re: Importing
              philmodjunk

              Filemaker only imports 1 line of data.

              That's only true if you are enforcing unique values on a field with validate always enabled. That sounds like a good thing, but I'm just making sure you understand that this is an option you have control of.

              I think I'd import all of this data into a separate table where ID numbers do not have to be unique. Then I'd set up a relationship linking it by ID to the table into which you are currently importing the data. Then I can set up a portal to this new table so that I can see 2 (or even more) rows of such data for each player record.

              • 4. Re: Importing
                MikeProcopio

                OK so I start a new database and just import the data first?? Sounds like it will work just dont know the steps just seems a tad bit confusing. I had a programmer take stats off the internet for me and then export it in an excel file. The only way I figured I could import it was through ID.

                • 5. Re: Importing
                  philmodjunk

                  Hmmm, keep in mind that I have only the vaguest idea as to what you have and what you want to do here.

                  This sounds like player stats. If so, I'd have a table for the players where I have one record per player and relate it to a table for the stats where I can have more than one record for each player. The linking value would be the ID number.

                  You can create a new table in a Filemaker file from your excel table simply by importing the records from your excel table and then specify New Table as the table into which you import the data. Doing that with your first import would set up your table for subusequent imports.

                  Alternatively, you'd get your programmer to redesign his solution so that all the data for a given player is on a single row in the spread sheet. (Keep in mind I have no idea what the current spread sheet actually looks like, so this might not be a good idea.)

                  • 6. Re: Importing
                    MikeProcopio

                    Yeah I've done a pretty bad job at explaining.

                    OK so I have this tool that takes stats for every player in the NBA and exports it to an excel file with column headers such as first name last name ID number, and team etc.

                    So I then import the file to the database to update the stats daily.

                    some players are traded and play for more than one team in a season which spits out more than one line of data with the same ID number

                    I can email you one of the excel sheets and my database if it would be easier

                    • 7. Re: Importing
                      philmodjunk

                      That explains the two rows and why they are needed.

                      I think a related table of player stats where ID numbers need not be unique would server here. You can then even filter your portal by team to see the stats for a specified team instead of both...

                      You could also use a layout based on this stats table and use the relationship to pull in player name etc from the player table in a header or sub summary part...