locked
Keep Phone number format RRS feed

  • Question

  •  

    Hi,

     I have phone number column in Excel file. The phone number is in this format: 523-349-0212. When this data imported in to SQLServer file, it is not keeping the format, storing it as 5233490212. The data type of this column is varchar. How to keep this format?

     

    Thanks in advance

    Monday, April 21, 2008 2:23 PM

Answers

  • rksingh024 is giving good advice. Make sure Excel is not formatting your numbers.

     

    If you need to format the numbers on their way into SQL Server, simply use a Derived Column transform and do something like:

     

    Code Snippet

    SUBSTRING(phoneCol, 1, 3) + "-" + SUBSTRING(phoneCol, 4, 3) + "-" + SUBSTRING(phoneCol, 5, 4)

     

     

    Monday, April 21, 2008 2:43 PM

All replies

  • Make sure there is no formatting done in Excel for Phone Number e.g. ###-###-####. It should be text format in Excel. To check the formatting in Excel

    1. Select the PhoneNumber Column in Excel

    2. Right Click and select Format Cells

    3.In Number tab check Category. It should not be custom. If it is then change it to text.

     

    If you require formatting present in database table, then use expression in SSIS to parse the phone number.

    Monday, April 21, 2008 2:39 PM
  • Before you send the Excel output to SQL Server, do a data conversion to DT_STR code page 1252 ANSI.

     

    Another idea is to put a data viewer prior to the destination and run in debug mode to see what is actually being sent to the database.

    Monday, April 21, 2008 2:40 PM
  • rksingh024 is giving good advice. Make sure Excel is not formatting your numbers.

     

    If you need to format the numbers on their way into SQL Server, simply use a Derived Column transform and do something like:

     

    Code Snippet

    SUBSTRING(phoneCol, 1, 3) + "-" + SUBSTRING(phoneCol, 4, 3) + "-" + SUBSTRING(phoneCol, 5, 4)

     

     

    Monday, April 21, 2008 2:43 PM
  • I formatted a text box in a report in MS Access 2016, with field name Cell Phone this way:

    =Format([Cell Phone],"(@@@) @@@ - @@@@")

    1112223333

    returned (111) 222 - 3333


    • Edited by GrandpasGink Tuesday, November 21, 2017 4:19 PM clarification
    Tuesday, November 21, 2017 4:18 PM