Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob Boles
This isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:
> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in
a
> long text field (defined as ntext (16)). When I run the query and export t
o
> Excel the text is truncated at 255 characters. How do I get all the text t
o
> export?
> Thanks,
> Bob Boles

Exporting Data From SSIS Package to Excel

I currently have a export that takes data from my SQL Server 2005 DB and exports it into Excel. This process works correctly. My excel template has the first row headers and the data is dumped in the row after the header. I would like to know if it is possible for me to add borders around my data without doing it within the template? I don't know how much data is going to be exported so I can't put borders within the template. I put borders around the headers to see if it will copy the formatting down to the data and it didn't. Thank You for any help.

I do not think you can do this using SSIS.

Thanks.

|||

I hope that isn't true I really have to find a way around that. Why does Microsoft limit the access to the Office Components being automated?

|||

Jerry8989 wrote:

Why does Microsoft limit the access to the Office Components being automated?

It doesn't, you just have to work a little harder.

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx
http://msdn2.microsoft.com/en-us/library/ms136007.aspx

I think automation is the only way you'll get this accomplished.
|||

Jerry8989 wrote:

I hope that isn't true I really have to find a way around that. Why does Microsoft limit the access to the Office Components being automated?

Well, for one SSIS is an ETL tool and as such isn't designed to be making fancy looking excel sheets, powerpoint presentations, etc... It's designed to read data, transform it, and load it somewhere. So the fact that you need pretty looking visual effects in your excel sheet, doesn't really warrant the need for that feature in SSIS.

Exporting data from SQL table to Excel File - How to delete rows before inserting new

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Quote:

Originally Posted by LimaCharlie

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Create a view that limits number of exported rows and use it in DTS package instead of a table.

Good Luck.|||hi iburyak,

just the same, the records are being appended to the excel file. what i want is to delete the rows in the destination file before inserting new records.

thanks.|||Did you create a view that removes unwanted records?
Execute a view on a Server side and make sure this is the data you want to export, don't create a view to select * from table_name it will not correct a problem.

You need to give me more information then just I want to delete records.
It is a wrong approach to load all records and delete on destination side you need to load only necessary records.|||Hi Iburyak,

The Question is... this excel file is getting populated everyday and everyday before porting data to the excel the old data has to be erased and new data filled up.

Any help on that.

Thanks in advance..

Exporting data from SQL table to Excel File - How to delete rows before insertin

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Thanks a lot.Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=135&rl=1

ddave|||LimaCharlie, take a look at this post:

http://www.dbforums.com/showthread.php?t=981661

Cheers

kbk|||hi kbk,

that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?

thanks a lot.|||What i want is to delete the rows in the destination file before inserting new records.

I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.

1. Create an activeX script as below: -This will remove the worksheet.

Function Main()

Dim srccsvfile
Dim objExcel
Dim objWorkbook, objWorksheet

srccsvfile = "C:\Test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts = False

Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet = objWorkbook.WorkSheets("New_Table")
objWorksheet.Activate
objWorksheet.Delete 'this is removing the worksheet instead of rows
objWorkbook.Save 'you must save the change otherwise in trouble

objExcel.Workbooks.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function

2. Create Execute SQL task as below: - create the worksheet again
CREATE TABLE `New_Table` (
`OfficePK` VarChar (8) ,
`DealerGroupFK` VarChar (4) ,
`StateManagerGroupFK` VarChar (20) ,
`OfficeType` VarChar (1) ,
`OfficeName` VarChar (255) ,
`OfficeAddress1` VarChar (255) ,
`OfficeAddress2` VarChar (255) ,
`OfficeSuburb` VarChar (255) ,
`OfficeState` VarChar (255) ,
`OfficePostCode` VarChar (255) ,
`OfficeCountry` VarChar (255) ,
`OfficePhone` VarChar (255) ,
`RIOfficeID` VarChar (50) ,
`RIFranchiseName` VarChar (255) ,
`VPServerIP` VarChar (50) ,
`LastReceived` DateTime
)
3. Then use data transform as you are doing now.
Make sure that the destination table name is New_Table and the connection is Excel.|||Hi TerryP,
I'll try this.
Thank you very much. =)sql

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

sql

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

Export wizard exports views as tables

Hi

I have a problem using SQL 2005 Export wizard.

I have 2 databases : DB1 and DB2

DB1 has X tables and Y views
DB2 has W tables and Z views

I want to export only few selected views from DB1 to DB2.

In Import and Export wizard, I select these views and execute the export-package. Completed Successfully.

And finally the problem:

All views that i exported from DB1, are not placed in DB2 as views, but as TABLES!

Why?

Waiting for your quick answer,
Martin

This is by design.

It may happen that some of the tables on which the view depends are not imported at all, which can cause the view definition to fail.

Thanks,

Kuntal

Export wizard exports views as tables

Hi

I have a problem using SQL 2005 Export wizard.

I have 2 databases : DB1 and DB2

DB1 has X tables and Y views
DB2 has W tables and Z views

I want to export only few selected views from DB1 to DB2.

In Import and Export wizard, I select these views and execute the export-package. Completed Successfully.

And finally the problem:

All views that i exported from DB1, are not placed in DB2 as views, but as TABLES!

Why?

Waiting for your quick answer,
Martin

This is by design.

It may happen that some of the tables on which the view depends are not imported at all, which can cause the view definition to fail.

Thanks,

Kuntal

export to excel, file not found

With sp1 installed, when a user exports a chart to excel via url
(ReportServerUrl/MyReport&rs:Command=Render&rs:Format=EXCEL ... I set this up
as a link on the report) everything works when the user has office xp
installed, but with office 2000, the user can only use the save as feature,
and then open the excel rendered chart. If the user selects "open" from the
dialog prompt, they get an error:
C:\Documents and Settings\User\Local Settings\Temporary Internet
Files\Content.IE5\KJYHDDL\Chart[1].xls Could not be found. Check the
spelling of the name ...
Is this the result of a browser setting, or server security on my end?
Thanks for any help.Have you installed Reporting Services SP1 on the server? This is required
for Office 2000 support.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JeffW" <JeffW@.discussions.microsoft.com> wrote in message
news:396BF27F-0526-43BC-BB78-D59DDE5B6058@.microsoft.com...
> With sp1 installed, when a user exports a chart to excel via url
> (ReportServerUrl/MyReport&rs:Command=Render&rs:Format=EXCEL ... I set this
> up
> as a link on the report) everything works when the user has office xp
> installed, but with office 2000, the user can only use the save as
> feature,
> and then open the excel rendered chart. If the user selects "open" from
> the
> dialog prompt, they get an error:
> C:\Documents and Settings\User\Local Settings\Temporary Internet
> Files\Content.IE5\KJYHDDL\Chart[1].xls Could not be found. Check the
> spelling of the name ...
> Is this the result of a browser setting, or server security on my end?
> Thanks for any help.|||Brian,
Thanks for your reply.
I have installed sp1 on the server, and verified that both my dev
environment and server are running version 8.00.878.00. Also, the excel
export does work, when the user chooses to save the file and then open it
(office 2000). It only fails when they try to open the exported chart
directly from the dialog that says "You are downloading the file ... Would
you like to open the file or save it to your computer ...". And it only
happens when to office 2000 or less users.
After installing sp1 on the server, I did not reboot or anything, but
confirmed that the version matched my dev environment that also has sp1.
Should I create the report from scratch or are there files/timestamps I
should be looking for on the server related to the renderer/exporter?
Thanks,
JeffW
"Brian Welcker [MSFT]" wrote:
> Have you installed Reporting Services SP1 on the server? This is required
> for Office 2000 support.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "JeffW" <JeffW@.discussions.microsoft.com> wrote in message
> news:396BF27F-0526-43BC-BB78-D59DDE5B6058@.microsoft.com...
> > With sp1 installed, when a user exports a chart to excel via url
> > (ReportServerUrl/MyReport&rs:Command=Render&rs:Format=EXCEL ... I set this
> > up
> > as a link on the report) everything works when the user has office xp
> > installed, but with office 2000, the user can only use the save as
> > feature,
> > and then open the excel rendered chart. If the user selects "open" from
> > the
> > dialog prompt, they get an error:
> >
> > C:\Documents and Settings\User\Local Settings\Temporary Internet
> > Files\Content.IE5\KJYHDDL\Chart[1].xls Could not be found. Check the
> > spelling of the name ...
> >
> > Is this the result of a browser setting, or server security on my end?
> >
> > Thanks for any help.
>
>

Export to Excel Range

I have an SSIS package that exports data to a named range in Excel, except that instead of putting the data in the named range, it adds the first row of data to the first row below the range. The spreadsheet is not protected. Why doesn't the data get put into the named range?

When I'm done testing the SSIS package, I want to protect the spreadsheet and enable edit for the named range so that data can be written using the SSIS package. Will enabling the named range be necessary?

Dan

Dan:

I have not used named ranges in particular to populate data, but in general, when you use Excel as the destination you do not have the option to "overwrite" the data as you do in a Flat File destination. The problem you are experiencing could be because your package is updating the same Excel file during each run.

Does it always insert a new row one row below the named range? What happens when you run the package 2 or 3 times; each time it should be inserting new records at the n+1th row.

The issue of populating Excel destination has been discussed in previous threads and Rafael Salas apparently as a solution for it. I am not sure if his solution addresses populating Named Ranges particularly.

Rafael has a blog at blogspot.com, and is also listed in the Top Answerer box. When you click on his name it provides details of his profile.

|||The first step in the SSIS package is to execute a file server component that copies and renames a spreadsheet template to the spreadsheet I'm trying to populate. I've deleted the data range and removed any read-only settings. What has worked is to create and use a header range which then populates data in the first row immediately following. Thanks for the response and reference to Rafael Salas.

Export to Excel Range

I have an SSIS package that exports data to a named range in Excel, except that instead of putting the data in the named range, it adds the first row of data to the first row below the range. The spreadsheet is not protected. Why doesn't the data get put into the named range?

When I'm done testing the SSIS package, I want to protect the spreadsheet and enable edit for the named range so that data can be written using the SSIS package. Will enabling the named range be necessary?

Dan

Dan:

I have not used named ranges in particular to populate data, but in general, when you use Excel as the destination you do not have the option to "overwrite" the data as you do in a Flat File destination. The problem you are experiencing could be because your package is updating the same Excel file during each run.

Does it always insert a new row one row below the named range? What happens when you run the package 2 or 3 times; each time it should be inserting new records at the n+1th row.

The issue of populating Excel destination has been discussed in previous threads and Rafael Salas apparently as a solution for it. I am not sure if his solution addresses populating Named Ranges particularly.

Rafael has a blog at blogspot.com, and is also listed in the Top Answerer box. When you click on his name it provides details of his profile.

|||The first step in the SSIS package is to execute a file server component that copies and renames a spreadsheet template to the spreadsheet I'm trying to populate. I've deleted the data range and removed any read-only settings. What has worked is to create and use a header range which then populates data in the first row immediately following. Thanks for the response and reference to Rafael Salas.

export to excel file

I've created a dts package that exports data to an excel. The data exports okay, but I'd like to make a few changes on how the data is exported.

First, when the data exports to the excel file, it always creates a new excel sheet. How can I map the data to the existing sheet in the Excel file? I have an existing sheet named 'Sheet1'. I've deleted the other two sheets in the excel file, so there is only one sheet. When I created the destination table in the dts package for the data transformation task, I specified the name as 'Sheet1', but when it exports, it creates a new sheet named 'Sheet11'. I haven't been able to correct this.

Second, I'd like to either ammend data to the last row in the excel file, which I'd have to know the last record that was inserted from the SQL table. Or probably much easier, simply drop the existing sheet in the excel file and simply re-create and insert.

How can I do this?

Thanks,
-D-I figured it out...please disregard.

Export to Excel exports HTML formatting too

Is there a way to control exporting only the data in a report and not the html with it?

By "HTML" I think you mean the layout of the report and the style information associated with each item.

The Excel Renderer is a layout renderer, which means it tries to maintain the closest visual fidelity possible to the report definition and other renderers (HTML, PDF). There isn't a way to export only data using the Excel rendering extension.

You might be able to use the CSV renderer for your data; CSV is a data-only renderer and can be opened in Excel.

export to excel

I'm experiencing an issue where when I export my report to

excel every number in my report exports as something other than a number. Every

number excel field (cell) has a green arrow in the upper left corner which will allow

me to manually convert to a number but this is a major hassle.

I've tried converting the fields in the report using cdbl and cdec but to no

avail.

Anyone have a suggestion on how to fix this?

Thanks

The issue seems to be caused by the formatnumber() function. Doe formatnumber by default change the data's type by default?|||No one else has experienced this issue?|||

The FormatNumber function always returns a String. See this doc page for reference:

http://msdn2.microsoft.com/en-us/library/xfta99yt.aspx

If you want to add number formatting and retain the original numeric data type, you should define the formatting in RDL. Open the properties window for the Textbox and use the Format tab.

-Chris

Export through MS SQL Server

I am new to the MS SQL Server, is there any command that exports a Table form Microsoft SQL Server 7 to a delemiterd text file?
Also, how could it be automated? i.e not form the GUI but through the schedule manager
ThanksYou can use bcp or DTS to export a delimited file. With bcp, you need to look up xp_cmdshell in Books OnLine (BOL), and build a bcp string to execute via a scheduled SQLServer Agent job. With DTS (visit www.SQLDTS.com (http://www.SQLDTS.com) for an opportunity to learn about DTS) you build the DTS package and then schedule the job to run via SQLServer Agent.|||Thank you for your help, I'll have a look...
Thanks again|||I am new to the MS SQL Server, is there any command that exports a Table form Microsoft SQL Server 7 to a delemiterd text file?
Also, how could it be automated? i.e not form the GUI but through the schedule manager

Thanks

If you are looking to export to a delemited text file, BCP can do the Job faster than DTS !! u could schedule it as well..|||You can schedule dts. I usually save dts as stuctured storage files and launch them though the scheduler. dtsrun.|||u r right Ebola!! u can schedule dts as well!! my choice was for performace when the export is to txt files... I prefer BCP for that...

Export reports to Excel (data only)

Hi,
Crystal Enterprise have an export option called "Export 97-2000 (data
only)" which exports a report to Excel format but just the data and
not the formatting. I'd like to get similar functionality with
reporting services.
I've tried using the "CSV" format which kinds of does a similar job
but it spreads everything out wide and both the header and table
groups gets mangled together.
What's the best way of accomplishing this in Reporting Services?
Are there any plans to have a different Excel export option that only
exports the data like Crystal?
TIA,
Magnus LassiWe don't have any plans for an Excel-specific data-only renderer at this
point (but that may change if enough people ask for it).
The CSV renderer should be able to give you what you're looking for after
you set the DataElementOutput property to suppress columns you don't want to
see.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Magnus" <mlassi@.yahoo.com> wrote in message
news:f85791fa.0407090735.11ce8969@.posting.google.com...
> Hi,
> Crystal Enterprise have an export option called "Export 97-2000 (data
> only)" which exports a report to Excel format but just the data and
> not the formatting. I'd like to get similar functionality with
> reporting services.
> I've tried using the "CSV" format which kinds of does a similar job
> but it spreads everything out wide and both the header and table
> groups gets mangled together.
> What's the best way of accomplishing this in Reporting Services?
> Are there any plans to have a different Excel export option that only
> exports the data like Crystal?
> TIA,
> Magnus Lassi