export data to Excel Template

Question

export data to Excel Template

vani_r14

SSCrazy

Points: 2843
More actions
February 16, 2010 at 7:57 pm

#139019

Hi all
It's me again with my Export Issue to Excel... I have been given a template with few macros and column headings specified and have to import data into it from SQL SERVER Table. (please find attached is the template)
i have created a table which stores all the information in SQL SERVER.
select c.firstname, c.lastname, c.dob, c.postcode into summary
from client
Table Data
FirstName Last Name DOB
amy mango 01/01/1984
annie peach 10/10/1986
June rose 02/04/1996
Kendra loop 01/04/1975
Sam thorn 02/10/1984
John Clement 10/10/1994
....
1500 rows
I have added an OLEDB source, Data conversion task to convert all field above to unicode string and an excel destination
It flows through the first two bits but fails on inserting the data to Excel Destination with errors. 0x80004005, 0xC020907B along with other errors. I have some how managed to fix it.
I have tried to insert the data with the Template Spreadsheet closed and SSIS throws errors. If I import data with Spreadsheet open it executes all three tasks but populates data into the spreasheet starting from rows 10000 and above. leaves the first 10000 rows blank.
The column headings are specified in Cols A4 to C4 and I need the data populated under those columns starting from Row A5
Can some one please give some suggestions as to where I could be going wrong...
thanks
Vani

Viewing 8 posts - 1 through 7 (of 7 total)

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

vani_r14 SSCrazy Points: 2843 More actions · Answer 1

Hi All

I have figured out how to do this and its now working fine. In case if someone needs to know how to for future all I did was in the Excel Destination Editor Window instead of selecting the SheetName I added the following -

Set the Data Access Mode to - SQL Command

and in the SQL Command Text - Specified the following Query =>

select * from [Therapy_List$A4:C4]

where Therapy_List - was the Sheet Name, A4:C4 were the columns in the Spreadsheet with the column headings. Click Preview in the Excel Destination Editor Window and the column names in Cells A4 to C4 will now come up as Column Headings.

When I executed the package it populated the data correctly from Row A5 below.

Please find attached is the Screenshots for reference.

This link was quite helpful - http://www.ssisguru.com/2009/10/export-to-excel-for-given-range-of.html

I will post out the complete solution shortly.

Cheers

Vani

vani_r14 SSCrazy Points: 2843 More actions · Answer 2

Hi all

If any one needs it, here is the complete solution on how to export data into excel when the excel file format is specified.

Thanks

Vani

bclark 68131 Valued Member Points: 71 More actions · Answer 3

This is great. But I have a problem. I can get the connection and the data to push to excel in the correct columns, I can see the header row. But it always puts the data starting on line 2. No matter what row I specify. Do you have any ideas what I am doing wrong?

select * from [sheet1$E6:G6]

I am using SSIS 2008 and SQL server 2008

vani_r14 SSCrazy Points: 2843 More actions · Answer 4

Hi

Can you please try putting nothing in the columns just do a standard export to excel and see what it does from there. i.e.

In the Excel Destination Editor Window- Select Table or View from the Data Access Mode Drop Down box and select the name of the Excel Sheet in the Drop down box below that. Have you tried this as well and is it working or not working. Can you please check in Excel if that row has some specific conditions set etc and try that.

I am no expert in this area but will try and help you as much as i can

Vani

ishchopra SSC Journeyman Points: 96 More actions · Answer 5

Hello Experts,

Instead of giving cell range if we have to import data cell by cell then what is the procedure ?

cheers !!

vani_r14 SSCrazy Points: 2843 More actions · Answer 6

[font="Comic Sans MS"]Hi Isha

Is that cell by cell import from a table in a database to Excel via ssis... If it is then you are able to use the SSIS Import Export Wizard to do this task and it is a lot easier. Or would you like to do it so each row gets saved as a seperate file if so then this is complicated...

Please clarify

Cheers

Vani [/font]

SummiRS2 SSC Enthusiast Points: 155 More actions · Answer 7

Hi,

This article(http://www.c-sharpcorner.com/UploadFile/d2dcfc/C-Sharp-create-excel-report-chart-contained-with-mark-designe/[/url]) shows a simple way to export data to template file(Excel). You can try to use mehtod in it.

Hope helpful!

Day after Day, Tomorrow is...
E-ICEBLUE[/url]