Export Report to File: csv

Showing posts with label csv. Show all posts

Thursday, March 29, 2012

Exporting Report to Excel

I'm having a problem exporting a report to Excel. It only save the first 23
lines of the report. If I save it as .csv I get everything. Any ideas?What version are you on? If RS 2000 I suggesting installing SP2.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> I'm having a problem exporting a report to Excel. It only save the first
> 23
> lines of the report. If I save it as .csv I get everything. Any ideas?|||I am using RS 2000. I loaded the latest SP for RS's. I'm still having the
problem. It occurs on two different machines. I preview the report and click
Save and select Excel. It then only saves a few lines of the report or I get
an error "operation is not valid due to the current state of the object". I
have found a work around that works on both machines. After clicking Save,
when the window appears to select the the name to save as I click cancel. I
then go back and save it like you normally would and it works. The problem
seems to be a bug. It doesn't make sense, but it works. I hope this will help
others.
"Bruce L-C [MVP]" wrote:
> What version are you on? If RS 2000 I suggesting installing SP2.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> > I'm having a problem exporting a report to Excel. It only save the first
> > 23
> > lines of the report. If I save it as .csv I get everything. Any ideas?
>
>|||You say preview, does this mean you are doing this from development? Or is
this from the Report Manager? If it works from Report Manager and it is just
development then it sounds like you have a work around.
One thing, SP2 should be installed at both the server and update the Report
Designer on the development machines as well. Especially if you bypassed
SP1. SP1 definitely had to be installed in both places.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
>I am using RS 2000. I loaded the latest SP for RS's. I'm still having the
> problem. It occurs on two different machines. I preview the report and
> click
> Save and select Excel. It then only saves a few lines of the report or I
> get
> an error "operation is not valid due to the current state of the object".
> I
> have found a work around that works on both machines. After clicking Save,
> when the window appears to select the the name to save as I click cancel.
> I
> then go back and save it like you normally would and it works. The problem
> seems to be a bug. It doesn't make sense, but it works. I hope this will
> help
> others.
> "Bruce L-C [MVP]" wrote:
>> What version are you on? If RS 2000 I suggesting installing SP2.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
>> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
>> > I'm having a problem exporting a report to Excel. It only save the
>> > first
>> > 23
>> > lines of the report. If I save it as .csv I get everything. Any ideas?
>>|||I am working in development.
"Bruce L-C [MVP]" wrote:
> You say preview, does this mean you are doing this from development? Or is
> this from the Report Manager? If it works from Report Manager and it is just
> development then it sounds like you have a work around.
> One thing, SP2 should be installed at both the server and update the Report
> Designer on the development machines as well. Especially if you bypassed
> SP1. SP1 definitely had to be installed in both places.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
> >I am using RS 2000. I loaded the latest SP for RS's. I'm still having the
> > problem. It occurs on two different machines. I preview the report and
> > click
> > Save and select Excel. It then only saves a few lines of the report or I
> > get
> > an error "operation is not valid due to the current state of the object".
> > I
> > have found a work around that works on both machines. After clicking Save,
> > when the window appears to select the the name to save as I click cancel.
> > I
> > then go back and save it like you normally would and it works. The problem
> > seems to be a bug. It doesn't make sense, but it works. I hope this will
> > help
> > others.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What version are you on? If RS 2000 I suggesting installing SP2.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> >> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> >> > I'm having a problem exporting a report to Excel. It only save the
> >> > first
> >> > 23
> >> > lines of the report. If I save it as .csv I get everything. Any ideas?
> >>
> >>
> >>
>
>|||I have seen bugs in development but then are fine in production. Make sure
you have run SP2 with the report designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
news:A3FB195D-C9F7-44DD-B138-40E162194F94@.microsoft.com...
>I am working in development.
> "Bruce L-C [MVP]" wrote:
>> You say preview, does this mean you are doing this from development? Or
>> is
>> this from the Report Manager? If it works from Report Manager and it is
>> just
>> development then it sounds like you have a work around.
>> One thing, SP2 should be installed at both the server and update the
>> Report
>> Designer on the development machines as well. Especially if you bypassed
>> SP1. SP1 definitely had to be installed in both places.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
>> news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
>> >I am using RS 2000. I loaded the latest SP for RS's. I'm still having
>> >the
>> > problem. It occurs on two different machines. I preview the report and
>> > click
>> > Save and select Excel. It then only saves a few lines of the report or
>> > I
>> > get
>> > an error "operation is not valid due to the current state of the
>> > object".
>> > I
>> > have found a work around that works on both machines. After clicking
>> > Save,
>> > when the window appears to select the the name to save as I click
>> > cancel.
>> > I
>> > then go back and save it like you normally would and it works. The
>> > problem
>> > seems to be a bug. It doesn't make sense, but it works. I hope this
>> > will
>> > help
>> > others.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> What version are you on? If RS 2000 I suggesting installing SP2.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
>> >> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
>> >> > I'm having a problem exporting a report to Excel. It only save the
>> >> > first
>> >> > 23
>> >> > lines of the report. If I save it as .csv I get everything. Any
>> >> > ideas?
>> >>
>> >>
>> >>
>>|||Ok. Thanks.
"Bruce L-C [MVP]" wrote:
> I have seen bugs in development but then are fine in production. Make sure
> you have run SP2 with the report designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> news:A3FB195D-C9F7-44DD-B138-40E162194F94@.microsoft.com...
> >I am working in development.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You say preview, does this mean you are doing this from development? Or
> >> is
> >> this from the Report Manager? If it works from Report Manager and it is
> >> just
> >> development then it sounds like you have a work around.
> >>
> >> One thing, SP2 should be installed at both the server and update the
> >> Report
> >> Designer on the development machines as well. Especially if you bypassed
> >> SP1. SP1 definitely had to be installed in both places.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> >> news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
> >> >I am using RS 2000. I loaded the latest SP for RS's. I'm still having
> >> >the
> >> > problem. It occurs on two different machines. I preview the report and
> >> > click
> >> > Save and select Excel. It then only saves a few lines of the report or
> >> > I
> >> > get
> >> > an error "operation is not valid due to the current state of the
> >> > object".
> >> > I
> >> > have found a work around that works on both machines. After clicking
> >> > Save,
> >> > when the window appears to select the the name to save as I click
> >> > cancel.
> >> > I
> >> > then go back and save it like you normally would and it works. The
> >> > problem
> >> > seems to be a bug. It doesn't make sense, but it works. I hope this
> >> > will
> >> > help
> >> > others.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> What version are you on? If RS 2000 I suggesting installing SP2.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> >> >> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> >> >> > I'm having a problem exporting a report to Excel. It only save the
> >> >> > first
> >> >> > 23
> >> >> > lines of the report. If I save it as .csv I get everything. Any
> >> >> > ideas?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Exporting Report Builder Tabular Report to CSV

A simple tabular Report Builder report was written to feed another system that requires quoted CSV. I have two issues when exported:

1) I can't get control over the exported column names. Currently, they are exported as "FIELDNAME_Value".

When I try to change the column headings in the designer, it has no effect on the exported column names

When I create a New Field and specify my desired column name (e.g., COMPANY), the export appears as COMPANY_Value.

How do I control these names for the CSV export?

2) The spec calls for quoted text. In my export, only values with special characters are quoted.

Thanks in advance.

-DRB

1) The CSV renderer gets the column names from each TextBox's DataElementName property. This property is exposed though Report Designer in VS.NET, but not through Report Builder. If you want to control the CSV columns you'll need to open the report in Report Designer, set DataElementName, and then redeploy back to the server.

2) The CSV renderer only qualifies values when they values contain the field or record delimiter. There isn't a way to force quotes around every text value.

I hope this helps.

-Chris

|||

Chris:

Thank you for the lead on the CSV column header name. It didn't quite work. Here's what I did:

1) From Report Builder, Save to File.

2) Move RDL to server.

3) On server, launch Visual Studio and open a new Report Services project.

4) Add the RDL to the project. Set the DataElementName property on every element.

5) Choose File > Save [filename] As to save updated RDL.

6) Move updated RDL back to local client and Load from File in Report Builder.

7) Run and export the report.

In my experience, the exported column heading did not change. Here's a snippet from the RDL and from the CSV:

<TableCell>
<ReportItems>
<Textbox Name="SITEADDRESS_Value">
<DataElementOutput>Output</DataElementOutput>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>White</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<Language>en-US</Language>
</Style>
<CanGrow>true</CanGrow>
<DataElementName>BUSINESS STREET</DataElementName>
<Value>=Fields!BUSINESSSTREET.Value</Value>
</Textbox>
</ReportItems>

From the CSV header row:
FACILITYNAME_Value,SITEADDRESS_Value,CityNameSiteAddressCITYNAME_Value,STATE_Value,ZIP_Value,FAX_Value,PHONE_Value

As you can see, the column heading still took its name from the Name property (best I can tell).

Please advise.

-D. B.

|||

Report Builder would open report from server, not from disk. In Visual Studio, you are saving report to disk, not deploying it to the server. Try deploying it to the report server from VS.NET and checking if this your header comes out as you expected in CSV, and then opening it in Report Builder.

CSV renderer always takes the name for the column from <DataElementName>, if present.

|||

Thank you Dennis:

I may have a new related related challenge. When I attempt to publish and run the report from within VS as directed, I receive the following error:

An error occurred during local report processing. The definition of the report /{report name} is invalid. The DataElementName property for the Textbox SITEADDRESS_Value contains "BUSINESS STREET", which is not a CLS-compliant identifier.

I appreciate that the space is the source of the error... but (back to my original post), my customer requires a CSV file with column headers such as "BUSINESS STREET".

Any other ideas?

-D.R.B.

|||

Unfortunately, DataElementName has to be CLS-Complaint, so you can't have spaces in the column name. Best available alternative is "_" character ("BUSINESS_STREET").

Exporting report (excel vs CSV)

Exporting csv runs faster and doesn't have limitation of rows compared with
the exporting excel.
However, I have issues using CSV.
Exporting csv do not show all column ( in case I use "Hidden" function on
the report.)
Exporting csv do not display correctly the Header on the table ( in case I
use field value (for example "=Fields!customHeader1.Value") for Header on
the table)
Is there any ways to export the report as csv and display correctly even
though I use some functions?Hi Ken,
Here is a link that might help clear up some of your issues.
http://blogs.msdn.com/bimusings/archive/2007/02/07/reporting-services-why-ar
en-t-all-my-report-columns-exporting-to-csv-and-or-xml.aspx
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Ken" <klee@.jeromegroup.com>
> Subject: Exporting report (excel vs CSV)
> Date: Thu, 27 Dec 2007 16:07:35 -0600
> Exporting csv runs faster and doesn't have limitation of rows compared
with
> the exporting excel.
> However, I have issues using CSV.
> Exporting csv do not show all column ( in case I use "Hidden" function on
> the report.)
> Exporting csv do not display correctly the Header on the table ( in case
I
> use field value (for example "=Fields!customHeader1.Value") for Header on
> the table)
>
> Is there any ways to export the report as csv and display correctly even
> though I use some functions?
>
>

Sunday, March 25, 2012

Exporting Data/1st three lines NOT CSV..

I have a specific format that I need to export data to. The first three lines of the document MUST be in the form of:

ascii
,
klg, Eastern Daylight Time,1,1
PineGrove,0,2005/10/01,00:00,1,1.75,192
PineGrove,0,2005/10/01,00:05,1,1.75,192
Pinegrove,0,2005/10/01,00:10,1,1.75,192

If I set this up in DTS and do an export, it puts commas after ascii - which I cannot have.

I've also tried using two data sources and exporting twice (hoping to append), however, one just overwrites the other.

Anyone have any ideas?? :o

Thanks in advance,
KristaIs this from a table?

Read the sticky at the top|||A couple of ideas...

You can create two text files, one with the headers. Then use the dos copy command to make one file.

copy file1+file2 file3

Another option is to create a temporary staging table. It can have one large varchar column that contains the data. Then export from this table. This option requires a little work.

Bill|||A couple of ideas...

You can create two text files, one with the headers. Then use the dos copy command to make one file.

copy file1+file2 file3 [B][I]

Bill

YOU'RE AWESOME!! I WAS TRYING TO REMEMBER HOW TO DO THIS EARLIER TODAY!! THANKS SO MUCH!!!!!!! :D|||You're welcome.

Check your PM.|||What's PM?|||Private Messagesql

EXporting data to xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,just adding for xml clause gives output in xml format
select * from <table> for xml. However this has lot of options too.
BOL has this example
CREATE VIEW p AS
SELECT od.OrderID,
pr.ProductName,
od.Quantity,
od.UnitPrice,
od.Quantity * od.UnitPrice AS total
FROM Products AS pr
JOIN
[Order Details] AS od
ON
pr.ProductID = od.ProductID
And then write the SELECT statement:
SELECT c.CompanyName,
o.OrderID,
o.OrderDate,
p.ProductName,
p.Quantity,
p.UnitPrice,
p.total
FROM Customers AS c
JOIN
Orders AS o
ON
c.CustomerID = o.CustomerID
JOIN
p
ON
o.OrderID = p.OrderID
FOR XML AUTO
--
Regards
R.D
--Knowledge gets doubled when shared
"Enric" wrote:

> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it customar
y,
> a xml.
> Any advice or though woud be greatly.
> Regards,|||thanks a lot,
"R.D" wrote:
> just adding for xml clause gives output in xml format
> select * from <table> for xml. However this has lot of options too.
> BOL has this example
> CREATE VIEW p AS
> SELECT od.OrderID,
> pr.ProductName,
> od.Quantity,
> od.UnitPrice,
> od.Quantity * od.UnitPrice AS total
> FROM Products AS pr
> JOIN
> [Order Details] AS od
> ON
> pr.ProductID = od.ProductID
> And then write the SELECT statement:
> SELECT c.CompanyName,
> o.OrderID,
> o.OrderDate,
> p.ProductName,
> p.Quantity,
> p.UnitPrice,
> p.total
> FROM Customers AS c
> JOIN
> Orders AS o
> ON
> c.CustomerID = o.CustomerID
> JOIN
> p
> ON
> o.OrderID = p.OrderID
> FOR XML AUTO
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Enric" wrote:
>|||Enric,
Also undestand that the XML that is produced is an XML fragment not an valid
XML document. You'll need to add a XML tag and a root element.
HTH
Jerry
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:5FC7B338-98E4-4C5A-A62E-A81E661DD1AF@.microsoft.com...
> just adding for xml clause gives output in xml format
> select * from <table> for xml. However this has lot of options too.
> BOL has this example
> CREATE VIEW p AS
> SELECT od.OrderID,
> pr.ProductName,
> od.Quantity,
> od.UnitPrice,
> od.Quantity * od.UnitPrice AS total
> FROM Products AS pr
> JOIN
> [Order Details] AS od
> ON
> pr.ProductID = od.ProductID
> And then write the SELECT statement:
> SELECT c.CompanyName,
> o.OrderID,
> o.OrderDate,
> p.ProductName,
> p.Quantity,
> p.UnitPrice,
> p.total
> FROM Customers AS c
> JOIN
> Orders AS o
> ON
> c.CustomerID = o.CustomerID
> JOIN
> p
> ON
> o.OrderID = p.OrderID
> FOR XML AUTO
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Enric" wrote:
>

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

Exporting data to Excel files

Hi everybody, i'm new to SSIS, so it's possible that mine is a very stupid question

I have to develop a simple ETL package that reads data from a csv file and writes them to an xls file; the problem is that when the number of rows exceeds the maximum number of rows allowed for an xls file i get an error.

There is a way to solve this problem? for example adding a new sheet or creating a new file?

Thanks in advance

You can do this, but you'll have to do a little extra work. Basically, you need to preprocess your CSV to get the number of rows that will fit in a sheet, write them, then process the next set.

These posts might help:

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||I don't see any other solution than create multiple sheets/files. That is an Excel limititaion, nothing that SSIS can do about it.|||seems like it may works, thanks!

Exporting Data from SDF Database to CSV file

Hi guys,

Would any of you be able to provide some guide on how am I going to export the selected data (multiple rows and columns) into Excel or CSV file?

Or at least export into Text file, which I can later on save the file name as .CSV, so it become a CSV file after saving.

Thanks.

Regards,

Jenson

In which context is the data "selected". You can always enumerate your data and use the System.IO namespace to create the CSV file in code?|||

Hi Erik,

Yes Erik, that's what I intend to do, do you have any guide for me to follow, or any sample codes to refer to? I need to refer to them and write my own one, as I don't really think what I wanted to do is the same, I just need to structure and check how they do it, and what are the various ways to achieve the same thing.

Thanks.

Regards,

Jenson

|||

Hope this sample is what you are looking for:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlServerCe;

namespace ExportSDF

{

class Program

{

static void Main(string[] args)

{

SqlCeConnection conn = null;

SqlCeCommand cmd = null;

SqlCeDataReader rdr = null;

try

{

// Based on this sample: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.aspx

// Open the connection and create a SQL command

conn = new SqlCeConnection(@."Data Source = C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf;max database size=256");

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM Customers", conn);

rdr = cmd.ExecuteReader();

System.IO.TextWriter stm = new System.IO.StreamWriter(new System.IO.FileStream(@."C:\customers.csv", System.IO.FileMode.Create), Encoding.Default);

// Iterate through the results

while (rdr.Read())

{

// Write all fields except the last one...

for (int i = 0; i < rdr.FieldCount-2; i++)

{

if (rdr[i] != null)

{

stm.Write(rdr[i].ToString());

stm.Write(";");

}

else

{

stm.Write(";");

}

if (rdr[rdr.FieldCount-1] != null)

{

stm.Write(rdr[0].ToString());

}

stm.Write(System.Environment.NewLine);

}

// Always dispose data readers and commands as soon as practicable

stm.Close();

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

conn.Close();

}

Happy coding!

|||Hi Erik,

Thanks for the great reply and sorry for the late reply. The reason behind is I have finished the workaround for this requirements and it's working just fine. By looking at your code, I find that this code is even easier to understand!

I will try it out later as I'm currently busy with other stuff. Thanks for the great codes, Erik!

Have marked your answer as answer =)

Thursday, March 22, 2012

Exporting Data from Database to a csv file

Hi,

How do I export data from my database table into a Comma separated value file format.

I am using SQL Server 2005 with vb.net

Thanks

you can try bcp.exe.|||It would use the SQL Server Import / Export wizard , it is more comfortable than the BCP command, although in some cases like automatic commandline export thats the only choice. Right click the database and choose the Export... command. You will be guided though a wizard for exporting.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI baroo,

Now I have the same task but I can't find out how to do it...I also have to transfer csv to SQL,although I did it but I don't know how to export data from SQL to a csv file...

Did you solve it?If so coúld you also explain me how to do it?

Thanks,

Can

|||Try bcp.exe or import/export wizard, as mentioned above.|||

Hi Greg,

Thank you for the reply,I am writing a program in VB Express (SQL server express 2005) and I have to do it through coding...I tried data reader writer.writeline but it didn't work out...

Do you have an idea how can I do it using ADO or BCP but through coding....

Thanks&regards,

Can

|||

Reference SQL 2005 Books Online topic "Overview of Bulk Import and Bulk Export" for more information.

Exporting Data from Database to a csv file

Hi,

How do I export data from my database table into a Comma separated value file format.

I am using SQL Server 2005 with vb.net

Thanks

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI baroo,

Now I have the same task but I can't find out how to do it...I also have to transfer csv to SQL,although I did it but I don't know how to export data from SQL to a csv file...

Did you solve it?If so coúld you also explain me how to do it?

Thanks,

Can

|||Try bcp.exe or import/export wizard, as mentioned above.|||

Hi Greg,

Thank you for the reply,I am writing a program in VB Express (SQL server express 2005) and I have to do it through coding...I tried data reader writer.writeline but it didn't work out...

Do you have an idea how can I do it using ADO or BCP but through coding....

Thanks&regards,

Can

|||

Reference SQL 2005 Books Online topic "Overview of Bulk Import and Bulk Export" for more information.

Exporting CSV Stream to a file that is ANSI not UNICODE

a CSV unicode file does not look good in Microsoft Excel (while maintaining
it's delimiters). I want to keep the file as a real CSV, but have it open in
Excel. If I save a unicode CSV as ANSI (notepad/wordpad) then it still looks
OK to other programs, and looks good in Excel.
I'm doing a
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
type method to write the file... how do I get it ANSI?I should say that I have already tried "&rc:Encoding=UTF-8" but maybe it is a
report design issue too... all values come into the A cell|||Got it.
It was a Device Info setting not a render format:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp|||Just a note, in RS 2005 (out in November) there will be a sitewide setting
for the export to export in ASCII instead of Unicode.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Bienstock" <davidleebspam-sqlrs@.yahoo.com> wrote in message
news:B132C76A-597D-4733-8166-7C6CAD87D06D@.microsoft.com...
> Got it.
> It was a Device Info setting not a render format:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
>

Wednesday, March 21, 2012

Exporting a CrystalReport report to file..

Hi ppl,

I'm trying to export a report to a file (.csv) but without mucht succes. It seems i can use ExportTo from PEPLUS.{h/ccp}, but i guess i also need 'UXDDiskOptions' and 'UXFPaginatedTextOptions' which are not defined in PEPLUS.
There is a comment in PEPLUS.h which says the following:
--------------------------
At this time, the export destination structures
// (e.g. UXDDiskOptions) and the export format option structures
// (e.g. UXFCharSepOptions) have NOT been included in the class
// library. You will need to include the appropriate header files
// for these structures.
------------------------

Where do i find these headerfiles? and am i on the correct track? Of course i've used google, but there is stunningly little info to find.

Thanks in advance for any hints/pointers/examples/whatever :)[ moved thread ]

Friday, March 9, 2012

Export to flat file

We have a need to export a couple of reports to a flat file (not csv). I am thinking that the easiest way to do this is to write a custom extension. Should I do it this way and if so, can somebody point me to some resources or is there an easier way to do this?

Thanks for the information.

You need to write your own rendering extension for any output format not supported out of the box.

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

|||Yeah, I was afraid that you would say that.

Export to flat file

Thanks for the information.

You need to write your own rendering extension for any output format not supported out of the box.

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

|||Yeah, I was afraid that you would say that.

Wednesday, March 7, 2012

Export To Excel, CSV, XML with expression in Hidden property omits data

I have a matrix table with a rectangle in the data cell. The rectangle has an image and textbox. The textbox has an expression in it's Hidden property based on the column name. The report renders fine on screen. When the report is exported to Excel, CSV, XML the textbox contents are not output (the images display as expected). I've tried setting the DataElementOutput to Output/Yes with no success. Exporting to TIFF, PDF, Web Archive/MTHML is fine.

Here is a sample RDL which exhibits the issue:

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>Gemini50DataSource</DataSourceReference>

</DataSource>

</DataSources>

<DataType>String</DataType>

</Values>

</DefaultValue>

<Prompt>Image Path</Prompt>

</ReportParameter>

</ReportParameters>

<Body>

</MatrixColumn>

</MatrixColumns>

<rdefaultName>PicIndex</rdefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!PicIndex.Value</Value>

</Textbox>

</ReportItems>

<GroupExpression>=Fields!PicIndex.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<rdefaultName>ColumnName</rdefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightBlue</BackgroundColor>

</Style>

<Value>=Fields!ColumnName.Value</Value>

</Textbox>

</ReportItems>

<GroupExpression>=Fields!ColumnName.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet2</DataSetName>

<rdefaultName>textbox1</rdefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</Corner>

<Sizing>AutoSize</Sizing>

<Hidden>=IIF(First(Fields!ColumnName.Value = "image"), Len(Fields!CellValue.Value)=0, true)</Hidden>

</Visibility>

<Source>External</Source>

<Value>="file:" + Parameters!ImagePath.Value + Fields!CellValue.Value</Value>

</Image>

<DataElementOutput>Output</DataElementOutput>

<rdefaultName>textbox2</rdefaultName>

<Hidden>=IIF(Fields!ColumnName.Value <> "image", False, True)</Hidden>

</Visibility>

<Style>

<BackgroundColor>PeachPuff</BackgroundColor>

</Style>

<Value>=Fields!CellValue.Value</Value>

</Textbox>

</ReportItems>

<Hidden>=IIF(True, False, True)</Hidden>

</Visibility>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightGrey</BackgroundColor>

</Style>

</Rectangle>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

</Body>

<rd:ReportID>a944d20c-558a-4805-9d4c-aecc9757f678</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>SELECT 1 as PicIndex, 'image' as ColumnName, 'image1.jpg' as CellValue

union

SELECT 2,'image','image2.jpg'

union

SELECT 3,'image','image3.jpg'

union

SELECT 4,'image',null

union

SELECT 5,'something else',null

union

SELECT 6,'another column', 'display my text!'</CommandText>

<DataSourceName>Gemini50DataSource</DataSourceName>

</Query>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>PicIndex</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ColumnName</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CellValue</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Here is a posting related to this that may help.

http://blogs.msdn.com/bimusings/archive/2007/02/07/reporting-services-why-aren-t-all-my-report-columns-exporting-to-csv-and-or-xml.aspx

cheers,

Andrew

|||

Andrew,

Thanks for the posting. I've seen that blog. Unfortunately, the "good news" wasn't so good for this case. The setting of DataElementOutput(yes, no, auto) has no effect on the output for the situation described . If you find any other possibilities I would very much appreciate it.

Thanks,

Brian

Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
Thanks
You might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:

> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks
|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.

>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,[vbcol=seagreen]
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
containing[vbcol=seagreen]
that
>.
>
|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
> currency as a string,
> Server doesn't have
> you would embed to
> CSV, there's
> Excel commands you
> $a601280a@.phx.gbl,
> containing
> that
|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A

Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
ThanksYou might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:
> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.
>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>> I am going to export a numeric(14,2) field that
containing
>> the Balance to a CSV file. It will be opened by the
>> Finance Staff with Excel.
>> They would like to get the Balance Field opened as
>> Currency Field (With $ sign). Is it possible for us to
>> convert the Balance Field or do some manuipulation so
that
>> it means her need ?
>> Thanks
>.
>|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
>>--Original Message--
>>You might try just prefixing it with a $ e.g. Instead of
>>SELECT numeric_column
>>Use
>>SELECT '$' + RTRIM(numeric_column)
>>Not sure if Excel will automatically recognize it as
> currency as a string,
>>but that's probably as close as you're going to get--SQL
> Server doesn't have
>>any clue what Excel is, never mind what Excel commands
> you would embed to
>>force the confusion. Besides, since you're exporting to
> CSV, there's
>>nothing you can embed anyway... It won't process any
> Excel commands you
>>include in a plain text CSV file...
>>
>>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
> $a601280a@.phx.gbl,
>>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>> I am going to export a numeric(14,2) field that
> containing
>> the Balance to a CSV file. It will be opened by the
>> Finance Staff with Excel.
>> They would like to get the Balance Field opened as
>> Currency Field (With $ sign). Is it possible for us to
>> convert the Balance Field or do some manuipulation so
> that
>> it means her need ?
>> Thanks
>>.|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A

Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
ThanksYou might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:

> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.

>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>
containing[vbcol=seagreen]
that[vbcol=seagreen]
>.
>|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
> currency as a string,
> Server doesn't have
> you would embed to
> CSV, there's
> Excel commands you
> $a601280a@.phx.gbl,
> containing
> that|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A

Friday, February 24, 2012

export to Excel

is there any way to export the result of the SQL statement to Excel or CSV?

Jassim:

Can you use SSIS (DTS in SQL Server 2000) provides an interface that handles this. Will this work for you? There are a number of ways to do this; it's just a matter of which way is best. BCP also can do this. You can also manually wire the queries so that proper punctuation is added.

Dave

|||

ya..dave has given all the options..

but if u just want to run a query and get the result in excel..executing it in management studio/query analyzer , set the query output to file .. from the menu..query->result to ->file , then run it , u can save it in any format.... .rpt is the default...

|||

How about export to Excel with password using TSQL?

Export to CSV/Excel

How can I export data from a table to a CSV file? I am working with an
Account package with multiple companies. Each company has its own database
and all the tables are named the same thing. I want to export all the data i
n
a table called Account for each company to a CSV file. There are about 20
companies and I do not want to manually do this using DTS. Is there a way I
could write a script to do this?
Thanks
EmmaIf you do not want to use DTS, you can use BCP. It is a command line
directive that allows you to export to a file directly from a table, view or
a query.
Rather than me explaining more about it, you can check look the BCP usage in
SQL Server Books Online.
Let me know if it helps.
"Emma" wrote:

> How can I export data from a table to a CSV file? I am working with an
> Account package with multiple companies. Each company has its own database
> and all the tables are named the same thing. I want to export all the data
in
> a table called Account for each company to a CSV file. There are about 20
> companies and I do not want to manually do this using DTS. Is there a way
I
> could write a script to do this?
> Thanks
> Emma
>|||Huge ask for a simple manual task.
one question, hope its a one time request. If so the time scripting can get
you around 100 tables to CSV using DTS.
Again in my mind i was going wild like, using osql put it in batchfile and
input account name to script it to csv. Again for such things you need to
provide accountname hardcoded.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Emma" wrote:

> How can I export data from a table to a CSV file? I am working with an
> Account package with multiple companies. Each company has its own database
> and all the tables are named the same thing. I want to export all the data
in
> a table called Account for each company to a CSV file. There are about 20
> companies and I do not want to manually do this using DTS. Is there a way
I
> could write a script to do this?
> Thanks
> Emma
>|||Thanks Edgardo. BCP did the job.
Emma
"Edgardo Valdez, MCSD, MCDBA" wrote:
> If you do not want to use DTS, you can use BCP. It is a command line
> directive that allows you to export to a file directly from a table, view
or
> a query.
> Rather than me explaining more about it, you can check look the BCP usage
in
> SQL Server Books Online.
> Let me know if it helps.
> "Emma" wrote:
>

Thursday, March 29, 2012

Exporting Report to Excel

Exporting Report Builder Tabular Report to CSV

Exporting report (excel vs CSV)

Sunday, March 25, 2012

Exporting Data/1st three lines NOT CSV..

EXporting data to xml file

Exporting data to Flat File

Exporting data to Excel files

Exporting Data from SDF Database to CSV file

Thursday, March 22, 2012

Exporting Data from Database to a csv file

Exporting Data from Database to a csv file

Exporting CSV Stream to a file that is ANSI not UNICODE

Wednesday, March 21, 2012

Exporting a CrystalReport report to file..

Friday, March 9, 2012

Export to flat file

Export to flat file

Wednesday, March 7, 2012

Export To Excel, CSV, XML with expression in Hidden property omits data

Export to Excel Spreadsheet

Export to Excel Spreadsheet

Export to Excel Spreadsheet

Friday, February 24, 2012

export to Excel

Export to CSV/Excel

Export Report to File

Blog Archive

About Me