Post reply

How to export data from sql server to excel 2007

  • September 11, 2009 at 11:50 am

    #133945

    Hi Everyone,

    Please help me in resolving the following issue.

    I have a sql server table that has different datatypes like (int,varchar,nvarchar,float,decimal) and I want to load the data into excel 2007 workbook.

    I am using microsoft.ACE.Ole db driver 12.0 to load into excel 2007 in SSIS. I am able to load the data into excel 2007 but the problem is the data which is in int,float,decimal datatypes in sql server are getting converted to text in excel 2007.

    The following is the test table which can be used in sql server and also I am attaching the excel 2007 spreadsheet.

    create table test1(

    sno int,

    sname varchar(20),

    grade int,

    marks1 float,

    marks2 float,

    total decimal(8,2))

    insert into test1 values(1,'xyz',4,25.5,25.25,50.75)

    insert into test1 values(1,'xyz1',5,25.5,25.25,50.75)

    insert into test1 values(1,'xyz2',6,25.5,25.25,50.75)

    insert into test1 values(1,'xyz3',7,25.5,25.25,50.75)

    insert into test1 values(1,'xyz3',7,25.5,NULL,25.5)

    Thanks,

    sai

  • September 14, 2009 at 9:49 am

    #1053302

    Hi ALL,

    I request to please suggest solution.

    Thanks,

    Sai

  • September 21, 2009 at 6:19 pm

    #1056521

    Sai,

    I've never been able to fully control the formatting in an Excel worksheet, so may not be able to fully satisfy what you're trying to do. These notes from my experience may help you and/or they may inspire someone more expert than I to explain an approach we can both use.

    If you're using the worksheet that you attached as a starting point (copied from an empty file), then you may want to try setting the format for all the columns to the desired setting -- only column A, "sno" is set to numeric (and with two decimal places, although it's coming from an integer). Even that hasn't always fully worked for me. In some cases I've found that the data will follow the example of a first data row which I've built in row 2 under the column headings. And then, I've found that if the worksheet is left open in Excel while the package is running, the formatting is more like that sample row than if the file is opened only by SSIS.

  • September 22, 2009 at 6:59 am

    #1056731

    Sai

    If you are willing to abandon SSIS and use Excel VBA (ADO) you will have

    complete control of what happens with the format. I don't think that MS has

    done a great job to integrate Excel and SSIS sorry to say.

    /Gosta

  • September 22, 2009 at 9:54 am

    #1056911

    saidwarak01

    Go the the following link. Great discussion and a vast amount of sample code for exporting / importing data to / from Excel. It may have what you need.

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

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