Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 31st, 2012, 04:01 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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..
Reply With Quote
  #2 (permalink)  
Old February 1st, 2012, 10:27 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
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)
Reply With Quote
  #3 (permalink)  
Old February 2nd, 2012, 03:44 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #4 (permalink)  
Old February 3rd, 2012, 12:24 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
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)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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 max_dev2006@yahoo.com 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



All times are GMT -4. The time now is 12:42 PM.


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