G-NAF Bulk Import Script: build an Australia national address database

Address verification had never been a simple topic. It involves quite a lot resources if you want to achieve an acceptable result. Previously we had tried to use Australia PAF file for verification purpose. However it is not as good as we expected: the PAF file is for parcel delivery, so for instance, you can identify a P.O. box through the PAF file, but it cannot be treated as a residential address.

Now good news is, through years and years negotiation, PSMA finally opened its G-NAF (geo-coded national address file) to public this year. Additionally PSMA also confirmed the it will keep the G-NAF refreshed every 3 months (May, August, November, and so on). But because data.gov.au is being rebuilt, the latest version I could find is August version. Well, my purpose of this post is to help you import address data into SQL Server quickly. So if you want to know more about G-NAF, simply go to their website.

The G-NAF file downloaded from data.gov.au includes the T-SQL script for database setup as well as the script for constraints setup. However importing the raw data could be a bit trouble due to amount of tables and raw data files.

To make life easier, you can try below bulk load statements. I generated them by looping tables and file names, what you need to do is just replacing "{G-NAF Folder}" with your own folder. Hope they can save you a bit time. Enjoy.

 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\ACT_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\ACT_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\ACT_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\ACT_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\ACT_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\ACT_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\ACT_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\ACT_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\ACT_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\ACT_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\ACT_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\ACT_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\ACT_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\ACT_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\ACT_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\ACT_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\NSW_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\NSW_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\NSW_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\NSW_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\NSW_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\NSW_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\NSW_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\NSW_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\NSW_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\NSW_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\NSW_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\NSW_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\NSW_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\NSW_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\NSW_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\NSW_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\NT_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\NT_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\NT_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\NT_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\NT_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\NT_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\NT_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\NT_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\NT_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\NT_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\NT_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\NT_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\NT_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\NT_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\NT_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\NT_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\OT_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\OT_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\OT_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\OT_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\OT_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\OT_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\OT_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\OT_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\OT_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\OT_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\OT_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\OT_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\OT_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\OT_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\OT_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\OT_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\QLD_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\QLD_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\QLD_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\QLD_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\QLD_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\QLD_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\QLD_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\QLD_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\QLD_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\QLD_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\QLD_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\QLD_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\QLD_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\QLD_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\QLD_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\QLD_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\SA_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\SA_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\SA_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\SA_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\SA_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\SA_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\SA_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\SA_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\SA_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\SA_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\SA_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\SA_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\SA_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\SA_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\SA_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\SA_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\TAS_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\TAS_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\TAS_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\TAS_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\TAS_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\TAS_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\TAS_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\TAS_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\TAS_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\TAS_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\TAS_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\TAS_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\TAS_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\TAS_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\TAS_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\TAS_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\VIC_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\VIC_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\VIC_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\VIC_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\VIC_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\VIC_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\VIC_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\VIC_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\VIC_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\VIC_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\VIC_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\VIC_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\VIC_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\VIC_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\VIC_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\VIC_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\WA_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\WA_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\WA_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\WA_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\WA_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\WA_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\WA_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\WA_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\WA_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\WA_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\WA_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\WA_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\WA_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\WA_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\WA_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\WA_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  

Peronal Template in SQL Server Management Studio

It has been a while since my last post. Well, there are so many jobs need to be done during this busy season. When we are busy, we need to find a way to improve our productivity, or simply free us from repeated jobs. Today let’s see a very important function in SSMS Template.

When we have SSMS installed, by default there will be standard system templates available. You can open the Template Browser by using the hot key “Ctrl + Alt + T”, or by using the command item from the menu View -> Template Explorer.



Applying the template is quite simple, simply find the template you want to use, double click it, and then replace the variables in the template. Let’s see an example:

Assume I want to create a new function by applying the function template. To do so, I double click the “Create Inline Function (new menu)” template under the Function category.



At this stage a new query window with default pattern should appear.



As you can see the template pattern contains comments section, script section, and lots of “<>”. These are the parameters can be quickly replaced. As specified by the instruction comments, we can use hot key “Ctrl + Shift + M” to replace the parameters, as shown below.



After we input the values, click OK button and we will have the function populated within the query window.



This is a standard function provided in SSMS. Obviously it can save us lots of time. But we can make it even better, i.e. we can create our own parameterised template!

Firstly, we need to have a blank folder. In below screenshots, I created a personal template folder by right clicking top-most node “SQL Server Templates” and selecting New -> Folder, and then named it “Personal Template”.



Right click the newly created folder and this time, I selected New -> Template. Now we will have an empty template available in the folder.

To fill in our template, we need to right click the template, and then select Edit, as shown below.



Now the template should be opened in SSMS. Just like example shown below, to indicate the parameter, we need to use the format “<name="" type="" example="">”. To make it simple, in this example I just created a "If exists, select" pattern.

 USE <database_name, sysname, master>  
 GO  
 IF EXISTS (SELECT 1 FROM sys.tables where name = '<table_name, sysname, dummy_table>')  
 BEGIN  
      SELECT * FROM <table_name, sysname, dummy_table>  
 END  

After we done everything, we can save it by using “Ctrl + S” or using the menu bar Save command. Close the template query window, now double click our personal template and press “Ctrl + Shift + M”, you should see parameter window appears.



Fill in values for the parameters, close the popup window by pressing OK button, you should see the result as expected. Handy function, isn’t it?

Power BI & Telstra Wifi Hotspot API

A while ago I wrote the post to show how to call Telstra Wi-Fi Hotspot API through C#. Today let’s see how easy we can achieve the same result (even better) by using Microsoft Power BI.


Power BI, in short, is an powerful BI tool for data extraction, transformation, and visualisation.  I would highly recommend it if you haven’t tried it yet.  At this stage you can sign it up for free, by applying a trial office 365 license.


Now back to the topic, today my plan is to call the API by using Power Query embedded in Power BI, and then display the result on the workspace. To do so let’s open Power BI Desktop, and press Edit Queries button on the ribbon.

On the Query Editor window (Power Query), open dropdown menu by press New Source button, then select Blank Query option.

Now click Advanced Editor button on the ribbon to open the editor window

Now Advanced Editor window pops up, and we can start to type in Power Query statements. To demonstrate how powerful the power query is, I decided to split the procedure into two: create a function to get oAuth token, and then call the Wi-Fi Hotspot API.


To create the function in Power Query, firstly let’s write below query (replace consumer key and consumer secret)

 let  
   authUrl = "https://api.telstra.com/v1/oauth/token",  
   authKey = "client_id={consumerkey}&client_secret={consumer secret}&grant_type=client_credentials&scope=WIFI",  
   getJson = Web.Contents  
       (authUrl,  
         [  
           Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],  
           Content = Text.ToBinary(authKey)  
         ]  
       ),  
   jsonDoc = Json.Document(getJson)  
 in  
   jsonDoc  

Click Done button and the oAuth result should be shown on Power Query window directly, as shown below

So far everything is good. What we need to do now is to introduce parameters into the query, and then parameterise the query. To do so firstly create new parameters

For demonstration purpose, I treat consumer key and consumer secret as parameters, as shown below

Now change our query a bit, I have highlighted changes in the screenshot attached below

Once you done everything and click Done button, the function window appears

Now you can test the function by entering consumer key and consumer secret then pressing the Invoke button.

The function should work as expected. Time to write the 2nd script. Following the same step to open a blank query window and input statements like below.

 let  
   accessToken = fnGetToken("{consumerkey}", "{consumersecret}")[access_token],  
   latitude = "-37.8103713989",  
   longitude = "144.99530273",  
   radius = "1000",  
   wifiUrl = Text.Format("https://api.telstra.com/v1/wifi/hotspots?lat=#{0}&long=#{1}&radius=#{2}", {latitude, longitude, radius}),  
   accessTokenHeader = "Bearer " & accessToken,  
   getWifiJson = Web.Contents(wifiUrl, [Headers = [#"Authorization" = accessTokenHeader]]),  
   wifiJson = Json.Document(getWifiJson),  
   table = Table.FromList(wifiJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
   wifiTable = Table.ExpandRecordColumn(table, "Column1", {"lat", "long", "address", "city", "state"}, {"lat", "long", "address", "city", "state"})  
 in  
      wifiTable   

Now things could be a little bit tricky. Depends on your Power BI desktop setting, you might get the result without any issue. But in most cases, you could see two different types of warnings: connection warning and privacy warning. In short it is because we are passing oAuth token from a different Url (https://api.telstra.com/v1/oauth/token vs https://api.telstra.com/v1/wifi/hotspots). Let's see how we can resolve these conflicts:


The first warning you might see is the connection warning.

Click the button “Edit Credential” will lead us to a popup window. Because we retrieve our token by passing consumer key and consumer secret into the function, we can simply select Anonymous method and then select the Url https://api.telstra.com/v1/oauth/token as the target.

The next window is about data privacy.

On the Privacy Level screen, select the Url https://api.telstra.com/v1/oauth/token, and then select “Public” as the scope.

After all these steps, the data table should be generated automatically

Now we can go back to the Power BI by clicking the button “Close & Apply” at the top left corner. Drag a map visual onto the workspace, drop Lat and Long columns onto the corresponding map fields, and that is it