10 Replies Latest reply on Oct 8, 2014 1:45 PM by philmodjunk

    .xls file won't import on server, but will on client

    BrentHedden1

      Summary

      .xls file won't import on server, but will on client

      Product

      FileMaker Server

      Version

      13.02

      Operating system version

      Window Server 2012

      Description of the issue

      I've got different .xls files that are being imported through the server from a PSOS process.  On occasion, the import process will stay stuck at the Import script step.  It doesn't execute the line, and it throws no errors.  I know it's stuck there because I've placed some sendmail command pre and post, and I never receive the post message.  I also tried the same thing through a server-side schedule, with the same results.  The same file will import perfectly fine when I import it through a normal FMP client connection.

      -----------------------------------
      The virtual user server created just hangs there and never logs out.  It doesn't show as using any system resources through the statistics screen, but other real user notice a slowdown when this happens.

      Once this happens, any other PSOS processes won't work.  Server just keeps piling on the virtual users but never logs them out.  The only way to stop this is to restart the FMSE service through the command line.

      Steps to reproduce the problem

      Not all .xls files have this behavior upon import.  I can provide a few sample files to test with that have given me problems.

      Expected result

      The file to be imported into the designated table, or at least an error is thrown so I can catch for it.

      Actual result

      Import script step doesn't execute, no error thrown.  Server script just hangs with no completion.

      Exact text of any error message(s) that appear

      none, unfortunately

      Configuration information

      FileMaker Server 13.02.295
      Virtual Windows Server 2012

      Workaround

      Import the same file using a client connection.

        • 1. Re: .xls file won't import on server, but will on client
          TSGal

          BrentHedden1:

          Thank you for your post.

          What error trapping do you have immediately following both the import script step and the Send Mail script step?  You may want to consider updating a field with Get (LastError) to determine what is the exact error message.

          Since the import appears to be hanging, what is the name of the .xls file?  How large is the .xls file?  Is the .xls file located in the Documents or the Temporary folder (or sub-folder of those folders)?

          Are any records being edited in the table that the .xls file is updating?

          Any other information you can provide about the computing environment at the time the script fails may be helpful.

          TSGal
          FileMaker, Inc.

          • 2. Re: .xls file won't import on server, but will on client
            BrentHedden1

            The script uses the Set Error Capture [On] and Allow User Abort [Off] at the beginning of the script.  So I should see something in the Server Event log if the import gives an error.

            There are two levels of error trapping that I'm using.  First is a Set Variable  with the Get (LastError), then an If statement that executes a warning log if there the error code is anything other than zero.  Second I have a Send Mail (via SMTP Server) that send me a status email of where in the script it's at.  I get the email just pre of the import line, but not the one just post.  (If a file imports even with errors, I get the post email so I know it's setup right).

            The filenames are nothing special or extrordinary.  One of them is    Nth_Cymer2_VMware_092514.xls   I do have a contingency if the filename is over 50 characters long, as I know that is a limitation when importing on FM Server.  I can provide a copy of the actual files that are not being imported upon request.

            All of the files are located in sub-directories of the Documents folder.  This is setup through the Container Data Path when using the External Storage option with container fields.  So all of the files that are being imported are in the correct area.

             

             

             

            • 3. Re: .xls file won't import on server, but will on client
              TSGal

              BrentHedden1:

              Thank you for the additional information.

              From your initial post, I assumed that certain files are not being imported consistently.  If specific files are not being imported at any time, then I'd like to see one of those files.  Check your Inbox at the top of this page for instructions where to send the file.

              Are these files being generated by Excel?  If not, what occurs if you open the file in Excel and then save it again.  Does it then import correctly?

              If you put the file on your local drive, are you able to import the file into FileMaker Pro manually?

              If you temporarily change the file name to something simple (like a.xls), does the file then import?

              Any other information you can provide may be helpful in narrowing down a possible cause.

              TSGal
              FileMaker, Inc.

              • 4. Re: .xls file won't import on server, but will on client
                TSGal

                BrentHedden1:

                I received your files.  Thank you.

                The files import without any issues under FileMaker Server 13.0v4 on both Windows Server 2008 R2 and Mac OS X 10.9.4.  Here are the steps I took:

                1. I created a FileMaker Pro database file based on the first file (I can't remember which one).  It created 8 fields (F1... F8).

                2. I created an "Import Records" script as follows:
                   Set Error Capture [ On ]
                   Allow User Abort [ Off ]
                   Set Variable [ $filepath ; Value: "file:" & Get (DocumentsPath) & "Nth_Cymer2<rest of filename>.csv" ]
                   Import Records [ No dialog ; Source: "$filepath" ; Add ; UTF-8 ]

                3. I created another script "Server" that only contains the script step:
                   Perform Script on Server [ Wait for completion ; "Import Records" ]

                4. I turned on Network Sharing.

                5. I uploaded the file to the server.

                6. I copied the two files (.xls and .csv) to the server in the Documents folder.

                7. I ran the "Server" script.  The records were imported.

                8. I re-opened the "Import Records" script, and changed the Set Variable step to include the second file, and changed the Import Records step to show Worksheet.

                9. I ran the "Server" script, and the records were imported.

                Let me know what I'm doing differently than you.

                TSGal
                FileMaker, Inc.

                • 5. Re: .xls file won't import on server, but will on client
                  TSGal

                  BrentHedden1:

                  I received your files.  Thank you.

                  The first file "LAW..." Excel file would not import manually.  I then opened the file in Excel, and saved it again with the same name.  This then would import manually.

                  The second file, "Nth..." Excel file imported manually without any changes.

                  After uploading the file to FileMaker Server 13 and placing the two files in the DocumentsPath, the first file (LAW...) would import fine, and I did get the spinning beach ball with the second file.

                  Noticing that the "Nth..." Excel file had graphics, while the "Nth..." CSV file sent previously only had text, I created another Excel file from the "Nth..." CSV file, and used that for the import, and it worked.

                  Since the only change appears to be the graphics in the original "Nth..." Excel file, I have sent the file to our Development and Testing departments for review to determine why this is failing during Perform Script On Server.  When I receive any feedback, I will let you know.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: .xls file won't import on server, but will on client
                    BrentHedden1

                    Thank you very much for testing and confirming these issues.

                    These files come from one of our vendors, so we have no control over how it's created.  They really are meant to be viewed, hence all of the graphics.  We've written complicated import scripts to take the data and re-format it into our own quote system. 

                    We could re-save these problem files into a different format and import that way.  But this causes extra steps for the end users that we're trying to avoid and shouldn't be necessary.  This is actually the temporary work-aound that we've implemented until a more permanent solution can be placed in. 

                    • 7. Re: .xls file won't import on server, but will on client
                      TSGal

                      BrentHedden1:

                      Our Testers were able to determine the issue.  In the formula for cell M28, remove the quotes around TBD.  The import will then work, locally and on the server.

                      The information and findings have been sent to Development for additional review.  When more information becomes available, I will let you know.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: .xls file won't import on server, but will on client
                        BrentHedden1

                        Thank you and all of the testers for the work you've placed into this.  I've confirmed that once I either change or remove the formula from M28, I'm able to import the file as expected.

                        What still doesn't make any sense is why this file(s) are able to be imported on a client, but not the server.  I suppose this is what the Development department will be determining. 

                        I need to restate that having to change the file(s) before importing is not a feasible solution for us.  There are just way too many of these that we handle on a daily basis. 

                        • 9. Re: .xls file won't import on server, but will on client
                          TSGal

                          BrentHedden1:

                          The issue has been replicated by Testing and sent to Development.  I understand changing the formula is "not a feasible solution", but until a change in our code is implemented, at least you know what is the cause.  You may want to consider talking to the vendor who creates these files and have them remove the quotes around TBD, as this works both locally and via Perform Script on Server.

                          TSGal
                          FileMaker, Inc.

                          • 10. Re: .xls file won't import on server, but will on client
                            philmodjunk

                            An entry in the Known Bugs List has been linked to this Issue Report. Any Comments/Questions/Suggested Corrections should be posted here or in a new thread. Please do not post such comments to the Known Bugs List thread.