Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can i create pipe delimited txt file from sql query results Expand / Collapse
Author
Message
Posted Sunday, April 29, 2012 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 29, 2012 3:44 PM
Points: 3, Visits: 3
HI all,
pls can you answer me it's urgent task..i searched in online but no luck yet...
Post #1292137
Posted Sunday, April 29, 2012 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns  ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T ' 



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1292146
Posted Sunday, April 29, 2012 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 29, 2012 3:44 PM
Points: 3, Visits: 3
Hi Lowel,

Thanks for ur quick response ..can i do that in master database because i'm a tester i don't know ..can i do that or not..
Here is my database details..can you answer accordingly..

My db name:Max_Test
My table name: #temp.
I need these columns from that table are offer_id,reservation_num,personal_id,acct_nbr_he.
PLs reply TIA.

Post #1292148
Posted Sunday, April 29, 2012 12:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
tswathi21 (4/29/2012)
Hi Lowel,

Thanks for ur quick response ..can i do that in master database because i'm a tester i don't know ..can i do that or not..
Here is my database details..can you answer accordingly..

My db name:Max_Test
My table name: #temp.
I need these columns from that table are offer_id,reservation_num,personal_id,acct_nbr_he.
PLs reply TIA.



You want that with or without column headers?


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1292182
Posted Sunday, April 29, 2012 12:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Never mind. We'll do it both ways.

Since you're a tester and don't know much about SQL Server or SSMS, we'll do this as simply as possible.

Paste the following code into SSMS on same window that create the #Temp table, highlight the code, and then press the f5 key. Copy the output from the results and paste into notepad. Save the notepad file.

--===== Comment out from here through UNION ALL
-- if you don't want column headers.
SELECT 'Offer_ID' + '|'
+ 'Reserveration_Num' + '|'
+ 'Personal_ID' + '|'
+ 'Acct_Nbr_HE' + '|'
UNION ALL
SELECT CAST(Offer_ID) AS VARCHAR(8000)) + '|'
+ CAST(Reserveration_Num) AS VARCHAR(8000)) + '|'
+ CAST(Personal_ID) AS VARCHAR(8000)) + '|'
+ CAST(Acct_Nbr_HE) AS VARCHAR(8000)) + '|'
FROM temp
;




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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1292184
Posted Sunday, April 29, 2012 3:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 29, 2012 3:44 PM
Points: 3, Visits: 3
Hello Jeff,

Thanks for ur time and quick response..
Actually I did in otherway ..
In SSMS- tools-options--there i select results to text ..and changed the custom delimited to | ..and i ran that it worked for me..Saved the results to a file....

Thank you so much again...
Post #1292218
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse