7 Replies Latest reply on Aug 3, 2009 7:19 PM by comment_1

    Runtime with Import problems

    TKnTexas

      Title

      Runtime with Import problems

      Post

      I am so confused on this point:  IMPORTING.  I am creating a runtime to supplement our ancient accounting system by building a searchable check register.  I cannot install Filemaker on my work computer (not an approved application).  So I create a runtime that I can use to do my tasks.  I am just not grasping the IMPORT script step. 

       

      Every time we print checks, we print the check register to a spool file.  I have had some successes in my calculations and other script steps, but not with importing.  I want to make it totally seamless to end-user once the question of check date is answered.   

       

      I apologize for the length.  I am just going to give as much information to make it easier to garner some help on this. I have developed a run-time schema that has a master folder (SPARTA) with sub-folders for each run-time that I use.  The path looks like:

       

      /SPARTA/APCKReg

      /SPARTA/download

      /SPARTA/MTG_UTIL 

       

      The spool file of each check register is placed into the download folder.  The $$filename is "apckrgMMDDYY.ap".  MMDDYY is the check date.  I have scripted this in small easily tested modules.  In the file there are two tables:  1) IMPORT and 2) FINAL. 

       

      Since the import file is a full of garbage records I import to IMPORT and do the clean up there.  After all of the garbage is gone, I can easily move the information to the FINAL table. Here is what I am doing:

      1)     Delete all records in the IMPORT table. 

      2)     Prompt for the check date, which is stored in a global.  I am still learning about using variables. 

      In my IMPORT step I have:       

      GOTO layout IMPORT_SCREEN which is in my IMPORT table.       

      SET VARIABLE step I create the file name from the gCheckDate field.        

      IMPORT for all file types with a path of file:sparta/download. 

       

      There I get "apckrgMMDDYY.ap cannot be found and is necessary for the completion of this script".  It does reference the correct filename based on the date.  I click OK and I am at the /SPARTA folder.  I move to the download folder and manually import.  Each row of the text file goes into one record one field.  Calculation fields parse out the data correctly.   

       

      The rest of the script completes by finding the blank rows, header rows, and such, deleting these.  I am left with the individual records for each check.  That is the correct found set. 

       

      3)     I am ready for moving the records to the FINAL table.       

      GOTO layout FINAL       

      IMPORT for Filemaker Pro type records with a path of file:sparta/APCKReg/IMPORT

      I get an error message 'file not found' and I am still in the /sparta folder.   

       

      I am at a roadblock.  Once the records are in FINAL everything works as I want.  I love how ver10 shows summaries in BrowseMode.

        • 1. Re: Runtime with Import problems
          TSGal

          TKnTexas:

           

          Thank you for your post.

           

          From your signature, it appears you are deploying this on Windows XP.

           

          Where is the "download" folder located?  Is it located on the C: drive?  Is it a network drive?  Have you tried specifying the entire path using using "filewin:/"?

           

          If the download folder is located on the C: drive in a folder entitled "Downloads", you should specify the file as:

           

          filewin:/C:/Downloads/

           

          ....followed by the name of the file you are trying to import.

           

          If you are in a user directory, be sure to specify the full path.

           

          However, according to #3, it appears one of your downloads folder is inside your FilePath at sparta/APCKReg/IMPORT/.  Is this correct?

           

          If you are still in the "sparta" folder, there is no need to reference it again.  What occurs if you us use:

           

          APCKReg/IMPORT/<filename>

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Runtime with Import problems
            TKnTexas
              

            Thanks for the help.  I did have it where the structure was

             

            \SPARTA\APP1-Name

                        \APP2-Name

                        \APCkReg

                        \Utilities

                        \Download

             

            These little runtimes I am working on are my training.  I have 4 databases developed under 3/4 and 5/6, that I want to move to 10.  I want to market these.  Utilities was the first I created.  I have to put this on a network drive. If I am out sick on the day to pay utilities, a co-worker has to access and upload to the accounting system.

             

            I moved the \Download to be inside \APCkReg, \Sparta\APCkReg\Download.  This worked for the download.

             

            I did run into another issue though.  The files are text files. They are not CSV or Tab-delimited.  They have an ending that is *.ap.   I am selecting "all file types" for the download. 

             

            I started reconciling the data.  It didn't reconcile after doing the clean up.  What I discovered is that seven records are truncated on import.  They happen to be checks that have a comma in the name.  Filemaker is deleting all information with a comma.  I can open with Notepad, Wordpad, TextEdit, MSWord, and Excel.  Only Filemaker is doing this. 

             

            I don't know how to intercept it.  I don't want to have to use Notepad or TextEdit to fix all the files for the year to remove commas.  I have no idea how many of the vendor names in our AP system's 4000+ database have commas in them.  Any insights on this would be appreciate. 

             

            However, the download issue is resolved.  Thanks for your help.

            • 3. Re: Runtime with Import problems
              TSGal

              TKnTexas:

               

              Thank you for the additional information.

               

              CSV and Tab-delimited are types of text files.  It sounds like the information you are importing is being recognized as a CSV (comma-separated) file.  If you have commas within the data, make sure the field information is surrounded by quotes.

               

              If you can bring the information into Excel, then do so and save it as XLS or CSV.  You should then be able to import the data properly.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Runtime with Import problems
                TKnTexas
                   Yeah.. I figured it was getting treated as comma delimited.  Unfortunately, natively I cannot affect the output.  I can have the vendor database administrator eliminated commas as we identify the vendor records.  I wanted this to be a simple process, avoiding the interim clean up in Excel.  But I guess I won't be able to do that.   I appreciate the help though.  I am learning so much .. and realizing how much more v10 have to offer.
                • 5. Re: Runtime with Import problems
                  etripoli
                     Is there some other text delimiter being used, that FM doesn't recognize.  If so, it's possible to import each line in the text file into a field in the FM database, and perform scripts/calculations to parse the information into other fields.
                  • 6. Re: Runtime with Import problems
                    TKnTexas
                       What I have discovered about my situation:   In scripting the import step, I select ALL FILES AVAILABLE that was the only option that would make the text files ending with .ap display.  In reality these are very simple text files.  They are not delimited in any form or fashion.  However, the vendor names may have a comma.  Filemaker 10 advanced does use the comma to define two fields on the line, it terminates all data after the comma.  This is done on import.   I used a REName command to change all from *.ap to *.txt.  Importing as tab-delimited (there are no tabs) brings in all of the data.
                    • 7. Re: Runtime with Import problems
                      comment_1
                        

                      I believe that if you select 'Show: [Tab-Separated Text Files]' you should still be able to select a .ap file for importing (even though it is grayed out), and it will be treated as a .tab file.

                       

                      Whereas if you select All Available, it will be assumed to be CSV, and it will ask for as many target fields as there are commas in the first record + 1. If subsequent records have more commas, the remaining data will not be imported for lack of a target field.