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

ExportData

Hi All ...
I am looking for simple T-SQL syntax for using ExportData method with
Bulkcopy object to export one table in tab delimited, text file format to
C:\TableName.txt. First row of text file should be column headers.
Anybody know offhand?
[TableName].ExportData ?
Thanks for your help ...When you refer to the BulkCopy object and ExportData method, you must be
speaking of Data Management Objects (DMO), which isn't easily called from
T-SQL. The best and most prevalent method of exporting data to a text file
would be to create a DTS package.
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:FD5558FE-CB29-44A6-ADA8-24DD306527CE@.microsoft.com...
> Hi All ...
> I am looking for simple T-SQL syntax for using ExportData method with
> Bulkcopy object to export one table in tab delimited, text file format to
> C:\TableName.txt. First row of text file should be column headers.
> Anybody know offhand?
> [TableName].ExportData ?
> Thanks for your help ...
>
>|||I like to use T-sql script wherever possible, but sounds like this is one of
those times when it's best not to. Thanks for your help.
"JT" wrote:

> When you refer to the BulkCopy object and ExportData method, you must be
> speaking of Data Management Objects (DMO), which isn't easily called from
> T-SQL. The best and most prevalent method of exporting data to a text file
> would be to create a DTS package.
> "bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
> news:FD5558FE-CB29-44A6-ADA8-24DD306527CE@.microsoft.com...
>
>

Export to Tab Delimited TXT - SQL Reporting Services

I am new to Reporting tools. How can I export the output to Tab Delimited TXT. I found some solutions, however, I could not understand how to proceed with them... anybody can explain the solution step by step...

Hi Pradeep,

Try some of these links

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924133&SiteID=1

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74882

|||

Thanks Sudhir for the reply, however, I need to export the SQL Reporting Services REPORT output to TAB Delimited TXT file.

|||

Hi,

From your description, it seems that you want to change the CSV extension to a Tab delimited output in your reporting service, right?

If so, you should add a new extension line to the RSReportServer.config file to enable the Tab delimited output.

In the config file, you should change the following settings:

1. For tab-delimited report, use
<FieldDelimiter> </FieldDelimiter>

2. For name override, you must specify language attribute:
<Name Language="en-US">TXT (Tab Delimited Text File)</Name>

For more information, see:http://msdn2.microsoft.com/en-us/library/ms156281.aspx

Thanks.

Export table in comma delimited format - how to eliminate empty spaces

Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>

---

|||Peter,

quote:

---

> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?

---

You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Peter,
>
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>

---

Export table in comma delimited format - how to eliminate empty spaces

Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
--
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
--
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>|||Peter,
> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...
> Peter,
> > When I export a table in comma delimited format, it works OK, but
> > there is one problem. If the field is varchar (or char) and has
> > the size, for instance, 50, there are empty spaces in the text
> > file, so it looks like this:
> >
> > I need to elimitate these free spaces somehow, so the text file
> > would look like this:
> >
> > "CBAS","Bass Gambling Supplies",2
> > "CBW","Ben's Wholesale",3
> > "CTDI","3 Diamond",4
> > "NONE","NON-CUSTOM",1
> >
> > How would I do it, either manually or programmatically?
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>

Export SQL Server to a comma delimited file

Hi,

I'm trying to deploy my Web site to GoDaddy. They told me I have to export the SQL Server Express database to a comma delimited file and then upload that file. The export procedure is simple in Access but I don't see any way to do it in SQL Server or from Visual Web Developer or Visual Studio.

Also, I can ask them, but I assume I have to export each table separately and also export the ASPNETDB as well.

Thanks for the help

Open SQL Management Studio express edition [if not installed then down load and install the same ] , use import export wizard to export to a flat file

|||

Thanks Rmaiya,

I downloaded the SQL Management Studio. I think I now need to learn how to use it. I can't seem to open my database. When I go to Open I get "there is no editor available for D:\myfolder\app_data\mydatabase.mdf. Make sure the application for the file type (.mdf) is installed." Also, where do you find the import export wizzard?

I'll try reading the Help instructions tomorrow.