cannot "save query results as.." tab delimited file

Question

Post reply

cannot "save query results as.." tab delimited file

winston Smith

SSCoach

Points: 19500
More actions
April 28, 2009 at 10:12 am

#130300

when i right click on the query results and click "save results", the only format available to me is CSV. how to i enable sql server to save results as tab delimited files?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

quick and dirty is to use grid mode, select the results and copy, then paste directly into Excel;

it pastes right in as tab delimited automatically..

i don't know if you can add the save as option to do tsv instead of csv.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

winston Smith SSCoach Points: 19500 More actions · Answer 2

cant use excel as it doesnt save in the correct encoding, and also there are hundreds of thousands of rows so its not that efficient to use excel.

also excel is buggy, i did once copy 600,000 rows into excel but it kept giving me out of memory errors, despite having plenty of memory available.

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah for big datasets, bcp is the only way to go;

here's an example, works perfectly, creates a tab delimited file:

exec master.dbo.xp_cmdshell 'bcp master.dbo.ALLCITYZIPCOUNTYSTATE out c:\sampleout.txt -S localhost\SQLExpress -T -c -t "\t"'

/*

Import from a file into a table.

in = import from a file

-S = Server\Instance

-T = trusted authentication

-c = character datatypes, tab delimited

-t = override the delimiter ; \t = tab

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

winston Smith SSCoach Points: 19500 More actions · Answer 4

will the tab delimited file created here be Unicode UFT-8 or ANSI though?