Post reply

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

  • December 28, 2016 at 9:49 am

    #316362

    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

  • December 30, 2016 at 6:39 am

    #1920076

    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 ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

  • December 30, 2016 at 6:46 am

    #1920077

    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.

    Thom~

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

  • December 30, 2016 at 7:12 am

    #1920081

    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

  • December 30, 2016 at 7:36 am

    #1920087

    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)

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

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