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


export table


export table

Author
Message
Nita Reddy
Nita Reddy
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 395
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
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 395
I have text which is causing me issue

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

Group: General Forum Members
Points: 2757 Visits: 2031
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 (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3195 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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2757 Visits: 2031
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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15084 Visits: 18597
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 (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2757 Visits: 2031

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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16609 Visits: 10063
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