SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


export table


export table

Author
Message
Nita Reddy
Nita Reddy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 411
I want to export one table but text contains ',' so can I use double " ? I don't see option when I select delimiter how should I do it.?
Nita Reddy
Nita Reddy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 411
I have text which is causing me issue

Text having data with value '369 Pacific ave, NC. (07818)'
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7465 Visits: 2033
These are not the best ways likely, but a couple of ideas I have are:
1) Use SSIS and a script component with a dataflow to add text qualifiers to your output columns.

2) Query the table using For XML Path('') to create a xml output of the data.

----------------------------------------------------
How to post forum questions to get the best help
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223773 Visits: 42007
Nita Reddy (8/18/2014)
I want to export one table but text contains ',' so can I use double " ? I don't see option when I select delimiter how should I do it.?


Pick a different delimiter. TAB is easy.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lempster
Lempster
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5226 Visits: 1657
MMartin1 (8/21/2014)
These are not the best ways likely, but a couple of ideas I have are:
1) Use SSIS and a script component with a dataflow to add text qualifiers to your output columns.

2) Query the table using For XML Path('') to create a xml output of the data.


If it's just one table and particularly if it contains a lot of rows (hundreds of thousand or more) I'd use bcp; you don't need to worry about a delimiter then, but if you want to use SSIS then as Jeff said, pick a different delimiter. I usually use | (pipe) or ^ .

Regards
Lempster
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7465 Visits: 2033
Following up on the BCP suggestion this is actually a good idea >
Open a command line window and type something like

bcp "adventureWorks2008R2.Person.address" out "c:\myBcpOut.txt" -c -T

If you need to supply user name and password (if you are not on a trusted network) then instead of the -T you will have to supply values for the -U and -P switches (-Umyusername -Pmypassword)....if you are logged into the machine you will not need to add a server name to the -S switch.

I did the above, the table I used has commas in the address line field. I was able to open the tab delimited file in Excel and all was properly aligned.

Give it a go

----------------------------------------------------
How to post forum questions to get the best help
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42276 Visits: 19526
Nita Reddy (8/18/2014)
I want to export one table but text contains ',' so can I use double " ? I don't see option when I select delimiter how should I do it.?


Quick suggestion, use the (close to industry standard) CSV (RFC-4180) format with ["] text qualifiers. Should be no problem at all.
Cool
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223773 Visits: 42007
Eirikur Eiriksson (8/22/2014)
Nita Reddy (8/18/2014)
I want to export one table but text contains ',' so can I use double " ? I don't see option when I select delimiter how should I do it.?


Quick suggestion, use the (close to industry standard) CSV (RFC-4180) format with ["] text qualifiers. Should be no problem at all.
Cool


Better yet, don't use commas for delimiters. Use TABs instead. The folks on the receiving end of the data will love you for it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7465 Visits: 2033

Better yet, don't use commas for delimiters. Use TABs instead. The folks on the receiving end of the data will love you for it.


Agreed, if you have not already, try the BCP suggestion. I give a command example (above) that will generate a tab delimeted file.

----------------------------------------------------
How to post forum questions to get the best help
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51439 Visits: 10844
MMartin1 (8/25/2014)

Better yet, don't use commas for delimiters. Use TABs instead. The folks on the receiving end of the data will love you for it.


Agreed, if you have not already, try the BCP suggestion. I give a command example (above) that will generate a tab delimeted file.

Agreed. If the people receiving the data have some columns with some rows quoted and some not, how do you think they'll react?


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search