1 2 Previous Next 21 Replies Latest reply on Sep 20, 2015 2:35 AM by Boblebad

    How to import data from sql dump file ?

    Boblebad

      Hi

       

      I'm new til Filemaker and Mac. I recently switched to Macbook Pro. Before that i was on Linux for some years. And then before that for many years on Windows, where i used Microsoft Access.

       

      Thirst things first. I didn't buy a Mac to install Windows on it, so i needed something that could do almost the same as Access. So now i'm here on Filemaker Pro 14 Advanced. And i love it

       

      Many things are easier than with Access and it's easy to learn too.

       

      Regarding my project in Filemaker, i need to import some data from a MySQL dump file. I have used the help files here on the site. I have downloaded the ODBC driver. Got it installed and set up and also to some extend connection with Filemaker.

       

      There of course is something i don't understand, cause it does not work. When trying to import data from the sql file via ODBC external data source, i get a password error. I have tried setting a login, but it does not work.

       

      And then there's another thing i don't understand. Through the help files here, it doesn't say anything about how the file gets connected as the external data source. But maybe this will show itself if the password worked.

       

      There are some things that are skipped in the help files. The server/host settings. As i understand it, i don't need the server version of Filemaker to import from sql files or databases. So how will Filemaker know which files is connected to the login ?

       

      I'm on fully a up-to-date Yosemite on Macbook Pro mid2012. I have installed what the help files told me to. And i also afterwards tried to install the driver from the MySQL site. But the problem is the same; password error.

       

      Can someone help me out, i must have missed something or maybe something went wrong ?

       

      All the best

      Carsten

        • 1. Re: How to import data from sql dump file ?
          user19752

          You need running service of MySQL, not dump file.

          If you already have service, you need to put dump file into the service using some MySQL tool to make it live data on the service.

          • 2. Re: How to import data from sql dump file ?
            beverly

            hello Carsten, I'm going to ask some questions.

             

            1. from the title of this thread, did you mean to Import (script step) INTO FileMaker Pro from a 'dump' which is .sql or .csv?

             

            2. does the table (or tables) exist in FMP? or do they need to be created?

             

            3. do you have MySQL also running? if so which version and where? did you set it up from "dump" files (.sql)? and/or are you trying to connect to the running MySQL from FMP?

             

            4. can you specify the help files (links) you are having trouble understanding? Is this FMP Help and/or this forum?

             

            You ask a lot of questions, but let's take them one at a time after a little more detail from you, please!

             

            beverly

            • 3. Re: How to import data from sql dump file ?
              Markus Schneider

              a dump can contain all - schema, control, data...

              there is no way to import sql schema/structure from a sql database into FileMaker.

               

              as user19752 and Beverly mentioned, You can connect the sql db to FM - but there are some questions..

               

              working with sql means often to have a very good knowledge in 'texteditors and/or shell scripting' - so, one can preprocess those files...

               

              The structure/schema and some layouts has to exist in FM before one can import data (without layouts, no data is visible...)

               

              FIleMaker is server-client, the server version just serves (ok, more than that, but its the main task) and a FileMaker client does all the rest (interface, calc's, scripting, etc). That said, You can create a FileMaker file that one can double click. Therefore no more info is needed ('wich file connected'...), like an excel file that is opened.

               

              You don't need the server for working with FileMaker, importing doesn't need a server - but You need the structure in FM

              • 4. Re: How to import data from sql dump file ?
                user19752

                For clarify, my answer was for using MySQL ODBC driver for import.

                 

                Addition for Markus Schneider: there is an option "new table" for import target, I think used only in temporary file or start of new file.

                • 5. Re: How to import data from sql dump file ?
                  abonino

                  Hi Carsten

                  this is not your question, bud more simple: why not working with ESS?

                  You have directly access to the MySQL-Srv with  FileMaker Pro.

                  => Manage External DataSources, DataSources: ODBC

                   

                  regards, Arthur

                  • 6. Re: How to import data from sql dump file ?
                    Markus Schneider

                    correct! my head was fixed in 'related tables' and the dumps from my Oracle time  - but FileMaker can create one table while importing

                    • 7. Re: How to import data from sql dump file ?
                      beverly

                      that is for new TABLE (in the current file into which you are importing). it doesn't give you a choice to NAME the table and will typically take the name from the file used for the import.

                       

                      I do this type of import all the time - usually into a temporary table, in the sense that I rarely import directly into an existing table, unless this is a frequent import with all the same columns - and then these are still "import tables" and the data gets pushed into the real tables after massaging in the import table.

                      • 8. Re: How to import data from sql dump file ?
                        Boblebad

                        Thanks for all the replies

                         

                        So i can't import from a sql file ?

                         

                        If that's right, i don't wanna use it. I don't want to install a server just to get the date from the sql file. Then i'll just get it as csv.

                         

                        I know the problem about the relations, i need to fix them afterwards. But it's a quit simple database with only two or three one-many relations.

                         

                        I can do how ever is the best/easiest way. I'm starting from scratch. So if the best is to create the database first and import, then i'll do that or if it's best to import and FIlemaker can make a new database and put the import into, then i'll do that.

                         

                        The help files is from the Filemaker site. I don't know whether they are in this forum too.

                         

                        Configuring an ODBC client driver and the ones that it links to.

                         

                        Remember i'm new to Filemaker. So if i haven't supplied you with the need information, please ask

                        • 9. Re: How to import data from sql dump file ?
                          user19752

                          You can execute SQL on ODBC sharing of FMP/FMS, but the syntax may be different from MySQL dump file, since "dump" use maximum of features on the db. If there is limitation option on dumping something like "USE SQL-92 standard only", the result may be used on FM SQL.

                           

                          csv import can make new table (and simplest layout for it) on (empty) file, then you can modify it.

                          But, changing table name and field type is not need if you use SQL, you may be use CREATE TABLE query part of dump file.

                          All column types in MySQL is not supported in FM, then I'm not sure which is the better...

                          • 10. Re: How to import data from sql dump file ?
                            Boblebad

                            So if i understand you correctly user 19752, then the easiest way to import the data is to do it via csv ?

                             

                            Then i just need to edit table name and field types and then join the tables again ?

                             

                            The fields are really simple. Just text and numbers. Nothing fancy

                            • 11. Re: How to import data from sql dump file ?
                              user19752

                              Yes.

                               

                              If the csv have field names in 1st line, you can use it when import. Table name will come from csv filename.

                              • 12. Re: How to import data from sql dump file ?
                                Boblebad

                                Well, i got the data imported now, but i have problems getting the link between the tables to work so that the main table gets the data from the two sub-tables. No matter how i relate the tables, i still only get the ID in the main table. It does not get the corresponding data.

                                 

                                Any suggestions ?

                                • 13. Re: How to import data from sql dump file ?
                                  Markus Schneider

                                  each of the 'sub tables' are complete imported, with the key field from the main table (foreign key)?

                                  • 14. Re: How to import data from sql dump file ?
                                    Boblebad

                                    The problem is that it looks like it's done backwards or something is done by sql on the site.

                                     

                                    In the main table, there's the field that carries the ID from the sub table. The sub table has only a PK. I added a FK. I tried connecting the PK from the main table with the FK from the sub table, but that didn't give anything. Then i tried connecting the field in the main table that carries the ID from the sub table, with the PK of the sub table, but that doesn't work either.

                                     

                                    In the sub table, there's an ID, which is PK and then there's the name field (it's author name table). Then there are som more fields that must have bin used for something else on the site. I think it's regarding some search/sorting pages he made. So they are not of any use to me.

                                    1 2 Previous Next