8 Replies Latest reply on Sep 18, 2012 9:58 AM by Sorbsbuster

    Problem with Import Records - File

    mattb

      Title

      Problem with Import Records - File

      Post

           Using FMP 11 v. 4 Advanced for Mac

           I'm experiencing a peculiar problem with Import Records -> File.  All I'm trying to do is import a text file into a table, but the text files do not have a TXT extension on them as they are created by another program.  My experience is that 2 out of 3 times I get an error message that says "This file could not be translated using the selected file type" - the selected file type being "All" in this case (see screenshot).  What's odd is that 1 out of 3 times, FMP does not come up with this error message and does successfully import the text file into a table.  Also, for the 2 files that generate this error, once I rename their file extension to be TXT, the error message disappears entirely and the import works just fine.  Seemingly, FMP's discrimination of file type when importing records is based on file name (esp. filename extension) ?!  Except when it doesn't, because I had one success without renaming one of the files. 

           So...what to do here?  The import process I'm writing into my FMP project involves importing data from files created by another software (though they are straight-ASCII text files) and obviously I can't be bothering customers to manually rename the files they want to import in order for the routine to work!  I must be able to import with the files given AS IS with their original names.  I thought of a workaround which involved making a copy of the "offending" file and changing the extension to TXT on the copy and running the import process off that file, but it's a no-go, because the error message comes up immediately upon selection of the original file in the dialog box, no opportunity to make a copy before the error occurs.

           The thing that's galling is the inconsistency of the error message appearing, like I mentioned it only comes up in 2 of 3 test cases - in 1 out of 3 cases it works like I would expect it to.

            

           Matt Bloomfield

            

            

      Import_Records_.png

        • 1. Re: Problem with Import Records - File
          Sorbsbuster

               I was disappointed to find that FM will only import a folder of files if they are .txt (not just text files, but .txt.)  On Windows you can have the import run as a script, and have the first step run a simple batch file that converts the files to a txt extension.  I'm sure Applescript will allow the same idea.

          • 2. Re: Problem with Import Records - File
            mattb

                 Sorbuster:

                 On FMP Mac, the import runs in a script as well, but the results are the same.  Since this is going to wind up in commercial software, the file names to be imported are not known in advance, they are user-selectable, and once the file name is selected from the dialog box, the error message immediately appears before the next script step is reached.  Kind of a Catch-22, even if I could get an Applescript to work.

                  

                 Matt Bloomfield

            • 3. Re: Problem with Import Records - File
              philmodjunk

                   You don't actually need an external script (batch, applescript etc) to rename a file. You can rename a file with a container field and Export Field Contents.

                   Have your script insert a copy of the file into a container field. Then use export field contents to export a copy of the file for importing. Use a calculation that examines the file name and adds the missing extension before assigning the file path to a variable for use with the export fields script step. You can export to temporary items so that this file is not retained in the user's system.

                   Note: If it is desirable to do so, you can use the first Insert File script step to open a dialog where the user selects the file to import. Then the script can take it from there, both renaming a copy of the file and then importing data from it.

                   Note 2: in both mac and windows, you can script a batch import of records or a batch insert of files into container fields of any compatible type if you put the folder enclosing the files inside the documents folder. There's a get function that lists all paths to items inside documents so you can use that to generate a list of filepaths to the files you want to import or insert.

              • 4. Re: Problem with Import Records - File
                mattb

                     Phil -

                     I had never used the Insert File function before, so I tried it manually and yes it does work.  But I'm having a problem automating it in a script.  There is an Insert File script step and that step seems to require specifying the the name of the container field in the open table and the name of the file to be inserted into it.  I did this and nothing happened.  It was an empty table, so I appended a blank record and then ran this, still nothing.  I then added at Set Field step so the container field would be the active field and still nothing.  What am I doing wrong?  When I run the procedure manually, everything is fine, the file icon shows in the container field.  But when I run the script absolutely nothing happens.

                      

                     Matt B.

                • 5. Re: Problem with Import Records - File
                  mattb

                       Phil -

                       Never mind the previous message.  The reason it wasn't working from a script is that although the layout I was working with was based on the table with the container field, I didn't specifically have the container field itself anywhere on the layout.  As soon as I added that, the script started to work and your method of Insert File - Export Field Contents worked as advertised.  I was able to run the Import routine on the new automatically-created TXT file and achieve my original objective, so problem solved - thank you.

                        

                       Matt Bloomfield

                  • 6. Re: Problem with Import Records - File
                    Sorbsbuster

                         Phil - I made the assumption (wrongly) that Matt was trying to import a list of files.  When I had that task I had thought of using the Export Field contents but of course I would have needed the list of files to start with, a step that Import From Folder would have done perfectly, except it only works with .txt files.  Hence I added the batch file step to rename the files as .txt.

                         Actually, not completely the case.  When I tagged the ASCI file as .txt it wouldn't import correctly; it pulled all the data into one field.  When I renamed the same file as .tab and imported it imported the way I wanted, with tabs and returns correctly separating fields and records.  Seems an odd behaviour, me thinking that a character is a character, but presumably there is a different translator for tab and txt files.  So the batch file had to first convert all the files to txt to generate the list of files to import, then had to convert the txt files to tab to allow them to import correctly, then another step in the script had to change the .txt from the import list into .tab to actually import the newly-named files.

                         I hadn't thought of exploiting the Get ( DocumentsPathListing ), though I still think I'll need a batch file to automate the move of the files from their current source to the users' documents folder.

                    • 7. Re: Problem with Import Records - File
                      philmodjunk

                           If you need to use a batch file to move the files, it's probably simpler to use the batch files to generate a list of the files in ther current location and then import that data into FileMaker for use in the import script.

                           Hmmm, wonder what would happen if you put a shorcut/alias to the files current folder inside of documents? Haven't tried that so don't know if the get function will return anything but the path to the shortcut, but if it then traced through to the contents of the original folder, we'd have something pretty userful here.

                      • 8. Re: Problem with Import Records - File
                        Sorbsbuster

                             "it's probably simpler to use the batch files to generate a list of the files" - that's what I'm doing when I run the batch file to generate the list of txt files.  It works well, and has made me think that tagging Batch Files on to the Send Event step is a far more powerful tool than I've been recognising.

                             It has inspired me to play with the DDE Execute step also.  If I could find a decent reference work for it it would clearly be very possible to harness the powers of Excel on to FM, with a combination of macros within Excel, Send Event to open Excel files or to run batch files to control system functions, and DDE commands to control what happens once the Excel sheet is opened.

                             The best advice I can find for the 'Reference Work' is to open Excel, record a macro to do what you want to achieve, and then check the syntax of the instructions.  And then 'guess'.

                             I ran the Get (DocumentsPathListing) over the folder, but it simply returns the folder shortcut as a lnk file.