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

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 Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the MySQL table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If MYSQL can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:
> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the MySQL table's strucutre and it was also set to varbinary(8)
> Please help.
>

Export to PDF issue

Hi,
I have a strange problem with a report when it is exported. My report has a
textbox on it which dispays the value of one of the parameters selected by
the user. When the report is displayed in the browser everything is
positioned correctly on the screen. However, when I export the report the
textbox has moved left and it is now overwriting the label which is next to
it. This happens when exporting to PDF or TIFF.
Does anyone have any ideas as to why this should happen? I'm using RS 2005.Make sure the allignment and the justification of the text is right and
increase the width of the text box which displays the parameter selected.

Export to excel issue

I Have a report, in SQL Reporting servies. THE report has a field where the value shows up as an integer ie 0. However, when the report is exported to excel, the field value shows up as a decimal ie 0.4.

How do i keep the number to show as an integer only ie 0, when exported to excel.

Are you specifying a format code to only display the integer, or is the underlying value actually an integer? If it is the former, what is the format code you are using?

Ian|||Yes, I am specifying a format code of F0 on that textbox for that field.|||Tarana,

This seems like a bug; however, the good news is there is a workaround: try the following format code.

##0

Ian|||Thanks a bunch Ian- that worked very well.

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com

export to excel

Hi:
I have some percentage value in my report. I set these to 0 decimal point
in design layout.
It works fine when display on the web. But when I export them to excel,
these value is set to 1 decimal point.
Is there a way to round to no decimal point when exporting to excel?Ken,
Indeed the standard percentage format without decimals (P0) doesn't export
correctly to Excel. You can use #,##0% as a custom format.
Jan Pieter Posthuma
"ken" wrote:
> Hi:
> I have some percentage value in my report. I set these to 0 decimal point
> in design layout.
> It works fine when display on the web. But when I export them to excel,
> these value is set to 1 decimal point.
> Is there a way to round to no decimal point when exporting to excel?
>|||Thanks Jan, it's very helpful
"Jan Pieter Posthuma" wrote:
> Ken,
> Indeed the standard percentage format without decimals (P0) doesn't export
> correctly to Excel. You can use #,##0% as a custom format.
> Jan Pieter Posthuma
> "ken" wrote:
> > Hi:
> > I have some percentage value in my report. I set these to 0 decimal point
> > in design layout.
> > It works fine when display on the web. But when I export them to excel,
> > these value is set to 1 decimal point.
> > Is there a way to round to no decimal point when exporting to excel?
> >

Export the data as an XML file?

I have two tables in my database like this.

AppsTable with columns INSTANCE_NAME, VALUE.

SecondTable with columns UID,SID,XID

I need the xml file like below.

- -

where commonID is the Value of UID from the second table,

appInstance name is the instance_name from the appstable.

If i have values like this in second table:

SID UID XID

XAB ABC AB2

BAX 23D BCK

I need the xml file like this.

<appInstance name="Siebel">

<appID commonID="ABC">XAB</appID>

<appID commonID="23D">BAX</appID>

</appInstance>

- <appInstance name="sap">

<appID commonID="ABC">AB2appID>

<appID commonID="23D">BCK</appID>

</appInstance>

please help me.

-

w

From your explanations it is not clear to me what you are trying to do.

Can you provide a query that gives the following result:

Siebel ABC XAB 23D BAX

sap ABC AB2 23D BCK

?

Then formatting it as XML will be a FOR XML application.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hai Eugene,

Here i am giving the code what i have done for my requirement.

SELECT name,(SELECT * FROM TempTable_XREF

FOR XML AUTO,TYPE).query(' for $p in /TempTable_XREF

return

<APPID CommonID = "{data($p/@.COMMON_ID)}">

{data($p/@.UCM_UID)} --here the modification needed.

</APPID>

')

FROM AppsTable as APPINSTANCE

FOR XML AUTO

In the above query in place of @.ucm_uid i need one different value for each loop

In other words, I have 4 columns in my database table UCM_UID,S_ID, IX_ID,COMMON_ID

For the first time i need UCM_UID

For the second time i need S_ID

For the third time i need IX_ID

|||

What I didn’t understand was the relationship between the two tables you use. Without that I can’t interpret “the first time”, “the second time”, and “the third time”.

Do you really want to get a cross product of the two tables and format it as XML? Did you intend to add into your sub-query a correlation condition to the outer query?

As for the formatting part, did you mean something like

SELECT

name,

(SELECT * FROM TempTable_XREF FOR XML AUTO,TYPE)

.query('for $p in /TempTable_XREF

return

<APPID CommonID = "{data($p/@.COMMON_ID)}">

{data($p/@.UCM_UID)}

</APPID>

<APPID CommonID = "{data($p/@.COMMON_ID)}">

{data($p/@.S_ID)}

</APPID>

<APPID CommonID = "{data($p/@.COMMON_ID)}">

{data($p/@.IX_ID)}

</APPID>

')

FROM AppsTable as APPINSTANCE

FOR XML AUTO

?

Or the equivalent using purely FOR XML functionality:

SELECT

name,

(SELECT

COMMON_ID AS "APPID/@.CommonID",

UCM_UID AS "APPID/text()",

NULL AS dummy1,

COMMON_ID AS "APPID/@.CommonID",

S_ID AS "APPID/text()",

NULL AS dummy2,

COMMON_ID AS "APPID/@.CommonID",

IX_ID AS "APPID/text()"

FROM TempTable_XREF FOR XML PATH(''),TYPE)

FROM AppsTable FOR XML RAW('APPINSTANCE')

?

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Hai Eugene,

the following format i need my XML file.

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

- <listOfIDXRefData xmlns="http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon">

- <idXRef name="BUS UNIT">

- <APPINSTANCE name="Siebel_80">

<APPID CommonID="Cust_1-UCM-123001">1-SIEB-123001</APPID>

<APPID CommonID="Cust_1-UCM-123002">1-SIEB-123002</APPID>

<APPID CommonID="Cust_1-UCM-123003">1-SIEB-123003</APPID>

</APPINSTANCE>

- <APPINSTANCE name="Siebel_UCM">

<APPID CommonID="Cust_1-UCM-123001">1-UCM-123001</APPID>

<APPID CommonID="Cust_1-UCM-123002">1-UCM-123002</APPID>

<APPID CommonID="Cust_1-UCM-123003">1-UCM-123003</APPID>

</APPINSTANCE>

- <APPINSTANCE name="IXMAL_01">

<APPID CommonID="Cust_1-UCM-123001">1-IX-123001</APPID>

<APPID CommonID="Cust_1-UCM-123002">1-IX-123002</APPID>

<APPID CommonID="Cust_1-UCM-123003">1-IX-123003</APPID>

</APPINSTANCE>

</IdxRef>

</listOfIDXRefData>

<idXRef name="PAYMENT">

- <appInstance name="Siebel_80">

<appID commonID="Pay-1-3EY">1-SIEB_EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_3EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_Y</appID>

</appInstance>

- <appInstance name="Siebel_UCM">

<appID commonID="Pay-1-3EY">1-UCM_3EY</appID>

<<appID commonID="Pay-1-3EY">1-UCM_Y</appID>

<<appID commonID="Pay-1-3EY">1-UCM_EY</appID>

</appInstance>

- <appInstance name="IXMAL_01">

<appID commonID="Pay-1-3EY">1-IX3EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_Y</appID>

</appInstance>

- <appInstance name="SAP46C_01">

<appID commonID="Pay-1-3EY">0001</appID>

</appInstance>

- <appInstance name="SiebelSIA75_01">

<appID commonID="Pay-1-3EY">1-3EY</appID>

</appInstance>

- <appInstance name="SiebelSIA75_02">

<appID commonID="Pay-1-3EY">1-3EY</appID>

</appInstance>

</idXRef>

</listOfIDXRefData>

Common id should be appended text (like cust or pay) with ucm id.

|||

Hai Eugene,

the following format i need my XML file.

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

- <listOfIDXRefData xmlns="http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon">

- <idXRef name="BUS UNIT">

- <APPINSTANCE name="Siebel_80">

<APPID CommonID="Cust_1-UCM-123001">1-SIEB-123001</APPID>

<APPID CommonID="Cust_1-UCM-123002">1-SIEB-123002</APPID>

<APPID CommonID="Cust_1-UCM-123003">1-SIEB-123003</APPID>

</APPINSTANCE>

- <APPINSTANCE name="Siebel_UCM">

<APPID CommonID="Cust_1-UCM-123001">1-UCM-123001</APPID>

<APPID CommonID="Cust_1-UCM-123002">1-UCM-123002</APPID>

<APPID CommonID="Cust_1-UCM-123003">1-UCM-123003</APPID>

</APPINSTANCE>

- <APPINSTANCE name="IXMAL_01">

<APPID CommonID="Cust_1-UCM-123001">1-IX-123001</APPID>

<APPID CommonID="Cust_1-UCM-123002">1-IX-123002</APPID>

<APPID CommonID="Cust_1-UCM-123003">1-IX-123003</APPID>

</APPINSTANCE>

</IdxRef>

</listOfIDXRefData>

<idXRef name="PAYMENT">

- <appInstance name="Siebel_80">

<appID commonID="Pay-1-3EY">1-SIEB_EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_3EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_Y</appID>

</appInstance>

- <appInstance name="Siebel_UCM">

<appID commonID="Pay-1-3EY">1-UCM_3EY</appID>

<<appID commonID="Pay-1-3EY">1-UCM_Y</appID>

<<appID commonID="Pay-1-3EY">1-UCM_EY</appID>

</appInstance>

- <appInstance name="IXMAL_01">

<appID commonID="Pay-1-3EY">1-IX3EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_Y</appID>

</appInstance>

- <appInstance name="SAP46C_01">

<appID commonID="Pay-1-3EY">0001</appID>

</appInstance>

- <appInstance name="SiebelSIA75_01">

<appID commonID="Pay-1-3EY">1-3EY</appID>

</appInstance>

- <appInstance name="SiebelSIA75_02">

<appID commonID="Pay-1-3EY">1-3EY</appID>

</appInstance>

</idXRef>

</listOfIDXRefData>

Common id should be appended text (like cust or pay) with ucm id.

|||

Hai Eugene..

Here i am specifying the entire requirement.

I have two tables
one with appinstance name,appid.
secondwithucmid,siebelid,ixid.(there are many tables i have with these cols)

note:I have many tables with these 3 id columns.

In my xml file

The element <idxRef name=" CUSTOMER">
The customer here is my table name
Each tag of this type specifies different tables.

The element <appInstance name="siebel_80">
-this is the value from the first table.

<appID commonID="....">....</appid>
<appID commonID="....">....</appid>
<appID commonID="....">....</appid>

As i have 3 ids in my table , here the common id would be
some appended text with any id from those 3 values.

HOPE YOU UNDERSTOOD MY REQUIREMENT ,
IF CAN'T PLEASE REPLY.

|||

First you should write a SQL query that gives you the following result.

BUS UNIT Siebel_80 Cust_1-UCM-123001 1-SIEB-123001

BUS UNIT Siebel_80 Cust_1-UCM-123002 1-SIEB-123002

BUS UNIT Siebel_80 Cust_1-UCM-123003 1-SIEB-123003

BUS UNIT Siebel_UCM Cust_1-UCM-123001 1-UCM-123001

BUS UNIT Siebel_UCM Cust_1-UCM-123002 1-UCM-123002

BUS UNIT Siebel_UCM Cust_1-UCM-123003 1-UCM-123003

BUS UNIT IXMAL_01 Cust_1-UCM-123001 1-IX-123001

BUS UNIT IXMAL_01 Cust_1-UCM-123002 1-IX-123002

BUS UNIT IXMAL_01 Cust_1-UCM-123003 1-IX-123003

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_EY

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_3EY

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_Y

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_3EY

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_Y

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX3EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX_EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX_Y

PAYMENT SAP46C_01 Pay-1-3EY 0001

PAYMENT SiebelSIA75_01 Pay-1-3EY 1-3EY

PAYMENT SiebelSIA75_02 Pay-1-3EY 1-3EY

Then such query can be transformed into a FOR XML query that formats the result as XML in the desired shape. You’ll have to use new FOR XML features of SQL Server 2005. See my post above for examples and also look at FOR XML articles in BOL. Use WITH XMLNAMESPACES to add XML namespace into XML results. Let me know if you need any help with FOR XML query.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Fine Eugene, I got your point.

But i am not able to get my result in a single sql query

I have written the following queries to get the format what you have specified in the above reply.

SELECT AppsTable.name,TempTable_XREF.UCM_UID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='Siebel_UCM'

SELECT AppsTable.name,TempTable_XREF.SIEBEL_ID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='Siebel_80'

SELECT AppsTable.name,TempTable_XREF.IXMAL_ID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='IXMAL_01'

I have referred SQLSERVER DOC but i am not able to write the single query by using ' FOR XML ' clause. Please Reply how to get the same result in XML for the above queries using the FOR XML clause in one sql query.

Thanks,

|||

You could have used UNION ALL between the three queries ;-)

Here’s a draft FOR XML query for the three queries you provided using SQL Server 2005 sub-query FOR XML and PATH mode:

WITH XMLNAMESPACES (DEFAULT 'http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon')

SELECT

'BUS UNIT' AS "idXRef/@.name",

'Siebel_UCM' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.UCM_UID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='Siebel_UCM'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*",

NULL AS "idXRef/DummySeparator1",

'Siebel_80' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+ TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.SIEBEL_ID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='Siebel_80'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*",

NULL AS "idXRef/DummySeparator2",

'IXMAL_01' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+ TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.IXMAL_ID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='IXMAL_01'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*"

FOR XML PATH('listOfIDXRefData')

I can’t verify the syntax so the query may contain parse errors.

You’ll need to add your ordering if necessary.

Note that the result will be in UTF-16 encoding and there will be no XML declaration. If you need XML declaration you’ll have to add it separately.

If you need this kind of XML formatting in SQL Server 2000 environment you’ll need to use FOR XML EXPLICIT.

You may want to take a look at your data modeling side of story – I don’t see much sense in AppsTable in the FROM clause of your queries.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Thank u verymuch Eugene,

Atlast i got the solution from your side.

Eugene, After running the above query , it is showing the namespace for each and every tag ( eg: <appid xmlns="http://..........")

How can i resolve this problem. Except this everything is ok.

Thanks,

Rao.

|||

Rao,

Unfortunately, there’s no way to remove the extraneous XML namespace declarations in SQL Server 2005. They add verbosity but do not change XML content in most XML application data models. You could work it around with string concatenations but it would lead to highly unmaintainable code.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Got your point thanks Eugene.

Regards,

Rao.

|||

Hai Eugene,

To generate the XML file,

Currently we are working with sample database which contains less number of records, as the process going on we will have lakhs of records with in the database.

Will the same query be ok for that also? or it leads to any performance issues.