export table

Question

Post reply

export table

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
August 18, 2014 at 3:36 pm

#397218

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 1

I have text which is causing me issue

Text having data with value '369 Pacific ave, NC. (07818)'

MMartin1 One Orange Chip Points: 27764 More actions · Answer 2

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.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lempster SSCoach Points: 15651 More actions · Answer 4

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 One Orange Chip Points: 27764 More actions · Answer 5

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

----------------------------------------------------

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

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.

😎

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27764 More actions · Answer 8

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.

----------------------------------------------------

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

Feel I'm starting to repeat my self on the issue:pinch:

Using the RFC-4180, commas and double quotes within a column/field/attribute are not a problem. The problem is more that many of the tools available are not compliant to this RFC, that in my opinion is what needs to be fixed. Switching from one delimiter to another is a form of procrastination, not a solution.

😎

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 11

Eirikur Eiriksson (8/25/2014)
Feel I'm starting to repeat my self on the issue:pinch:

BWAAA-HAAA!!! Me too!

Switching from one delimiter to another is a form of procrastination, not a solution.

I'll have to register my strong disagreement with that statement. 😉 For example, TSV is so much easier to implement that switching the delimiter to TABS IS one of the better solutions. The CSV standard that you've cited is fraught with vertical (inconsistent) exceptions that cause a world of hurt for those that have to import it. TSV solves most of those problems, especially in the world of BULK INSERT, quickly and easily.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 12

Jeff Moden (8/25/2014)
Eirikur Eiriksson (8/25/2014)
Feel I'm starting to repeat my self on the issue:pinch:
BWAAA-HAAA!!! Me too!
Switching from one delimiter to another is a form of procrastination, not a solution.
I'll have to register my strong disagreement with that statement. 😉 For example, TSV is so much easier to implement that switching the delimiter to TABS IS one of the better solutions. The CSV standard that you've cited is fraught with vertical (inconsistent) exceptions that cause a world of hurt for those that have to import it. TSV solves most of those problems, especially in the world of BULK INSERT, quickly and easily.

Until a field/record/column contains TAB characters then oops lets switch to PIPE....;-)

😎

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 13

Eirikur Eiriksson (8/25/2014)
Jeff Moden (8/25/2014)
Eirikur Eiriksson (8/25/2014)
Feel I'm starting to repeat my self on the issue:pinch:
BWAAA-HAAA!!! Me too!
Switching from one delimiter to another is a form of procrastination, not a solution.
I'll have to register my strong disagreement with that statement. 😉 For example, TSV is so much easier to implement that switching the delimiter to TABS IS one of the better solutions. The CSV standard that you've cited is fraught with vertical (inconsistent) exceptions that cause a world of hurt for those that have to import it. TSV solves most of those problems, especially in the world of BULK INSERT, quickly and easily.
Until a field/record/column contains TAB characters then oops lets switch to PIPE....;-)
😎

True enough... that's when then sometimes find out that they have both TABs and PIPEs in their data. It would be a lot simpler if we went back 40 years and used the correct ASCII characters for text data transmissions... characters 28 through 31 in the ASCII table.

http://www.asciitable.com/

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 14

Jeff Moden (9/3/2014)
Eirikur Eiriksson (8/25/2014)
Jeff Moden (8/25/2014)
Eirikur Eiriksson (8/25/2014)
Feel I'm starting to repeat my self on the issue:pinch:
BWAAA-HAAA!!! Me too!
Switching from one delimiter to another is a form of procrastination, not a solution.
I'll have to register my strong disagreement with that statement. 😉 For example, TSV is so much easier to implement that switching the delimiter to TABS IS one of the better solutions. The CSV standard that you've cited is fraught with vertical (inconsistent) exceptions that cause a world of hurt for those that have to import it. TSV solves most of those problems, especially in the world of BULK INSERT, quickly and easily.
Until a field/record/column contains TAB characters then oops lets switch to PIPE....;-)
😎
True enough... that's when then sometimes find out that they have both TABs and PIPEs in their data. It would be a lot simpler if we went back 40 years and used the correct ASCII characters for text data transmissions... characters 28 through 31 in the ASCII table.
http://www.asciitable.com/

Sarting to look like a good case for XML

😎

BTW one should not need that ASCII reference, one must know this by heart like any other alphabet:-D