Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Today @ 12:38 PM
Points: 14,409, Visits: 37,697
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

--
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!
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 40,772, Visits: 38,020
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."

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


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 40,772, Visits: 38,020
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."

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