1 2 Previous Next 17 Replies Latest reply on Aug 23, 2011 4:30 PM by PamelaRoditch

    Lost import order

    PamelaRoditch

      Title

      Lost import order

      Post

      I loose the field import order every time I open FMP to import a daily cvs file.  It would be ideal to arrange the order once only and then use it again next time so as not to have to manually rearrange the order every time.  I used to be able to save the field order, but now nothing I do will help... I'm even trying to set a script for this, but am somehow missing a step and not having any luck. There was an old posting about this in 2009 in the forums, but the link to help that someone posted is no longer working. Does anyone have any help, please? It would be very much appreciated!

        • 1. Re: Lost import order
          philmodjunk

          Is the file hosted over a network?

          A scripted import should preserve your specified import options--including the column to field mapping.

          Can you describe your script and how you use it? That may suggest a reason why this hasn't worked for you.

          • 2. Re: Lost import order
            PamelaRoditch

            Hi! Thanks for responding! Yes, the csv. file is hosted over a network.  I have not been able to set the script up.  When I choose the file it tells me that it does not recognize it, as it is not an FMP file.

             

            • 3. Re: Lost import order
              philmodjunk

              Sorry. I meant, is the FileMaker database hosted over the network?

              When you set up your script with the import records step, you specify that the source file will be a csv file just like you do when manually importing the records.

              Does the csv file always have the same name and always reside in the same shared directory?

              • 4. Re: Lost import order
                PamelaRoditch

                Yes, our database is hosted by someone on our campus and through the network.  I did say the source file would be a csv file. It has a slightly different name everyday (the date changes) but it always resides in the same shared directory.

                • 5. Re: Lost import order
                  philmodjunk

                  When you add Import Records as a step in your script. You can click the "specify data source" check box to bring up the data source dialog. There's a drop down there where you can selct a file type for csv files. This is where you can also click Add File to add a data source reference to the CSV on your shared directory. This is the simple "hardwired" approach you can use to get a simple verions of your script working.

                  Once you've selected a file this way, you can click the Specify Import Order check box to bring up the same dialogs you use when selecting Import Records from the File menu to map columns to field and enable other import options as needed.

                  Try using this script to import records into a copy of your file to see if you can get it to work.

                  Once you've got that working, we can enhance this script by using a variable to compute the filepath and filename of the excel file to be imported. That way we can get something that adjusts the filename each time to refer to the date that is part of the file name. This variable can then be added to the data source dialog to be used in place of the literal reference to a specific file name.

                  Please note that you may need to warn/beg/threaten the powers that be who are responsible for the format of your excel spreadsheets that they cannot modify the format of the spread sheet without first notifying you so that you can update your script to handle the change in format.

                  • 6. Re: Lost import order
                    PamelaRoditch

                    So kind of you to take the time to let me know all these details!!  I will be busy tomorrow trying everything. Thanks a bunch!!

                    • 7. Re: Lost import order
                      PamelaRoditch

                      If you're still there, Phil, - I'm getting a better picture now!!  But when I go to try the script and select the folder it still tells me"08-16-new_lls.csv was not created by Filemaker or is severely damaged and cannot be opened."

                      I have selected the csv files option from the drop-down menu. I guess there's still something I'm doing wrong?

                      • 8. Re: Lost import order
                        philmodjunk

                        That's pretty generic message that tells us FileMaker couldn't open the file.

                        Do you get this message if you select Import Records from the file menu and try to import the data?

                        We need to confirm whether or not the issue is with FileMaker or the CSV file.

                        Can you use a word processor or text editor to open the csv file and look at it?

                        • 9. Re: Lost import order
                          PamelaRoditch

                          Oh, I see. - I don't get that message, or any error message, when i select Import Records.  I can easily open the csv files when they come in, and edit them as well as import them - (there's just the lost order every time). They open without a problem in Word and in Paint.

                          • 10. Re: Lost import order
                            philmodjunk

                            In Paint? Paint can only open bitmapped graphic files. If you can open a csv file in paint it isn't a text file. At least that's what happens in windows xp on my machine. (Never thought of trying to open a text file in paint so I had to try it.)

                            If you can import the file manually via Import Records in the File menu, then you can do it in the script. We'll have to see if we can figure out the problem with your script.

                            Open your script in the script editor. Select the Import Records step. The specify data source check box should be selected. If it's not click it. If it is, select File... from the drop down to the right of it.

                            In the Specify File dialog that is now open, you should see the name of the file listed here. If it is not, click Add file and select it from the shared directory.

                            • 11. Re: Lost import order
                              PamelaRoditch

                              YAY!!! It worked!!!  You're the greatest!!!  Thank you so much!!!!

                              Now it's just that variable thing you were talking about, to be able to choose a different file every day...

                              • 12. Re: Lost import order
                                philmodjunk

                                I see that one of your files was named: 08-16-new_lls.csv

                                Presumably this file was created on August 16 and that all text after the second - is the same for every file.

                                With the next version of this script, you'll need to use some method to calculate the FileName. For purposes of simplicity, I'm going to set this up so that the user enters a date and then performs the script. The script uses the entered date to compute the file and import it. If you have 100% consistency in when the file is put up in the shared directory and when you import it, you may be able to compute this from the current date instead of having to enter it each time.

                                Put a field on your layout such as this global date field: gFileDate

                                Then a script can be run that looks like this:

                                Set Variable [$Path ; //filepathgoes here & Right ( "0" & Month ( Yourtable::gFileDate ) ; 2 ) & "-" and Right ( "0" & Day ( YourTable::gFileDate ) ; 2 ) & "-new_lls.csv" ]
                                Import Records [no dialog; "$Path" ; Add ; Windows ANSI ]

                                In the place where I've typed in //filepathgoeshere, put the exact text you have in the Specify File dialog , but replace the name of the file with the expression here that starts with "Right".

                                To add the reference to $Path in the Import Records step, Select File... again from the drop once you've selected the script step and add $Path by typing it in on it's own row above the existing file name. Keep this original file name on a second line so your field mapping options are preserved.

                                • 13. Re: Lost import order
                                  PamelaRoditch

                                  Hello again! Following your instructions as best I could, this is what my script says now:

                                  Set Variable [$Path;Value:"//filewin:Housingregistry/janet/Right (\"0\"&Month(Yourtable::gFileDate);2) & \"-\"and Right ( \"0\" & Day ( YourTable::gFileDate ) ; 2 ) & \"new_lls.csv\""]

                                  Import Records ["$Path"; Add; Windows ANSI]

                                   

                                  When I clicked Perform, it did the same thing as yesterday; it imported the same records from the initial csv file.

                                  I don't understand- where does a user "Enter a date" and perform the script?

                                  I'm sorry I'm so dense, but I'm missing something... Thank you if you still have patience for this.

                                  • 14. Re: Lost import order
                                    philmodjunk

                                    I'm suprised that worked at all. You seem to have some backslashes that shouldn't be in this expression and the "and" operator where you should have the ampersand (&).

                                    Value:"//filewin:Housingregistry/janet/Right ("0" & Month(Yourtable::gFileDate);2) &  "-" & Right ( "0" & Day ( YourTable::gFileDate ) ; 2 ) & "new_lls.csv"]

                                    gFiledate is a field that you define in your table and put on a layout so that the user can specify the date of the file to be imported.

                                    Yourtable, by the way, is just my "placeholder" for whatever table name you choose when defining this field. g is my short hand for a field with global storage enabled--a field option you can specify for this field so that it is accessible on any layout in your file and so that values one user selects in this field do not affect what other simultaneous users see in the field.

                                    1 2 Previous Next