Numeric characters that are varchar type going into Excel column as string

  • I am exporting data from sql2012 into excel sheet, some of the fields (for example productnumber) are varchar sql datatypes that are all numerals, but once in awhile they may contain an alpha letter. So, as it is displayed in Excel , a productnumber that has an alpha letter will get aligned to the left, making report visuals lopsided. Any suggestions on how to doctor the productnumber field while in the sql query before exporting?

    Thanks in advance

  • As far as I've ever seen, the alignment is done in the presentation layer...Excel, SSRS, etc. SQL just pushes the data...

    ------------------------------------------------------------------------------------------------Standing in the gap between Consultant and is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

  • Kevin is correct. If visuals are that much of a problem, either align the columns yourself or create the report is SSRS and define it in your design.

    Exporting straight from SQL to Excel will just provide default formatting, of which includes numerics aligning to the right, and strings to the left.


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Do you have the Excel column set as Text before inserting the data? That would avoid a numeric format.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • southernnevada99 (12/28/2016)

    I am exporting data from sql2012 into excel sheet...

    HOW are you doing the export and what format are you using? For example, are you using CSV/TSV or what?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply