Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to export to excel with merged cell. Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 11:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:01 AM
Points: 132, Visits: 254
Hi,
I am using SSIS 2008 R2,My requirement is to export the data from sql to excel,
but in excel format,three columns in header are merged,due to this only one column below it displaying the data,and rest of the two columns remains empty.

But I want data in those two columns also.


Attached the sample excel file.

In this,Date Column is merged of three cells.

But below this I want to display data of three columns.

Please help me!!!!



  Post Attachments 
1_Delegency_Report.xlsx (5 views, 73.05 KB)
Post #1505487
Posted Thursday, October 17, 2013 1:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
Either unmerge the column, or don't use a header at all.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505510
Posted Thursday, October 17, 2013 5:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:01 AM
Points: 132, Visits: 254
I cant remove the Merge columns,since this is user requirement.

Attached Herewith is excel file for my requirement.
In this First two rows are merged.


My data is exporting correctly,but my problem is numeric data is stored is stored as text.
My user want this in numeric.

Please help me !!
This is urgent to me.

Thanks in advance!!







  Post Attachments 
50_Daily_disb_report.xlsx (3 views, 9.18 KB)
Post #1505624
Posted Thursday, October 17, 2013 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 5,314, Visits: 9,724
Please will you post the DDL (CREATE TABLE statement) and a few sample rows for the table you're exporting from? How many rows are you exporting?

John
Post #1505633
Posted Thursday, October 17, 2013 5:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
avdhut.k (10/17/2013)
I cant remove the Merge columns,since this is user requirement.

Attached Herewith is excel file for my requirement.
In this First two rows are merged.

My data is exporting correctly,but my problem is numeric data is stored is stored as text.
My user want this in numeric.



If your user wants a pretty formatted Excel file, you should use SSRS instead of SSIS.

Anyway, what is the data type of the numeric column in the SSIS data flow?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505638
Posted Thursday, October 17, 2013 5:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:01 AM
Points: 132, Visits: 254


Create Table Temp2
(
channel varchar(10),
forthedatent Int,
forthedate Numeric(12,2),
forthemonthcnt Int,
forthemonth Numeric(12,2),
)

Insert Into Temp2
Values ('Others',0, 0, 16, 1000),
('a',1, 1000.00, 14, 1000),
('TOTAL',1, 1000, 30, 2000)

Select * from Temp2
Post #1505640
Posted Thursday, October 17, 2013 5:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:01 AM
Points: 132, Visits: 254
Hi Koen,

Datatype of the Numeric column in SSIS is UNOCODE [DT_WSTR]
Post #1505641
Posted Thursday, October 17, 2013 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
avdhut.k (10/17/2013)
Hi Koen,

Datatype of the Numeric column in SSIS is UNOCODE [DT_WSTR]


That's a string datatype. And you're surprised the data is exported as text?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505648
Posted Thursday, October 17, 2013 6:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:01 AM
Points: 132, Visits: 254
No,But I want it to stored as Numeric,
Please tell me is there any way to do this.

I also tried to change the SSIS Excel Destination Datatype,thruogh advanced editor,but it is not updated through this.
Post #1505652
Posted Thursday, October 17, 2013 6:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
How do you select the data at the source? Why is the numeric column read as a string column?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse