export query results into CSV file issue in SQL Server

Question

I am using SQL Server 2008 Enterprise. I want to export the query result into csv file from SQL Server Management Studio. The issue is, the default csv file is comma (',') separated for each result column in each exported row, and I want to make it '\t' separated since comma exists in some of the result column values.

Any quick solutions?

thanks in advance, George

Solution 1

If you really want the \t delimiter write a query like this

select 
  Cast(PersonCode as varchar(50)) + '\t'
  + Cast(PersonReference as varchar(50))    
from People

The casts are just incase you aren't working with varchar types, but you don't need them if you are. Run this query with results to text and just paste the result into notepad or similar.

Solution 2

When you click "Save as" and you are prompted to enter a filename, drop down the box below the filename and select "Text (Tab delimited)"

Comments

[Mark Dickinson]

You don't even need to paste. Using results to grid, just right click and save results as, using all files as the file type, to allow you to save as a txt file or whatever you want.

[Mitch Wheat]

@ Mark Dickinson: isn't that what I said in my answer?! :)

[George2]

Thanks, it works in SQL Server 2008, and I have tried in SQL Server 2005 no such feature?

[George2]

Not working for me since I have binary type if case is not used. Here is the error message. My further question, if I cast binary to varchar, then cast back, is it ensured that originial value of binary is restored? Msg 402, Level 16, State 1, Line 1 The data types nvarchar and varbinary(max) are incompatible in the add operator.

[Mark Dickinson]

@Mitch: I don't have that option in my save results as. I just get Export File (csv), or All files (.). Maybe I misunderstand the '\t' in the question, does that mean tab? Anyway my example means you could use War and Peace for a delimiter if you want so I still think its good :)

[Mark Dickinson]

@Mitch, I'm on 2005 in the office, that might explain the difference.

[George2]

The columns in the table contains, uniqueidentifier type, int type, nvarchar type, datetime type and varbinary(max) type, I am not sure whether cast them all to nvarchar type then cast back will restore the exact original value?

[Mark Dickinson]

@George2: Casting binary to a char type should be ok, it would loose any leading zeros if you cast to numeric type.

[George2]

"if you cast to numeric type" -- do you mean cast from numeric type to nvarchar type? :-)

[Mark Dickinson]

I meant, if you cast binary to nvarchar, you should be ok. I'd probably go for a varchar(max) from a varbinary(max)

[George2]

Thanks Mark, 1. I have tested your solution and it works -- at least no error from SQL Server Management Studio. I have casted int/datetime/varbinary(max)/uniqueidentifier to nvarchar(max), is that fine? No data lost? 2. Another question is how to restore such data to another table of the same schema (I found there is only one column when we use cast and +)?

[Mark Dickinson]

Here's a starter sqlteam.com/forums/topic.asp?TOPIC_ID=50648, you may obviously need to get this to return more than two columns. You run the create function script then you can query it like a view or a table.

[George2]

Thanks Mark, how about your answer and comment to this question -- "Cast int/datetime/varbinary(max)/uniqueidentifier to nvarchar(max), is that fine? No data lost?"? :-)

[Mark Dickinson]

If you are able to see both tables in your management studio. What's to stop you just writing an Insert...Select query. It doesn't matter if the tables are in different dbs as long as you have permission to use both.

[George2]

Hi Mark, the two tables are from two database servers. I have permission, and currently I am using linked server to bring the remote database server to the local database server's Management Studio. Can we write insert from one table inside one database server to another table of another database server?

[Mark Dickinson]

Try writing something like select * from sourcedb.user.sourceTable . If it works try writing Insert into destDb.user.destTable select * from sourcedb.user.sourceTable . user is usually dbo. Being explicit about the columns gives you change to cope with any schema changes.

[Mark Dickinson]

Hope that helps, I'm off to the chippy now :)

[Robert Calhoun]

When you save the results set that way, it only saves the selected rows. Right-click, select all.