How to import column from dB and force data to a numeric data type?

[Mike Hanby]

Howdy,

I have a MySQL database that I'm pulling several columns into a spread
sheet using the MS Query feature.

One of the columns is a phone number column. Unfortunately, the
database has this column as TEXT and not numeric. So, when I try to get
Excel to apply a Phone Number mask to the column, none of the data shows
up with the phone number mask.

For example, in the database, all phone numbers are entered without
formatting: 2125556234

I'd like Excel to display this column as (212) 555-6234, but it doesn't.
Best guess is because the data is stored as TEXT.

So, is there a way in the query or in the spread sheet to convert all
data in from this column to be stored in the spreadsheet as NUMERIC?

Thanks, Mike

 

[Nick Hodge]

Mike

You can force it to numeric by copying a blank cell and then selecting the
data and selecting Edit>Paste Special...>Value+Add, This can of course be
automated. Bear in mind that Excel does not have masks like Access and
considering the data will never have maths carried out on it, you may be
better to add the brackets and dashes using concatenation and parsing the
string

e.g.

="("&LEFT(A1,3)&") "&MID(A1,5,4)&"-"&RIGHT(A1,4)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[DM Unseen]

why not cast the text column to numeric in the query, this would be
easy, but you need to edit the SQL in msquery since the GUI does not
support this:

select (cast phone as int) as phone, .....
FROM ....

In MSQuery use VIEW->SQL to edit the SQL directly.

DM Unseen

 

[Mike Hanby]

Thanks Nick and DM, your suggestions pointed me down a path that lead to
success

After much googling, here's what I came up with that worked for me.

I ended up doing a Format Cells on the 3 columns that contained phone
numbers, and then wrote a macro to perform "Text to Columns" when the
"AfterRefresh" event ocurred (i.e. after the query refreshed and pulled
all the data in, then do the TextToColumns code).

The key

Here's the code:

==========================
Excel Objects:ThisWorkbook
==========================
Private Sub Workbook_Open()
Initialize_It
End Sub

==========================
Module:Module1
==========================
Dim X As New Class1

Sub Initialize_It()
Set X.qt = ThisWorkbook.Sheets(1).QueryTables(1)
End Sub

==========================
Class Modules:Class1
==========================
Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
FormatPhoneNumbers
End Sub

Public Sub FormatPhoneNumbers()
Dim objRange1 As Range
Dim objRange2 As Range
Dim objRange3 As Range
Dim objRange4 As Range

'Set up the ranges
Set objRange1 = Range("N:N")
Set objRange2 = Range("O:O")
Set objRange3 = Range("P")
Set objRange4 = Range("L:L")

'Parse the N column, Telephone Work
objRange1.TextToColumns _
Destination:=Range("N1"), _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="-"

'Parse the O column, Telephone Cell
objRange2.TextToColumns _
Destination:=Range("O1"), _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="-"

'Parse the P column, Fax
objRange3.TextToColumns _
Destination:=Range("P1"), _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="-"

'Parse the L column, Fax
objRange4.TextToColumns _
Destination:=Range("L1"), _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="-"

End Sub

Nick Hodge said the following on 06/20/2005 16:25:

 

[aaron.kempf]

hey bud; start using databases; Excel is worthless and too buggy to use
as a repository

-Aaron

 

[greaseman]

Golly..... over a year of absolutely stupid, worthless and useless
aaron-isms. I'm thinking more and more that Microsoft canned him after
they discovered he had a lobotomy.