July 25, 2005 at 2:35 pm
I am trying to export data from SQL server to excel using DTS and copy task. but i have more 10,000 records. and there is excel row limitation. Is it possible to export in multiple sheets in same excel file?
This is quite urgent and would appreciate any help.
Thanks in advance.
July 25, 2005 at 2:41 pm
Yes, the excel sheets will show up under the "table names" in the transformation task. If you need to dynamically create sheets you can try building them as you go.
To build a sheet on an existing Excel file connection: Create Table `New_Excel_Sheet`( `Name`Varchar(20))
hope that helps.
July 25, 2005 at 3:43 pm
Thanks Osoba for your reply.I am already creating a table called Export_sheet for the original spreadsheet
and then as u suggested i tried creating aother table called New_Excel_Sheet . but its not creating any sheet in the excel file.its just replacing the original one.
can you help me what iam doig wrong?
thanks.
July 25, 2005 at 3:51 pm
The Excel row limitation is way more than 10,000 - 65,535 rings a bell - so I am surprised that you are getting an error.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 25, 2005 at 3:53 pm
I am sorry its more then 10,0000.
July 25, 2005 at 4:02 pm
I would check to see if you are using the same sheet name (table name if you are building the sheet via T-SQL). If you use the same name it will return an error that the table already exists.
Post your code for the table creation if you continue to have problems.
..also is it a set requirement to deliver the data via Excel?
July 25, 2005 at 4:08 pm
Iam not writing any code for that..Iam just crating two tables using Execute SQL task before running the copy task.
This is still not working...
Is there any other process?
July 25, 2005 at 4:20 pm
Not sure what your level of SQL Server is but...Another way to accomplish your goal:
1) Go to the database where your source table exists.
2) Right click and choose All Tasks
3) Select Export Data
4) Fill out the connection inof for the Source (your db)
5) For the destination, use the drop down box and choose Excel
6) You can then specifiy the file name and its location
7) You can the either copy the whole table or use a query to limit your result set
8) Make sure you have an Excel sheet named the same as your source table or it will error.
9) If you need make 2 sheets from the same source table, you're going to need to manipulate the task some. It will require saving the export as a DTS package and going from there.
July 25, 2005 at 5:34 pm
Journeyman,thanks.
Atleast i can see another sheet in excel file but second sheet is total empty,it gives only first header.
I still get an excel row exceeds error. any idea?
July 26, 2005 at 8:15 am
You will need to do the export in 2 stages if you use the wizard. It can only point to one sheet at a time (think of it as one export query using the wizard.) I would take a look at record count again or check to see if you can export your data via .csv file. Again, not sure of your requirements. You're real close though.
July 26, 2005 at 8:45 am
Iam not using wizard.I have used some activex task and copy task and so far its exporting to csv file only and then its getting FTP's to remote.
It seems that remote ppl are not able to import csv file into excel seet (this is used for report purpose )coz of huge data so they suggested to have two sheets in same excel file. So i am trying so the same in existing DTS program.
do you have any idea how to export data in other sheet?
or any other idea? thanks.
July 26, 2005 at 8:53 am
Iam not using wizard.I have used some activex task and copy task and so far its exporting to csv file only and then its getting FTP's to remote.
It seems that remote ppl are not able to import csv file into excel seet (this is used for report purpose )coz of huge data so they suggested to have two sheets in same excel file. So i am trying so the same in existing DTS program.
do you have any idea how to export data in other sheet?
or any other idea? thanks.
July 26, 2005 at 9:12 am
Is it about time that your users started using something like Access instead?
With that much data (as you've found), Excel just starts getting ungainly.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 26, 2005 at 2:38 pm
OK in summary, sounds like you have the DTS transformation setup based on your source table (or tables if this is a query) pointing at one Excel file (your target). But because of raw row count (>65K or so..) it's producing an error.
You need: A mechanism to push row count 1-65K into one sheet, 66K-136K to another sheet and so on....
I would do the following to get your deadline met and then would come back and review to clean this up:
First off take into assumption I don't know the data but here goes..I would take your data and insert into a temporary table with an Identity column set to (int,1,1). Off that I would select and insert into yuor excel sheets (in the Excel workbook) where rowid<65K for the first query, and >=65 <136K for the next and so on. It will require you to set up more than one Copy transformation from source to target and will require you to manipluate the query in the Source section of the transformation querying from the temp table.
This allwos a query based solely on row number. Would that work at all?
July 26, 2005 at 9:59 pm
Thanks again Journeyman...looks like iam very close now.
I created two temp tables and another Copy transformation. Now i have two queries and passing separately to copy transformations but it gives same error as its still getting exported to the first sheet.
I think some how I am not able to give pointer to another sheet. I tried many ways but no luck so far. Can you give me some more hints?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply