Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 31st, 2012, 04:01 AM
Registered User
 
Join Date: Jan 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Moving data

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

Last edited by OldMike; January 31st, 2012 at 04:05 AM..
 
Old February 1st, 2012, 10:27 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)
 
Old February 2nd, 2012, 03:44 AM
Registered User
 
Join Date: Jan 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 3rd, 2012, 12:24 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving on... ryanburnett BOOK: Beginning ASP.NET 4 : in C# and VB 1 August 26th, 2010 03:22 PM
moving mysql data to sql server every 24hours [email protected] SQL Server 2000 4 November 28th, 2006 09:52 AM
Moving data from one table to another twizted ADO.NET 0 January 23rd, 2006 04:05 AM
Moving data from Excel to Access ChrisWalsh Excel VBA 1 April 14th, 2005 12:13 PM
Moving Data stu9820 ASP.NET 1.0 and 1.1 Basics 1 February 17th, 2004 01:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.