Integrating Royal Mail PAF CSV
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.