August 29, 2018 at 1:12 pm
This T-SQL works perfectly (and i AM aware of security implications of enabling xp_cmdshell, not an issue here...)
DECLARE @table_name VARCHAR(50) ='AgentMaster'
DECLARE @columnHeader VARCHAR(4000)
declare @crude_sql varchar(4000)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name
SELECT @crude_sql = 'bcp "SELECT '+ @columnHeader +' UNION ALL SELECT * FROM AgentsDb..AgentMaster" queryout C:\Bcpl\agentfile.txt -c -t, -T -S '+ @@servername
EXEC xp_cmdshell @crude_sql
However, it outputs columns delimited by COMMA. And I need TAB-COLUMN-DELIMITED output text file.
What do I need to change in my bcp command for that?
Thanks
Likes to play Chess
August 29, 2018 at 1:25 pm
Can't people read manuals anymore? it is quite clear what you need to do on it.
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017
August 29, 2018 at 1:26 pm
It's in the documentation. You're never going to get better at this if you don't even bother to try figuring this out for yourself. I'll give you a hint, it's the default and a comma is showing up, because you've overridden the default.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2018 at 1:36 pm
Thank you!
I looked it up in the documentation that you pointed me at, and found out where i overwrote it. I did not first realize that the comma actually means col delimiter and not just a part of command line syntax. Thanks.
Likes to play Chess
August 29, 2018 at 1:38 pm
this now works
........txt -c -t \t -T -S '+ @@servername
Likes to play Chess
August 29, 2018 at 1:51 pm
VoldemarG - Wednesday, August 29, 2018 1:36 PMThank you!
I looked it up in the documentation that you pointed me at, and found out where i overwrote it. I did not first realize that the comma actually means col delimiter and not just a part of command line syntax. Thanks.
No, the -t means column delimiter, the comma has no meaning in and of itself. It is only meaningful, because it follows the -t.
VoldemarG - Wednesday, August 29, 2018 1:38 PMthis now works
........txt -c -t \t -T -S '+ @@servername
You do not need to specify the column delimiter if you are using the default. You can safely drop -t \t.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2018 at 2:14 pm
What I cannot quite figure from the documentation though is how to get my HEADER ROW DELIMITER be {CR}{LF} while ROW DELIMITER just {CR}
PROBLEM:
i tried may switches combinations on bcp command line but cannot get that .
the goal is to import this file (produced by above bcp command) into an SSIS package that is looking for different Header and Row delimiters as shown in 2 attached screenshots.
Thank you.
Likes to play Chess
August 29, 2018 at 2:27 pm
VoldemarG - Wednesday, August 29, 2018 2:14 PMWhat I cannot quite figure from the documentation though is how to get my HEADER ROW DELIMITER be {CR}{LF} while ROW DELIMITER just {CR}
PROBLEM:
i tried may switches combinations on bcp command line but cannot get that .
the goal is to import this file (produced by above bcp command) into an SSIS package that is looking for different Header and Row delimiters as shown in 2 attached screenshots.Thank you.
Those screen shots show the same delimiters for both the header rows and the detail rows. It would be highly unusual for there to be different delimiters.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply