9 Replies Latest reply on May 20, 2013 7:06 AM by philmodjunk

    importing from an microsoft access table

    jfalberg

      Title

      importing from an microsoft access table

      Post

           I'm trying to migrate from a Microsoft Access table over into Filemaker using the import wizard but there does not seem to be a direct method.  I tried exporting from access to a dbf file only to find my column headers truncated if above a certain length, and I cannot seem to have any success when trying to use excel or even an xml file unless I'm doing something wrong here. 

            

           I tried xml as follows:

           From Access: right-click desired table, Export, XML File, XML, XSD (defaults)

           From Filemaker in desired database: File -> Import Records -> XML Data Source -> Specify XML Data Source, File, Continue

            

           I get the following FileMaker Pro message:  XML/XSL information is not enough to proceed with import/export

           Help!

        • 1. Re: importing from an microsoft access table
          philmodjunk

               I believe that you would need to set up an XSLT "Grammar" before the XML import will work.

               What problems did you encounter exporting from Acces to Excel to Filemaker?

               Exporting to text (tab) or csv should also be possible and are also formats from which FileMaker can Import.

               Also, If you set up an ODBC connection, you could import your data into a Filemaker table via ODBC.

          • 2. Re: importing from an microsoft access table
            jfalberg

                 File -> Import -> File (*.xlsx)

                 Display worksheets (selected one worksheet) -> Continue

                 Import Field Mapping shows blank source fields to target fields of f1-f16.  I even tried "Don't import first record (contains field names) and nothing changed.

                 I would like to try ODBC as hopefully that will work directly from an Access Table, I'd imagine I'd setup one file at a time this way.  Otherwise I don't want to do tedious gruntwork to get everything in right.

                  

            • 3. Re: importing from an microsoft access table
              jfalberg

                   So far as trying to setup an ODBC for Microsoft Access I am getting the following error message as I'll need to fix this somehow:

                   The setup routines for the Microsoft Access Driver (*.mdb, *.accdb)
                   ODBC driver could not be found.  Please reinstall the driver.

                    

              • 4. Re: importing from an microsoft access table
                philmodjunk

                     Importing records from Excel is a very common method for importing data into FileMaker. You can even drag and drop the Excel file onto the FileMaker application icon to both create a brand new FileMaker file and import the data from the spreadsheet all in one go.

                     Did you try clicking the arrow buttons to preview more than the first row of data from the spreadsheet?

                     Have you actually tried to import from the excel spreadsheet to see what you get?

                     Have you opened the excel spreadsheet and inspected the spreadsheet? The first row or rows may be blank.

                      

                • 5. Re: importing from an microsoft access table
                  jfalberg

                       This image is the excel spreadsheet I am trying to import into a new table in filemaker as I just tried the drag and drop method as follows:

                       Specify Excel Data -> only Statute appears, press Continue

                       First Row Option -> choosing Field Names, OK

                       save as "Statute Converted 1.fp7

                       Layout #1 displays F1-F16 vertically and Layout #2 displays a grid showing f1-f16 as column headers along with 312 blank rows

                  • 6. Re: importing from an microsoft access table
                    davidanders

                         FM Migrator Pro   $200 single user
                    http://www.fmpromigrator.com/products/fmpro_migrator/index.html

                         Access to Filemaker Capabilities
                    http://www.fmpromigrator.com/services/access_to_fmpro_service.html

                          

                    • 7. Re: importing from an microsoft access table
                      philmodjunk

                           I can't imagine that FMMigrator is needed just to import data from an Excel File.

                           

                                Layout #1 displays F1-F16 vertically and Layout #2 displays a grid showing f1-f16 as column headers along with 312 blank rows

                           Are both layouts based on the same table?

                           What view mode did you select when examining the data on these layouts? Table View? List View? Form View?

                           For a quick view of your data, select table view and then click the Modify button in the status tool bar if you don't see all the columns of data that you expected.

                           The blank rows that you are seeing could be the result of the viewing options selected for that layout or it could be a layout based on a different table than the one into which the data was imported.

                           Also, what I see in your spreadsheet represnts at least 13 fields and at least 17 records. Of course there is probably more columns (fields) and rows (records) than what is visible in the screenshot.

                      • 8. Re: importing from an microsoft access table
                        jfalberg

                             I tried viewing in all table, list & form view.  While the printscreen showed only a portion, there are actually 16 columns (to column P) and 312 rows in total.  I was able to get another Filemaker expert to convert this from his end but am perplexed as to why I was unable to successfully.

                             I now have 7 different tables in a filemaker database file which I placed on the filemaker server and now want to odbc reference it so that my c#.net application can make references and add/update to it like it did with an Access Database.  Maybe I should post this as a separate question as to how I can best accomplish this if possible?  I'm using c#.net since I felt there were limitations I could not do in a filemaker environment but still want to tie this in with the overall project.

                        • 9. Re: importing from an microsoft access table
                          philmodjunk

                               I was able to get another Filemaker expert to convert this from his end

                               There are quite a few different options that can be selected during the import records process. I'd guess that your expert selected different options than you did and I would recommend reviewing with your expert exactly how they did it as this could help you get a better understanding of the process.

                               A new thread on your ODBC connection sounds like a good idea. After a thread has generated a number of responses back and forth, other forum participants tend to skip it. Starting a new thread with your new question tends to invite them back into the discussion.