Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

export table Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 3:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 9, 2015 6:45 AM
Points: 440, Visits: 370
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.?
Post #1604701
Posted Monday, August 18, 2014 3:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 9, 2015 6:45 AM
Points: 440, Visits: 370
I have text which is causing me issue

Text having data with value '369 Pacific ave, NC. (07818)'
Post #1604707
Posted Thursday, August 21, 2014 1:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 544, Visits: 1,192
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.

Post #1605972
Posted Thursday, August 21, 2014 8:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:07 PM
Points: 36,857, Visits: 33,686
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1606090
Posted Friday, August 22, 2014 3:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:41 AM
Points: 2,260, Visits: 1,602
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
Post #1606167
Posted Friday, August 22, 2014 6:06 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 544, Visits: 1,192
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
Post #1606576
Posted Friday, August 22, 2014 11:42 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 3,059, Visits: 8,706
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.
Post #1606590
Posted Sunday, August 24, 2014 9:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:07 PM
Points: 36,857, Visits: 33,686
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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1606959
Posted Monday, August 25, 2014 10:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 544, Visits: 1,192

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.
Post #1607130
Posted Monday, August 25, 2014 11:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 5,179, Visits: 4,644
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
Post #1607149
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse