5 posts in this topic

Hey,

I have a Tab-Separated Text File which I want to import to a database that I just created in FileMaker Pro 15. The text file essentially is a log of events, for instance, one line describes an order, another line describes a payment that has been made. The problem is that I somehow need to import all these lines into different tables. I want all lines in the log file that describe a payment in a table called Payments, and all lines that describe an order in a table called Orders for example.

I'm just starting out with FileMaker so forgive me if there is an obvious solution that I'm not aware of. I have a background in software development, so the only solution that I can think of is writing a small program myself that does all the parsing for me and creates separate text files that contain all the orders and payments and other events that have been logged in the file. Then I could simply import each file into the table I need all the data in. 

So to sum everything up, my main question is if there is a way to somehow parse the text file before importing it. I know that you can create custom scripts in FileMaker but I'm not sure if what I need to do can be done just with that.

Thanks in advance,

Mike

Share this post


Link to post
Share on other sites
3 hours ago, 123 said:

one line describes an order, another line describes a payment

You left out the part explaining how to tell these two apart.

Share this post


Link to post
Share on other sites

Hey,

each line has a certain keyword. For example Payment or Order. Those keywords are in the same position in every line.

Thanks for your response,

Mike

Share this post


Link to post
Share on other sites

Posted (edited)

Basically, you have two options:

1. Import the file twice, once into the Payments table and once into Orders. In each table, define a Type field and set it to validate by calculation:

 Self = "Payment" 

and:

Self = "Order"

respectively, validate always. When importing, map the keyword to the Type field.

 

2. Import into a temp table first, then do a find for each type and import the found set into the corresponding target table.

 

Both options can be scripted so that the user only needs to select the file.

Edited by comment
1 person likes this

Share this post


Link to post
Share on other sites

Hey,

thanks for the quick response. The first way you described sounds like a good solution, I will give it a try tomorrow, thank you so much.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • By rt916
      Hey folks,
      I am working on a layout that auto-populates a bunch of data from related fields. One of the fields is called district_number which has the numerical 1-12 value, depending on the district. I am trying to get a script to read the district number, then based on the value have it populate data in another field.
      I started by setting the district_number as a variable, $DistrictNumber
      Then I have the If statement:
      If[$DistrictAddress = "01"]
        Insert Text [Select;xxx::district_address_label; "District 1 P.O. Box 3700 blah blah blah"]
      Else If [$DistrictAddress = "02"]
        Insert Text [Select;xxx::district_address_label; "District 2 P.O. Box 3700 blah blah blah"]
      Else If [$DistrictAddress = "03"]
        Insert Text [Select;xxx::district_address_label; "District 3 P.O. Box 3700 blah blah blah"]
      ... etc...
      End If
      But ti doesn't work. Any help on what I am doing wrong is much appreciated!!
      Thank in advance,
      RT
      here is an image of how it looks

    • By Ali A.
      Hi.
      I have a search field, a user types his customer number in it.
      I want to when a user types his customer number and press inter-script run.
      how can I do this work? I use on object keystroke but when I want to type number script run and user can't type phone numbers.
      how can I fix this?

    • By GisMo
      I have a php script that takes two PDF files and merges them into one file. I am using this in conjunction with the insert from URL script step. 
      The way the script works is it takes two PDFs in container fields and base64encode each files. Then, those two base 64 strings are POSTed via insert from URL.
      I've tested the script forwards and backwards and it does combine the PDF files reliably. The issue is when posting from FileMaker. What typically happens is the first page is combined twice and that's my resultant PDF. 
      My php script checks that both inputs are different and throws an error is they are identical. I don't get any errors there.
      Is there a way to POST a file from insert from URL?
    • By dysong3
      I have a number of scripts that have worked perfectly for years in previous versions of FileMaker, however in FMP 15 they causse FM to crash.  They only way I have found to avoid the crash is to use the debugger and to use the "step into" button.  That of course is a very cumbersome workaround. You can see one of the culprits below.  Anyone have any ideas what's wrong here?
       
       

    • By dkey
      IN my db I have the following fields
      IMAGE (CONTAINS 14 FRAMES FROM A MOV FILES AS A JPG FILE)
      FILEPATH (TEXT FILE AS FOLLOWS: file://Data/Videos/MVI_9887.MOV)
      VIDEO A Container file where I would like to see the "file://Data/Videos/MVI_9887.MOV"
      In the DB I have several different records how can I create a SCRIPT AS SUCH
      GO TO FIELD VIDEO
      OPEN file://Data/Videos/MVI_9887.MOV
      I Don't want to keep the video file in the db but only look at it when needed
      The various INSERT QUICKTIME, URL and so on require me to set the actual file path for each MOVIE file and this is time consuming as I have more than 1200 records, therefore 1200 movie path to insert
      Anu help?
      Thanks a lot