AnsweredAssumed Answered

Problems with import textfile in database FM Pro 12

Question asked by bartprins on Dec 4, 2013
Latest reply on Dec 7, 2013 by 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

Outcomes