Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


export table


export table

Author
Message
Nita Reddy
Nita Reddy
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 387
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
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 387
I have text which is causing me issue

Text having data with value '369 Pacific ave, NC. (07818)'
MMartin1
MMartin1
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: 1120 Visits: 2015
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45207 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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

Group: General Forum Members
Points: 2683 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
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: 1120 Visits: 2015
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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6780 Visits: 17739
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45207 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
MMartin1
MMartin1
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: 1120 Visits: 2015

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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10317 Visits: 9594
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