2 Replies Latest reply on Jul 25, 2013 12:18 PM by pwilliams@rm.com

    Integrating Royal Mail PAF CSV

    pwilliams@rm.com

      Title

      Integrating Royal Mail PAF CSV

      Post

           Hi all,

           does anyone have any experience of the best way top do this ? It is a 1.5Gb CSV of all UK postcodes.

           Rightly or wrongly, I have gone with the following :-

             
      •           Import CSV into new table / FM file
      •      
      •           Create a single field for each entry that concatonates the whole address into a single string
      •      
      •           Create a duplicate field in the main database file (containing customer data) and use this as the relationship
      •      
      •           When entering a postcode, it uses the concatonated field to narrow down the address

           The problem I have in combining all the various elements of the address into a single string. The simple way is to simply say postcode & " " & House No etc etc etc but there are so many variables that might or might not be empty (such as no house number but there is a house name.

           I am struggling to come up with a calculation field that accurately put this together. It consists of so many elements that might or might no be there but is basically goes something like this :-

             
      •           There could be a Flat number or house number or house name (sometimes 1 of these sometimes all 3)
      •      
      •           There will be a street name and maybe a sub street (sometimes one or sometimes both)
      •      
      •           There will be a district and then sometimes a town as well

           I have been using IsEmpty to ascertain if there is data in the fields and it is mainly the combination of the 3 elements on the house number line that is causing me trouble.

           Any help appreciated.

           Thanks

           Peter