6 Replies Latest reply on Sep 4, 2009 8:34 AM by TSGal

    XSLX Excel Imports

    hschlossberg

      Summary

      XSLX Excel Imports

      Description of the issue

      I'm trying to import a modern Excel file (.xslx format) in 10v3 (Win XP SP3) and can't get it to work as desired.  Surely others have used this feature with success?  Maybe it's just a Windows thing?  Or maybe it's because I don't have a modern version of Excel running on my machine?I have tried it three ways, but only the third works:1) Open the xslx file as a new FM file: I get the Import status window that shows 'importing from: myfile.xlsx' and 'records remaining' (with no record count or file size specified).  Never asks if the first row is data or field names, and nothing else ever becomes of it. 2) Import into an existing file as a new table: asks me which worksheet to import and then pops up the status window as above for about a minute before it finally pops up the Import Field Mapping window.  Import Field Mapping can only be worked on at this point by clicking 'stop' on the status window.  Default is to import into the current table, but as soon as I select 'New Table', I get the status dialog again.  I can click stop to go back to the Import Field Mapping dialog, but as soon as I click the "don't import first record" option, it pops up the status window again as though it has started importing, though it doesn't get anywhere.  (Again, I hit 'stop'.)  No target fields are shown, even though the target table is shown as "new table" and I can see all the source fields and can click the arrow through all the field data.  The 'Import' button is grayed out, presumably because no fields have been mapped (and I can't because there are no target fields appearing to be mapped).3) Import into an existing table of an existing file: asks me which worksheet to import and then pops up the status window as above for about a minute before it finally pops up the Import Field Mapping window. Default is to import into the current table, which display fine.  Clicking the "don't import first record" option takes about 45 seconds to get a reaction.  But from here I can click the 'import' button and it works as expected.  Of course I don't have the desired field names since I am importing into an existing table.    

        • 1. Re: XSLX Excel Imports
          TSGal

          howards:

           

          Thank you for your post.

           

          I only have Excel 2003 on my Windows XP SP3 machine, and it does not have the ability to save as .xlsx format.  I do have Excel 11.5.5 on my Mac, and that saves as .xlsx format.  I can import those .xlsx files without any problem into FileMaker Pro 10.0v3 files or new files.

           

          I don't know if FileMaker Pro is having difficulty with your file, the file format, or some other factor.  I would like to obtain a copy of one of your non-sensitive .xlsx files and try and determine the possible cause(s).  I have sent you a private message (top of this page - right side - X Messages) with instructions where to send the file.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: XSLX Excel Imports
            TSGal

            howards:

             

            I received your Excel file.  This is what I did...

             

            1. I launched FileMaker Pro 10.0v3 Advanced on my Mac using Mac OS X 10.5.8 and created a new file "ImportExcel.fp7".

             

            2. I pulled down the File menu, selected "Import Records -> File...", and opened your file.

             

            3. I was prompted for a worksheet, so I chose "Sheet1", and clicked "Continue..."

             

            4. After a couple of seconds, it said "Importing...".  Several seconds went by, the message disappeared, and a few more seconds before the import mapping displayed.

             

            5. I chose "New Table", and it seemed to hang for some time.  After a couple of minutes, I stopped the process.

             

            6. I launched Excel 2004 on my Mac, and tried to open your file.  I received a prompt that the file was created with a later version and if I wanted to translate.  I did.

             

            7. After several seconds, it came up with a warning that the information exceeded the boundaries of the current version, and to use a later version to see all the information.

             

            8. After the file opened, everything looked fine except the columns past AJ had no data, but the first row was formatted.  For test purposes, I removed all columns past column AJ and saved it as "file.xlsx" and "file.xls".

             

            9. I returned to FileMaker, and I was able to import both "file.xlsx" and "file.xls" into new tables.

             

            10. Thinking there must be some data outside the boundaries, I found and installed a copy of Excel 2008 for the Mac.

             

            11. I opened your original file in Excel 2008, and the first row had columns formatted to column XBH!  No data past column AJ, but since that first row is formatted for data to column XBH, it is interpreted as data in FileMaker Pro.  That is, 16,000+ columns/fields.

             

            12. I returned to FileMaker Pro, tried the import from your main file, and since I now had matching fields from the import of "file.xlsx" in step #9 above, I could see the fields lined up properly with your Excel file, but there were also 16,000+ additional rows lined up in the import dialog box below the original set of fields.  I clicked on New Table, but after several minutes, I stopped the process.  It may eventually show the fields, but I didn't want to wait.

             

            I have sent a copy of the database file along with "file.xlsx" to our Technical Support liaison, and he should send it to you later today.  If you have not received it by end of day today, please let me know, and I'll make sure it gets sent to you.

             

            TSGal

            FileMaker, Inc. 

             

             

            • 3. Re: XSLX Excel Imports
              hschlossberg
                

              Thanks, TSGal.  A couple things of note:

               

              1) All my attempts were done on a Windows XP (SP3) machine running 10.0v3. 

               

              2) Although the status dialog was "wonky", the import mapping dialog had no trouble displaying the source fields/data.  

               

              a) It was the TARGET fields that wouldn't display for a new data table.  

              b) Since the first row was designated as field names rather than data, it seems FM should have ignored all the blank columns.  You said that it had over 16,000 columns formatted; I'm not exactly sure what defines "formatted", but if that means that all someone did was select the entire Excel worksheet and set it to be left-aligned or yellow fill, then FM needs to consider something other than formatting to determine what it is about to import.

               

              c) If 16,000 fields/columns is beyond FM's limit, then FM should have generated such an error right off the bat.  

              Thanks,

              Howard 

              • 4. Re: XSLX Excel Imports
                TSGal

                howards:

                 

                Unfortunately, I am unable to find a copy of Excel for Windows.  However, I was able to get Excel 2008 for the Mac.  The resulting files were then moved to my Windows XP SP3 machine where I have FileMaker Pro 10.0v3.

                 

                Here are some tests that I ran on my iMac (2.4 GHz Intel Core 2 Duo) and my Dell Dimension (Pentium 4 CPU 3.40 GHz) using FileMaker Pro 10.0v3.

                 

                Your Excel file was saved as follows:

                File1.xlsx - 1378 columns (A through AZZ)

                File2.xlsx - 2054 columns (A through BZZ)

                File3.xlsx - 2730 columns (A through CZZ)

                File4.xlsx - 6110 columns (A through HZZ)

                 

                In FileMaker Pro 10.0v3, using Test.fp7, I select Import Records -> File and select "New Table".  Once selected, below is the elapsed time until the new fields showed:

                 

                Mac:

                   File1 - 8 seconds

                   File2 - 14 seconds

                   File3 - 23 seconds

                   File4 - 115 seconds

                 

                Windows:

                   File1 - 13 seconds

                   File2 - 23 seconds

                   File3 - 35 seconds

                   File4 - 192 seconds

                 

                It appears that the ratio of elapsed time to the number of records is not linear.  Therefore, 16,000+ fields will take a long time to read.  I suspect that if I let it run long enough, the fields would eventually display for 16,000+ fields.

                 

                When a column or cell is formatted, information is written to that column or cell.  The cell is no longer blank.  If you load the file into Microsoft Excel, the table expands out to 16,000+ columns.  Therefore, this information is saved in the file and FileMaker Pro is attempting to access it.

                 

                Again, if the spreadsheet is saved without the additional formatting in the otherwise blank cells, FileMaker Pro will import the file a lot quicker.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: XSLX Excel Imports
                  hschlossberg
                     Whether or not this would be considered a bug, I would at least recommend a change for future versions.  The customer who provided me with the xlsx file is pretty Excel savvy and the formatting they applied was applied in a pretty common way (select the entire worksheet using the button in the upper left corner of the sheet and set the formatting).  If FileMaker hopes to target Excel users with ease of use and ease of moving data back and forth, then I would suggest that this is one aspect of potential trouble that could be alleviated through a smarter import routine on FMP's side.
                  • 6. Re: XSLX Excel Imports
                    TSGal

                    howards:

                     

                    Sorry for not mentioning it in my last post.  I definitely reported this to our Development and Software Quality Assurance (Testing) departments to consider changing this in a future release.  I can definitely envision other users encountering the same problem.

                     

                    In the mean time, another workaround, is to have the Excel user save the XLSX file as XLS.  Only 256 colums/fields are saved, so the formatting from the the 257th through 16,000+ is dropped, and the file type imports IMMEDIATELY into FileMaker Pro.  This is the best option I have found at this time.

                     

                    TSGal

                    FileMaker, Inc.