AnsweredAssumed Answered

Integrating Royal Mail PAF CSV

Question asked by pwilliams@rm.com on Jul 25, 2013
Latest reply on Jul 25, 2013 by 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

      

Outcomes