export to text file problem

Hi,
I recognise that long varchars are cut when exported to the text file.
This is the scenario:
Define varchar(6000) in the test table. Put in one line with , for example, 600 chars.
Run import data to the text file. The length of the string in the text file is only 232 chars.
Any idea?
Thanks,
Yana
Hi Yana,
I tried creating the testtable and exporting the data to the text file.
Surprisingly it worked correctly.
I would be glad if you can furnish some more information, so that I will be
able to work on it more appropriately.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

export to text file problem

Hi,
I recognise that long varchars are cut when exported to the text file.
This is the scenario:
Define varchar(6000) in the test table. Put in one line with , for example,
600 chars.
Run import data to the text file. The length of the string in the text file
is only 232 chars.
Any idea?
Thanks,
YanaHi Yana,
I tried creating the testtable and exporting the data to the text file.
Surprisingly it worked correctly.
I would be glad if you can furnish some more information, so that I will be
able to work on it more appropriately.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

EXPORT TO PDF - double lines won't export

Has anyone seen this before:
if you set the borderstyle for any cell to double, when you export to
PDF, it appears as a single line. Is there a work-around for this,
aside from a adding another row? These are pretty complicated reports,
and I don't want the added overhead of managing the visibility of a row
I added just to get a double line.
This should be simple. Any ideas?what a great group. Answers for everyone. Boy, Microsoft really has
this all under control. Just like the Bush administration, everyone is
so busy counting their money that there's no time for the little guy...

Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.

Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.

export to excel

Hi,

In my report, I have 3 rows in the details section. I need to show all the 3 rows into a single line when it is exported to excel. Is this possible to do in reports, please help me.

I don't understand what you're trying to accomplish; if you can explain in more detail maybe I can give better guidance.

Each row in RDL will be exported to at least one row in Excel (it may span rows depending on the content of the cells and the position of other report items).|||

Hi,

I want to export every 3 lines in my report into 1 line in an excel sheet. Is there any way to do that.

|||Sorry, no.

Export to CSV without header line

Hi,
Is it possible to export to CSV without header line?
I create report for my user where they can retrieve data in csv(txt) file
for their further use.
I would like to skip generation of header line in CVS rendering if it is
possible.
Thanks
DonThanks for the tip.
But where do you set the device info?
Sissel
"Gastón Pírez" wrote:
> You can do this setting a parameter in the device info
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> Gaston.-
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:ECC0D669-97A0-49D4-9914-50ABCD4C3223@.microsoft.com...
> > Hi,
> >
> > Is it possible to export to CSV without header line?
> >
> > I create report for my user where they can retrieve data in csv(txt) file
> > for their further use.
> > I would like to skip generation of header line in CVS rendering if it is
> > possible.
> >
> > Thanks
> > Don
> >
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"Don" wrote:
> Hi,
> Is it possible to export to CSV without header line?
> I create report for my user where they can retrieve data in csv(txt) file
> for their further use.
> I would like to skip generation of header line in CVS rendering if it is
> possible.
> Thanks
> Don
>|||in sql server 2000 reporting services, can i export csv without header line?
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> Try www.sqlscripter.com to export data to text/csv.
> It's free.
>
> "Don" wrote:
>> Hi,
>> Is it possible to export to CSV without header line?
>> I create report for my user where they can retrieve data in csv(txt) file
>> for their further use.
>> I would like to skip generation of header line in CVS rendering if it is
>> possible.
>> Thanks
>> Don|||Good question. I'd like to know how to export csv without header lines in
RS2005. AND can someone tell me how to customer csv render in a data-drive
subscription? Can we do that automatically so that users who retrieve the
report from a designed folder will be able to open the csv file automatically
w/o headers? thanks
"Peter Fuller" wrote:
> in sql server 2000 reporting services, can i export csv without header line?
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> > Try www.sqlscripter.com to export data to text/csv.
> > It's free.
> >
> >
> > "Don" wrote:
> >
> >> Hi,
> >>
> >> Is it possible to export to CSV without header line?
> >>
> >> I create report for my user where they can retrieve data in csv(txt) file
> >> for their further use.
> >> I would like to skip generation of header line in CVS rendering if it is
> >> possible.
> >>
> >> Thanks
> >> Don
> >>
>
>

export to csv file places all detail line on 1 line of CSV?

trying to write a report that when exported (csv) each detail line in my
report would be a seperate line in the csv file currently the csv places all
detail line on the same line in the csv and delimits the row by comma. is
there a way in the csv rendering to allow the RecordDelimiter to be a
<cr><lf>?thanks you are correct but maybe I'm not being clear on my end the columns
are not the issue. I have 3 detail line in the report each line has 3 columns
report table
detail line1
detail line2
detail line3
but when i export to csv i get
(record1) line1,line2,line3
next(record) line1,line2,line3
etc...
what i'm looking for is
(record1)
line 1
line 2
line 3
(nextrecord)
line1
line2
line3
make any more sense?
thanks in advance for all the help
"Bruce L-C [MVP]" wrote:
> I assume you want Excel to open this up. RS defaults to unicode which Excel
> does not recognize. If RS 2005 you can configure RS to export it as Ascii
> which Excel will then properly split into cells.Even if what you want is a
> file for some other reason, I have RS configured for ASCII csv and my rows
> are NOT separated by a comma. So I think regardless of what you want to do
> follow the below and see if it resolves it for you:
> You only need to change in one place, rsreportserver.config. Reboot after
> the change. The below shows commenting out the existing entry and putting in
> the needed change to have CSV export as ASCII.
> .. <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
> news:CFE91818-CC7B-4297-8802-0C15BBB52998@.microsoft.com...
> > trying to write a report that when exported (csv) each detail line in my
> > report would be a seperate line in the csv file currently the csv places
> > all
> > detail line on the same line in the csv and delimits the row by comma. is
> > there a way in the csv rendering to allow the RecordDelimiter to be a
> > <cr><lf>?
> >
>
>|||I assume you want Excel to open this up. RS defaults to unicode which Excel
does not recognize. If RS 2005 you can configure RS to export it as Ascii
which Excel will then properly split into cells.Even if what you want is a
file for some other reason, I have RS configured for ASCII csv and my rows
are NOT separated by a comma. So I think regardless of what you want to do
follow the below and see if it resolves it for you:
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII.
. <!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
news:CFE91818-CC7B-4297-8802-0C15BBB52998@.microsoft.com...
> trying to write a report that when exported (csv) each detail line in my
> report would be a seperate line in the csv file currently the csv places
> all
> detail line on the same line in the csv and delimits the row by comma. is
> there a way in the csv rendering to allow the RecordDelimiter to be a
> <cr><lf>?
>|||Ahh, multiple detail lines. No, they are all considered one line of data for
export. I am not aware of a way to do what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
news:7DA88C6E-4683-483D-9554-E576566EED48@.microsoft.com...
> thanks you are correct but maybe I'm not being clear on my end the columns
> are not the issue. I have 3 detail line in the report each line has 3
> columns
> report table
> detail line1
> detail line2
> detail line3
> but when i export to csv i get
> (record1) line1,line2,line3
> next(record) line1,line2,line3
> etc...
> what i'm looking for is
> (record1)
> line 1
> line 2
> line 3
> (nextrecord)
> line1
> line2
> line3
>
> make any more sense?
> thanks in advance for all the help
>
> "Bruce L-C [MVP]" wrote:
>> I assume you want Excel to open this up. RS defaults to unicode which
>> Excel
>> does not recognize. If RS 2005 you can configure RS to export it as Ascii
>> which Excel will then properly split into cells.Even if what you want is
>> a
>> file for some other reason, I have RS configured for ASCII csv and my
>> rows
>> are NOT separated by a comma. So I think regardless of what you want to
>> do
>> follow the below and see if it resolves it for you:
>> You only need to change in one place, rsreportserver.config. Reboot after
>> the change. The below shows commenting out the existing entry and putting
>> in
>> the needed change to have CSV export as ASCII.
>> .. <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "gackinclose" <gackinclose@.discussions.microsoft.com> wrote in message
>> news:CFE91818-CC7B-4297-8802-0C15BBB52998@.microsoft.com...
>> > trying to write a report that when exported (csv) each detail line in
>> > my
>> > report would be a seperate line in the csv file currently the csv
>> > places
>> > all
>> > detail line on the same line in the csv and delimits the row by comma.
>> > is
>> > there a way in the csv rendering to allow the RecordDelimiter to be a
>> > <cr><lf>?
>> >
>>

export the table to csv by command line

Hello:
Is it possible to export the table by command line. I
have try to use isql but the format is not like CSV.
Thanks a lot!isql.exe is the old DB-Lib version and does not support SQL Server 2000
features, use osql.exe.
In fact, what you probably want is bcp.exe. Something like:
bcp mydb..mytable out c:\mytable.csv -c -t, -T
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> Hello:
> Is it possible to export the table by command line. I
> have try to use isql but the format is not like CSV.
> Thanks a lot!

export the table to csv by command line

Hello:
Is it possible to export the table by command line. I
have try to use isql but the format is not like CSV.
Thanks a lot!
isql.exe is the old DB-Lib version and does not support SQL Server 2000
features, use osql.exe.
In fact, what you probably want is bcp.exe. Something like:
bcp mydb..mytable out c:\mytable.csv -c -t, -T
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> Hello:
> Is it possible to export the table by command line. I
> have try to use isql but the format is not like CSV.
> Thanks a lot!