10 Replies Latest reply on Feb 20, 2011 11:02 PM by WolfDrechsel

    Importing a table to FMP: Each table element should be one data record

    WolfDrechsel

      Title

      Importing a table to FMP: Each table element should be one data record

      Post

      Hi friends,

      I've got to do the following job:

      Importing a bus timetable into filemaker, where each single departure time is a distinguished data record. Meaning: Left side is a column, listing all the bus stops, at the very top there is a row, listig the numbers of the scheduled trips.

      Now I need data records which the contain name of the bus stop, the departure time and the trip number.

      Can anybody give me a hint how to approach that problem.

      Yours, Wolf 

      PS.: The graphics file attached shows how the schedule looks like.

      sample_schedule.jpg

        • 1. Re: Importing a table to FMP: Each table element should be one data record
          Sorbsbuster

          What would the data typically look like now, in rows and columns, as it is in the file that you will be importing from?

          • 2. Re: Importing a table to FMP: Each table element should be one data record
            WolfDrechsel

            Hi sorbsbuster,

            nice you're paying attention to my issue. I attached a picture to the initial message.

            Greetings,
            Wolf 

            • 3. Re: Importing a table to FMP: Each table element should be one data record
              Sorbsbuster

              No problem, Wolf,

              The correlation between Excel and Filemaker is that each column is a field and each row is a record.  (I think you knew that, anyway)

              If you create a table in a Filemaker file with the field names as you have them across the top of the columns, and import the data from that Excel sheet it will let you line up each column against the right field name.  It will import the data into the right fields, and create a record for each row.

              (In fact, FM can create the Filemaker file directly from the Excel sheet if the column header is set as the field name.)

              If that was what happened, and you were looking at a Filemaker layout in list view, and it looked exactly like your spreadsheet, would that mean your question was answered?

              • 4. Re: Importing a table to FMP: Each table element should be one data record
                philmodjunk

                I think he wants all combinations of the name in column a with column C through N to be in separate records.

                You can do that with a two stage process. Import into a table where each spreadsheet row is a record, then run a script that extracts the data from each column as needed to create records in your final table.

                I haven't done this myself, but something might also be possible with an XML import if you can set up the grammar to split the columns into records during the import...

                • 5. Re: Importing a table to FMP: Each table element should be one data record
                  Sorbsbuster

                  I think you should have fields:

                  - Bushaltstelle_Name

                  - Ankunft01

                  - Ankunft02

                  - Ankunft03

                  ...etc

                  Then to let you be flexible with the name of the trips, I would have the same number of global fields:

                  - gFahrtnummer01

                  - gFahrtnummer02

                  ..etc

                  Import column 1 into the field Bushaltstelle_Name

                  Import column 2 into the field Ankunft01

                  Import column 2 into the field Ankunft03

                  ...etc

                  You will have to tidy the spreadsheet first to remove the empty columns (eg column D)

                  The 'an' and 'ab' will be a bit tricky, though.  Needs some thought....  (You could have two stops: 'Bahnhof - an' and Bahnhof - ab')

                  • 6. Re: Importing a table to FMP: Each table element should be one data record
                    Sorbsbuster

                    - Phil, so:

                    Mannheim, Hauptbanhof                          12:10

                    Mannheim, Hauptbanhof                          12:20

                    Mannheim, Hauptbanhof                          12:35

                    Mannheim, Hauptbanhof                          12:50

                    Mannheim, Hauptbanhof                          ...etc

                    Reichskanzler - Muller - Strasse                12:12

                    Reichskanzler - Muller - Strasse                12:22

                    Reichskanzler - Muller - Strasse                12:37

                    Reichskanzler - Muller - Strasse                ...etc

                    ...etc

                    • 7. Re: Importing a table to FMP: Each table element should be one data record
                      Sorbsbuster

                      If that's the case, could Wolf not (easily) create the Excel table as is, straight into FM format.  Then duplicate the table.  Then set up Import A, ImportB, ImportC, etc, which import:

                      The first import runs:

                      Source Field                                             Destination

                      Table1::StopName                                   Table2::StopName

                      Table1::ColumnA                                     Table2::DepartureTime

                      The second import runs:

                      Source Field                                              Destination

                      Table1::StopName                                    Table2::StopName

                      Table1::ColumnB                                      Table2::DepartureTime

                      ...etc

                      This is like thinking in 3D for me.  My head is starting to hurt.

                      • 8. Re: Importing a table to FMP: Each table element should be one data record
                        philmodjunk

                        Good idea! And you don't ever have to "Create Excel table as is, straight into FM format". You can just import from Excel each time.

                        • 9. Re: Importing a table to FMP: Each table element should be one data record
                          Sorbsbuster

                          Then delete the records with an empty 'abkunft'.

                          But need to get the appropriate Fahrtnummer into every import's record.

                          @Wolf - Before thinking about that, can you confirm which way you want the import to work?

                          • 10. Re: Importing a table to FMP: Each table element should be one data record
                            WolfDrechsel

                            Hi to everybody,

                            thank you all for your valuable thoughts, and please apologize this reply comes so late. - The project within which I had to solve the problem described above is scheduled rather tightly, so "doing" had a higher priority.

                            What I took from your answers was that there is no "ready-to-go" solution. As I'm not good in scripting FM, I prepared the tables before importing them to FM. I'm not good in visual basic as well - but good enough to get it done. The script transposes the tables into lists, each line containing all the info necessary - and then it's easy enough even for me to get them imported.

                            If one of you is intersted in that little script, please drop me a line and I'lll be happy to mail it over.

                            Yours thankfully, Wolf