February 27, 2015 at 9:40 am
I found this from a web site
SELECT * FROM Salesorders;
OUTPUT TO 'C:\\\tets\sales.csv'
FORMAT TEXT
QUOTE '"'
WITH COLUMN NAMES;
However I do not want the delimiter to be a comma. Instead I plan to use something like ****%****
Please help
February 27, 2015 at 9:46 am
SSRS or SSIS.
February 27, 2015 at 9:54 am
Sql server management studio
February 27, 2015 at 10:02 am
The syntax of the query you posted is never going to work. Not sure where you found that but it is not even in the ballpark.
You can however achieve what you want a lot simpler. You can set this directly in SSMS.
Tools -> Options -> Query Results -> SQL Server -> Results to Text
On the top right side is the output format. Change to Custom delimiter and enter whatever you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2015 at 10:24 am
That code is from Sybase.
To export to csv, you need to use the bcp utility. If you want to call it from SSMS, you have to use xp_cmdshell.
February 27, 2015 at 10:26 am
I know what you mean.
SSMS allows me to save the data in a file that has the extension .rpt
So my next question is what tool do I use to reimport this data to another database ?
OK, I am happy with the .rpt file. But what good will that do if I can not import the data back to another database.
IDEALLY what I want is a CSV file.
( I am not in a position to use BCP, Since I only have readonly access to this database that has the data )
February 27, 2015 at 10:27 am
Louis can you please pass an example of what command to user. Keep in mind , I need a CSV file.
And I need the delimiter to be '****%*****' please
February 27, 2015 at 10:31 am
Sorry Sean was right
TOOLS-->OPTIONS does give you the options to save with a custom delimiter.
I am all set. I think, LETS SEE HOW IT GOES
February 27, 2015 at 10:36 am
Sorry, ran into an issue again ( See attached file )
SQL Sever 2012 gives you an option to add a custom delimiter.
However you can only enter one character ( I tried this and it did not allow me to enter more than one character ) BOMER
So I will need the syntax for the BCP utility.
February 27, 2015 at 10:41 am
It would be something like
EXEC xp_cmdshell 'BCP.exe "Test.dbo.Table" Out "C:\Table.csv" -c -t"****%*****" -SMyTestServer -T -m10 -e"C:\Table.err" -o"C:\Table.out"'
I linked the article on bcp utility in my previous post. It will give you all the options available.
Remember that local paths refer to the server that is running the command.
February 27, 2015 at 10:44 am
Sorry I have no way to copy this.
I tried to highlight this and only part gets highlited.
Can you copy and paste the entire command in one line please
February 27, 2015 at 10:46 am
This site has all the options for SQL sever 2014. I am going to try this
https://msdn.microsoft.com/en-us/library/ms162802.aspx
bcp "SELECT FirstName, LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T
February 27, 2015 at 10:57 am
OK, this is good if you were to gram all the rows in a table.
Sorry, I need to able get the data from a SQL query
Can you modify your BCP command and post please. And can you please have that enetered as one sentence ( I mean don't put in around a scrolling ares it is apain to copy from that )
February 27, 2015 at 11:14 am
You actually posted the way to export a query and the link specifies all the options available.
Maybe you found this version more readable.
To copy the previous version is really easy. Just click on the start of the line and go down.
DECLARE @BCP varchar(8000);
SET @BCP = 'BCP.exe ' --Command
+ '"SELECT Id, myDate FROM Test.dbo.Test_Dates WHERE myDate IS NOT NULL" ' -- Source
+ 'queryout ' --bcp option
+ '"C:\Table.csv" ' --Datafile
+ '-c ' --character data type
+ '-t"****%*****" ' --field terminator (column delimiter)
+ '-SEKT522038 ' --Server
+ '-T ' --Trusted Connection (Use -U and -P for user and password)
+ '-m10 ' --Maximum number of errors
+ '-e"C:\Table.err" ' --Error file
+ '-o"C:\Table.out"'; --Output file
EXEC xp_cmdshell @BCP;
EDIT: If the query is too complex, you could use a stored procedure or a view.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply