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 from SQL to an Outlook group or Dist List?

I need to export a list of name and email address fields from a SQL
table, in some format so that a user can take the file I generate and
Import it into Outlook and it will keep all the names together in a
Group or Distribution List rather than dumping them into the user's
main address book. Any ideas?

Francesco"Francesco" <francesco@.neurodesign.com> wrote in message
news:8b6199d0.0307310928.73359d1e@.posting.google.c om...
> I need to export a list of name and email address fields from a SQL
> table, in some format so that a user can take the file I generate and
> Import it into Outlook and it will keep all the names together in a
> Group or Distribution List rather than dumping them into the user's
> main address book. Any ideas?
> Francesco

Assuming you have SQL2000, you can use BCP or DTS to create a CSV file, or a
file in some other delimited format. As to how you import that into Outlook,
you will get a better response posting in an Outlook newsgroup - there are
several microsoft.public.outlook.* groups.

Simon

Exporting data with dynamic fields

Hi,

I have one Query Builder application where user creates queries and generates output with required fields.Then user can export the same to excel (Using farPointSpread properties)
But if no. of records goes beyond 10K it goes too slow or even user machine hangs up. I want to give proper export facility. Can it be given using DTS?

I tried DTS (.vbs) file. but for random field selection it did not work.DTS package asks for new Transactions to be built for every change in Source table structure. It first creates the table in the destination databse and then exports the data.
If I try modify the VB code generated by the package to match with new Source table it does'nt work.

Can anyone tell me what should I do for export the data dynamically i.e. export the tables with differrent structres?

Thanx in advance.|||You should do a helluvalotta programming. DTS sucks, and I don't believe most people on this forum use it for much more than directly transfering defined recordsets.

Exporting data to Flat File

I'm using SSIS package to export some data to a comma delimited CSV file. The problem is that some of the fields have commas in them. Is there a way to deal with this other to changing the delimiter?

It depends on your requirements. As I see it you have 2 options:

1) Change your delimiter

2) Change the commas in the data to something else.

-Jamie

|||

Use Text Qualifiers (for ex. double quotes ") when you export the data.
Each field will be enclosed within double quotes.

Thanks,
Loonysan

Export To PDF Loses VbCRLF Formatting

Hi

I've simplified this so I can test it's not me and so it's easier to explain.

I have one table in SQL server called Table1. Five fields called: One, Two, Three, Four, Five. Values of NULL, 2, 3, 4, NULL.

Reporting services, put a table in a report. Two fields. Header of Field1 and Field2.

Value field data for Field1 ="One: " & vbcrlf & "Two: " & vbcrlf & "Three: " & vbcrlf & "Four: " & vbcrlf & "Five: "

Value field data for Field2 =Fields!One.Value & vbcrlf & Fields!Two.Value & vbcrlf & Fields!Three.Value & vbcrlf & Fields!Four.Value & vbcrlf & Fields!Five.Value

Preview the report, all is well, you get this:

Field1 Field2

One:

Two: 2

Three: 3

Four: 4

Five:

Export it to a PDF, all is not well, I get this:

Field1 Field2

One: 2

Two: 3

Three: 4

Four:

Five:

I can make it work by checking the NULL fields and replacing them with a character. But why does it ignore the vbCRLF when exported to a PDF?

Any ideas...

give minimum space (width and hight) for that field and try|||

Hello merin

Not sure how I give it minimum space?

Also the field is being outputted with enough space (I think), as 234 appear in that field. If I replace the NULLS with a hypen, it works perfectly. But that NULL in the table in field one is causing the VbCRLF to be ignored when it is exporting to a PDF.

With a hypen as a replacement for the NULL I get this preview and it outputs perfectly to a PDF. (This is how I've fixed the problem, but I'm still interested as to why this happens)

Field 1 Field2

One: -

Two: 2

Three: 3

Four: 4

Five: -

|||

Unfortunately, this is not something we support very well at all in SQL Server 2005. You'll see inconsistent behavior - for instance, exporting to TIFF will act as if the tabs are not present so the words will run together and exporting to PDF will act as if the tabs are spaces except for underline which won't cover the complete text.

Your best bet will be to do this via code. Switch to a fixed-width font and simulate tabs with multiple spaces.

Sorry I don't have a better answer.

|||

Thanks for this. The NULL replace with a hypen works for me for now. Hopefully in the next SP it will be a little more consistant with the formatting, here's hoping anyway.

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - or char(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - orchar(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to Excel in MS Access

Hi,

I have one form in access application which shows various fields from
the database. The fields are like No of PCs a client has,No of
employees,revenue etc.

Need of the user is to filter the data on the form and export the
results.
I am using a Qury as the base for this form.Can I do it easily or i
need to write the code to do this.

Secondly can I provide a separate interface to allow user to Query and
extract the data.

Say, PC between <SomeRange> AND/ OR
Revenue Between <Some Range
whatever result comes will be exported to the Excel file.

Please tell me if you have any solution.

Thanks in Advance.

Rohit

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict241673.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=840002If you can utilize a query to create the data you want, then you can do
a simple trick of exporting the data within Access to Excel with very
little code.

DoCmd.OutputTo acOutputQuery, "Name Of Query", acFormatXLS

This can be assigned to a button and you have your results. This works
great if the query is based off the actual form.

Enjoy!

export table with text and ntext fields

hello,
i have to export more tables. This tables has all kind of fields
(date,text,ntext ...)
What is the best way to export this data so that the data could be later
imported on a other server ...(CSV? ....)
any ideas?
thanks
XavierDid you try using DTS?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:0CDC2879-14FF-4CA6-A947-CD4B5650F683@.microsoft.com...
> hello,
> i have to export more tables. This tables has all kind of fields
> (date,text,ntext ...)
> What is the best way to export this data so that the data could be later
> imported on a other server ...(CSV? ....)
> any ideas?
> thanks
> Xavier
>|||yes - but i could not use it - because the customer did not allow dts...
I have to use only T-Sql cmds ...which the customer eill run on his server
in the Q-Analyser , so my question is:
if i have a table 3 fieldtypes (date/text/ntext) - and i want to save the
values in a flatfile(csv?) how can i do this - and what delimiter should i
use? - to be sure that the content can be restored on the other side ...
thanks
"SriSamp" wrote:

> Did you try using DTS?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:0CDC2879-14FF-4CA6-A947-CD4B5650F683@.microsoft.com...
>
>|||You will then have to use BCP.
Check these sources to get started on the same:
http://www.databasejournal.com/feat...cle.php/3391761 and
http://www.sql-server-performance.com/bcp.asp
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:58F11DD1-840C-4F0C-BC69-C5EECA49B3D0@.microsoft.com...
> yes - but i could not use it - because the customer did not allow dts...
> I have to use only T-Sql cmds ...which the customer eill run on his
> server
> in the Q-Analyser , so my question is:
> if i have a table 3 fieldtypes (date/text/ntext) - and i want to save the
> values in a flatfile(csv?) how can i do this - and what delimiter should
> i
> use? - to be sure that the content can be restored on the other side ...
> thanks
>
>
>
> "SriSamp" wrote:
>|||Surely there is an out to text file in QA?
SriSamp wrote:
> You will then have to use BCP.
> Check these sources to get started on the same:
> http://www.databasejournal.com/feat...cle.php/3391761 and
> http://www.sql-server-performance.com/bcp.asp|||Yes, there is. But if you are exporiting (N)TEXT, am not sure of the length
of these in the tables that are mentioned, since QA limits the max
characters to around 8K.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Adrian Edwards" <abc@.abc.com> wrote in message
news:dlatvb$4rh$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Surely there is an out to text file in QA?
> SriSamp wrote:|||thanks for the suggestion,
it works well...
My only problem is what shell i do if the content of ntext is larger then 8
k
best regards
Xavier
"SriSamp" wrote:

> You will then have to use BCP.
> Check these sources to get started on the same:
> http://www.databasejournal.com/feat...cle.php/3391761 and
> http://www.sql-server-performance.com/bcp.asp
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:58F11DD1-840C-4F0C-BC69-C5EECA49B3D0@.microsoft.com...
>
>

Export table to dbf file

I need to export certain fields of a table from sql 2000 into a dbf
file, but can't find the proper query command to make it work. I also
want to set this up to run each night automatically.

Any help will be greatly appreciated.

RandyHi

You will probably need a scheduled DTS job, check out Books online for more
information about DTS and the following site is also very useful
http://www.sqldts.com/default.aspx

John

"Randy Henson" <rhenson@.cellxion.com> wrote in message
news:5dde95d2.0308220515.1232d9b5@.posting.google.c om...
> I need to export certain fields of a table from sql 2000 into a dbf
> file, but can't find the proper query command to make it work. I also
> want to set this up to run each night automatically.
> Any help will be greatly appreciated.
> Randy

Export report to excel - format items as numbers

Hi,

I have a trouble when exporting a local report to excel.

The fields are exported to excel as text despite they are numbers and i have explicitly formatted them as such:

=FormatNumber(Fields!someField.Value,0)

or

=FormatNumber(Fields!someField.Value,2)

Tried a second approach to use: "Format" property to "g" or "N" as specified here:

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

but this again does not result in "Number" format in excel but in a custom one. But I'm required to output these fields in number format, not custom one. So the second option does not work for me.

Tried a third approach to do some casts like CDbl, but again this result in custom format.

There should be some way to instruct the report to output these fields in number format.

Does anyone knows it?

Thanks,

Yani

HI,Yani:

What is the meaning of 'Custom '? From what kind item value you want to convert it into a "Number".

Would you please give us a sample for your scenario?

|||

Hi,

Thanks for your reply.

My report contains multiple columns of integer and decimal values, which are shown perfectly in the Report Viewer.

However, that report is mainly used when it's being exported to excel.

However, when the report is exported to excel the integer and decimal values don't remain numbers any more, but are rendered as text.

But I need them in Number format.

Doing, what I've written above results in a custom format - which means, if you open an excel doc and select a cell -> right click on it -> "Format Cells" -> On the first Number Tab, there are multiple categories. So I need my cells to be in category "Number" , instead of "Custom".

Thanks,

Yani

|||

HI, Yani:

Yes, i have gave it a try

1.When i set the 'Format' property of the column to C or N or CInt(Fields!someField.Value), it results to be the 'Custom Format' in the formatting property in the EXCEL

2.When i set FormatNumber(Fields!someField.Value,"N"), it results to be the 'General Format' in the formatting property in the EXCEL.

But i have not found any solution to make it render as the "Number Format" in the Excel. I will get inside into it in the following days and inform you whether i can get the answer ASAP.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi Rex Lin,

Thanks a lot for you investigation.

You have correctly understood my question.

I would be very grateful in case you manage to find an answer to it :)

Thanks in advance,

Yani

|||

Hi

I am facing the same problem like the integer/number data rendered into excel is considered as General/Custom format instead of Number.
I tried with Int() in reports and it is too rendered as General format...

Actually I also have some percentage values in cells of a report but those are considered as text when exported to excel. I hae to format them as Percentage only. Here i have used FormatPercent( ) in reports

If you have found any solution for the above issue then could you please share it with me too. Your quick reply will be very helpful as I have to fix this issue ASAP.

Thanks in advance
Sups123

|||

Hi Yani,

Try the below code

=FORMATNUMBER(Fields!LICENSE_FEE_AMOUNT.Value,2,True,True)

where LICENSE_FEE_AMOUNT is the name of the value that is being displayed.

2 is the number of decimal places.

Thanks

Subbu

|||

Hi

I think this can help you out..

Excel will interpret only some set of data types. If the field that is returned is some expression and the return type is not always specific data type then it will interpret as string. Let me explain this.

Consider you have expression like =iif(Fields!txIUnit.Value=1,fltQ1Actuals,'To Be decided'). In this example since the return type might be float or string, it will always interpret as string. that is why when downloaded in excel format it identifes it as text.

Try converting the expression into Decimal or Int or double, then it will be represented as text in the excel. It will be pure number.

I mean..Try as below

=CDbl(iif(Fields!txIUnit.Value=1,fltQ1Actuals,'To Be decided'))

Though the above code returns number if the condition is satisfied(txIUnit.Value=1), in other case it shows '#Error'

Please let me know if it makes sense what i have explained