Hi

I have extracted geobase data from one programs database and using excel want to import it into another geobase database so I can run some SQL scripting to undertake some comparison checks. The problem is the two have silghtly differring formats. For example

in the first table the address is displayed as below for example with the following columns

Address, Town, Post Code

1 Thames Street, Camden, E12

In the second table where the data is being imported into, it is displayed with additional columns;

House Number, Street Name, Street Type, Town, Post Code

1, Thames, St, Camden, E12

I can extract the numerics easily enough, but not sure how to get them into the House Number column. In the second table, the program uses abbreviations to identify the street type so I need to also convert the existing ones. e.g


Street = St
Close = Cl
Drive = Dr

Not sure if this would be done by a CASE statement? I have some VBA knowledge but at the moment not enough to attepmt this in any reasonable space of time.

Can anyone assist? there are some 600,000+ records to transpose and anyway of saving a bit of work would be appreciated

Mike

There are couple of ways

1. Use Split function / Instr in Excel and split the Street Column and then join
2. Do the same in SQL

Splitting the text in SQL is better (http://sqldud.blogspot.in/2011/06/ho...-multiple.html ) . You can have case function in SQL SErver (http://sqldud.blogspot.in/2007/04/ca...-database.html) to do the abbreviation.

If you have Street as the last column of your query then based on text (Street, Close, Drive etc) it might either split and create a new column.

You can then insert this output to the New Database.

Good luck

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Shasur, thank you. I will have a look but I am not sure I really want to be running SQL scripting against a live database that I am not the owner of. This is why I have all the relevant data extracted to a csv spreadsheet

Mike

Hi Mike

Not a problem. You can use ADO for Opening CSV files and do the same as you do in a query, The Query will be part of VBA and can do the same you intend to do

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)