Solved

Query Results to Text File

Posted on 2011-03-15
3
380 Views
Last Modified: 2012-05-11
Is it possible to run a query and have results export to text file in pre-defined column widths with leading zeros if required?

I'm hoping I may be able to build a text file that I can import into a system.  It's 5 fields, all numeric, the results would need to look like this....

00000100002000000000300000000040000000005000000000

First field is 10,000
2nd is 2,000,000,000.....etc
0
Comment
Question by:tobin46
3 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 35144316
Check the SQL - BCP QUERY OUT for your requirements.
0
 
LVL 9

Accepted Solution

by:
sshah254 earned 350 total points
ID: 35144339
Use command line SQL - SQLCMD

Use the necessary padding functions from here - http://www.sqlusa.com/bestpractices2005/padleadingzeros/

and Concatenate your results.

Ss
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 150 total points
ID: 35144341
several options

SELECT RIGHT ( replicate('0', 10 ) + cast(10000 as varchar)  , 10 ) +  RIGHT ( replicate('0', 10 )+cat(secondcolumn as varchar)  , 10 ) + ..

if you want to build the file from you can use the osql utitlity

http://msdn.microsoft.com/en-us/library/aa214012(SQL.80).aspx
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question