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 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.

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.