10 Replies Latest reply on Dec 7, 2013 12:31 AM by bartprins

    Problems with import textfile in database FM Pro 12

    bartprins

      I want to make a database where i can import data which is delivered to me in a textfile (UTF-8) with fixed length fields.

      I will need too edit a couple of fields and add a couple then export it again to a txt with fixed lenght.

       

      I tried to make a database where there is one importfield in a table where i import the textfile so that the complete file is one field named ImportedText in the table ImportTbl.

      After that i have made a couple of tables for instantance: CustomerTbl, AdressTbl and DeliveredproductsTbl.

       

      I made a script where it “reads” the textfile and uses a couple Trim(Middle) functions so i can fill the fields in the responding tables.

      For example in the textfile there is starting at position 115 a persons name which consists of 10 characters and i want that to import in the CustomerTbl at the field Name i would use this:

       

      Make a new record and go to field and insert the right tekst from the textfile:

      Insert Calculated Result [Select; CustomerTbl::Name;Middle(ImportTbl::ImportedText; 115 ; 10)]


      So far so good, and this works.

       

      However i assumed (should never hav done that hahaha) that the delivered txtfile would be in a really fixed format, but there can be variations.

       

      First Example: there can be multiple customers in the textfile, which isn’t a big problem cause i could do this by hand and copy and paste the tekst from one customer into the ImportedText field.

       

      Second example; and here’s my problem (thanks for reading such a long story i’m already oblidged )

       

      The customer in the textfile could have multiple adresses and products in the textfile:

      So a txtfile would look like this:

       

      10NameFirstNameEtcEtc (where 10 is the recordID number which is CustomerRecord)

      20Adress1CityEtcEtc (again where 20 is the recordId number which is AdresRecord in this case)

      20Adress2CityEtcEtc

      40Product1EtcEtc

      40Product2EtcEtc


      I think i should use a loop where i look in the text file cause the 20 (Adress) and 40 (Products) could be multiple, but don’t really know how to begin cause they can vary in numbers. The way i solved it was only good when a record looked like this :

       

      10NameFirstNameEtcEtc (where 10 is the recordID number which is CustomerRecord)

      20Adress1CityEtcEtc (again where 20 is the recordId number which is AdresRecord in this case)

      40Product1EtcEtc

       

      I’m getting these files from a third party where this is used a standard.

       

      Perhaps some of you have done this before, even have a sample or something i would be much oblidged and thanks for reading again!

       

      Bart Prins

        • 1. Re: Problems with import textfile in database FM Pro 12
          mikebeargie

          An anonymized sample of your data file would probably allow the most suggestions.

           

          It appears that your first two characters are not a RecordID, but rather a RecordType indicator.

           

          In previous files where I've had a RecordType and RecordID in the same line, I've always split the lines up into separate tables and processed each table separately.

           

          Again, a sample will help get more suggestions.

          • 2. Re: Problems with import textfile in database FM Pro 12
            Mike_Mitchell

            Hello, Bart.

             

            Your instinct is correct; one way to accomplish this is through a loop. I'm assuming this text block comes in in a repeating fashion, such as:

             

            10NameFirstNameEtcEtc

            20Adress1CityEtcEtc

            20Adress2CityEtcEtc

            40Product1EtcEtc

            40Product2EtcEtc

            10NameFirstNameEtcEtc

            20Adress2CityEtcEtc

            40Product1EtcEtc

            40Product2EtcEtc

            40Product2EtcEtc

             

            and so forth. In other words, each customer begins a block including that customer's address and product information.

             

            Proceeding on that assumption, my suggestion would be to take advantage of the Position function. Do something like this:

             

                 Position ( text ; "10" ; 1 ; 2 ) - 1

             

            This will extract the position of the last character in the first customer's information (assuming there's more than one). You can then use the Left function to grab that text:

             

                 Left ( text ; Position ( text ; "10" ; 1 ; 2 ) - 1 )

             

            Then, you can reset the original text block (using a variable, of course, to preserve the imported information) to strip away the first value using the Right function:

             

                 Right ( text ; Length ( text ) - Position ( text ; "10" ; 1 ; 2 ) + 1 )

             

            What I suggest, then, is you loop through, extracting each customer's information in turn using this method, until you come to the last customer. When that happens, your Position function will return a value of -1. When that happens, you know you've hit the last block and can just use the remaining text (i.e., the result of the Right function above).

             

            Inside your individual text block, you can run another loop to extract the individual pieces - the customer, address, and product elements. I suggest you loop through and use the GetValue function, testing the first two characters. If they're "10", add them to the Customer table; if "20", to Address, and if "40", to Product. You can accomplish all this through a single utility layout and a global key field with relationships to all three tables, set to allow record creation.

             

            HTH

             

            Mike

            • 3. Re: Problems with import textfile in database FM Pro 12
              paulsbird

              Bart,

              You are on the right path with using loops. If i understand you correctly the file would look like this?

               

              10SmithJohn (where 10 is the recordID number which is CustomerRecord)

              20999BoogieBoogieAve (again where 20 is the recordId number which is AdresRecord in this case)

              20AnywhereNH00000

              40WidgetBlue

              40WidgetGreen

               

              10DoeJane

              20333MainSt

              20SomewhereAL00000

              40WidgetRed


              etc

              etc

              • 4. Re: Problems with import textfile in database FM Pro 12
                bartprins

                Hi Paul and both Mikes

                 

                 

                Thanks for reading !

                 

                I've made a little sampledb to make it visual as Mike suggested perhaps this will make it more clear.

                I've made a sample Importedtext field where in real life i would open a txt-file and copy this and paste in to the Importedtext field for now.

                Perhaps one of u has time to look at it.

                 

                @Mike mitchell - i think i understand what you mean, i'm using the Trim Middle function for this but don't exactly understand how and where to use the getvalue in to the loop, i'm a newbie at this.

                • 5. Re: Problems with import textfile in database FM Pro 12
                  paulsbird

                  Hi Bart,

                  This is just a basic example of how to get your data into your database.

                  Import your text file into a temp database table (t_import)

                  This creates a record for every line of your text file.

                  Now you can use a Create Data script to parse the data,

                  Take a look at the Create Data script. It is a simple reformat of your data.import that should work as long you have only a single customer per record.

                  You will need to adjust the Middle() statements to fit your fixed field length requirements.

                  You will need to relate the tables i created by Customer ID, but I think you'll get the logic of the script.

                  Paul

                  • 6. Re: Problems with import textfile in database FM Pro 12
                    paulsbird

                    the script - this has no error trapping and I am sure there are more elegant ways.

                     

                    Go to Layout [ “t_Import” (t_Import) ]

                    Show All Records

                    Go to Record/Request/Page [ First ]

                    Loop

                     

                    If [ t_Import::fieldtype = 10 ]

                    Set Variable [ $LastName; Value:TrimAll(Middle(t_Import::field1;3;16);0;0) ]

                    Set Variable [ $FirstName; Value:TrimAll(Middle(t_Import::field1;17;16);0;0) ]

                    Go to Layout [ “Cust_Table” (Cust_Table) ]

                    New Record/Request

                    Set Variable [ $MyCustomerID; Value:Cust_Table::CustomerID ]

                    Set Field [ Cust_Table::Last_Name; $LastName ]

                    Set Field [ Cust_Table::First_Name; $FirstName ]

                    Go to Layout [ “t_Import” (t_Import) ]

                    Go to Record/Request/Page [ Next; Exit after last ]

                    End If

                     

                    #Check for Address

                    If [ t_Import::fieldtype = 20 ]
                    Set Variable [
                    $Street; Value:TrimAll(Middle(t_Import::field1;3;32);0;0) ]

                    Set Variable [ $Zip; Value:TrimAll(Middle(t_Import::field1;35;5);0;0) ]

                    Set Variable [ $City; Value:TrimAll(Middle(t_Import::field1;40;32);0;0) ]

                    Go to Layout [ “Address_Table” (Address_Table)

                    New Record/Request
                    Set Field
                    [
                    Address_Table::CustomerID; $MyCustomerID ]
                    Set Field [
                    Address_Table::Address; $Street ]
                    Set Field [
                    Address_Table::Zip; $Zip ]
                    Set Field [
                    Address_Table::City; $City ]
                    Go to Layout [
                    “t_Import” (t_Import) ]

                    End If


                    #Check for Product

                    If [ t_Import::fieldtype = 40 ]

                    Set Variable [ $Descr1; Value:TrimAll(Middle(t_Import::field1;3;32);0;0) ]

                    Set Variable [ $Descr2; Value:TrimAll(Middle(t_Import::field1;33;32);0;0) ]

                    Go to Layout [ “Product_Table” (Product_Table) ]
                    New Record/Request

                    Set Field [ Product_Table::CustomerID; $MyCustomerID ]

                    Set Field [ Product_Table::Product Description 1; $Descr1 ]

                    Set Field [ Product_Table::Product Description 2; $Descr2 ]

                    Go to Layout [ “t_Import” (t_Import) ]

                    End If
                    Go to Record/Request/Page
                    [ Next; Exit after last ]

                    End Loop


                    • 7. Re: Problems with import textfile in database FM Pro 12
                      ch0c0halic

                      I basically agree that this is a good script to start with.

                       

                      However,

                       

                      1. I’d structure it with an If(t_Import::fieldtype = 10) - ElseIf(t_Import::fieldtype = 20) - ElseIf(t_Import::fieldtype = 40) - End If(). No reason to do the extra if testing when one each is all that is going to work. And IMHO it simplifies the logic. This also allows for a single Go To Next Record (EAL) at the end which I find is easier to debug.

                       

                      2. I’d clear the variables at the end of each loop so I can see exactly what data is loaded for each pass. Otherwise while debugging you have a set of old and new data. Seeing address data while the Product is processing may be distracting.

                       

                      I might start with each set field grouping for a record type as a sub-script so I can debug/use each one separately.

                       

                       

                      Alternatively

                       

                      You could create a series of calculations to do the field separations. You’ll end up with a set of calculations for each record type. Then add a record_id field and script adding a unique record_id value to each record so they can be linked to the primary Customer record (type 10). Then an import of each record type into its table, requires 3 imports, does all the record creations at one time.

                       

                      Something like this:

                       

                      set variable “$record_id” ; Max (Cust::Cust_id) + 1

                      Go to First Record

                      Loop

                      Set Field

                       

                      If (t_Import::fieldtype = 10)

                      set variable [“$record_id” ; “$record_id + 1"

                      end if

                       

                      GTNR (EAL)

                       

                      End Loop

                       

                      Find t_Import::fieldtype = 10

                      import into Cust_table

                       

                      Find t_Import::fieldtype = 20

                      import into Addr_table

                       

                      Find t_Import::fieldtype = 40

                      import into prod_table

                       

                      Align the appropriate calculation fields for each record type to its destination tables fields.

                      1 of 1 people found this helpful
                      • 8. Re: Problems with import textfile in database FM Pro 12
                        bartprins

                        Hi Paul,

                         

                        Thanks for your time !!! I'm much oblidged !!

                         

                        I used your script and it works like a charm. One litte question is i use the Middle Function instead of your Trimall Middle what is the difference between these two?

                         

                        Chocohalic thanks for your input too, i will trie to convert the script as it is now to your version, Ps what does GTNR (EAL) mean, Go to Next Record and then..?

                         

                        Again thank you all for helping me out, i like Filemaker and this forum more and more!

                         

                        Bart Prins

                        • 9. Re: Problems with import textfile in database FM Pro 12
                          ch0c0halic

                          Go To Next Record, Option - Exit After Last