Link to home
Start Free TrialLog in
Avatar of jsctechy
jsctechyFlag for United States of America

asked on

create a fixed-width file from a query and stored as a asc file

hi there,
how can i create a fixed-width file from a SQL Statement?
lets said i have this SQL Statement:
SELECT     NUMBER_KEY, DEV NUMBER, DATA_LEVEL, SUB_PRIMARY
FROM         CD_Main

and i need to write a file with the following format
Field Name              From       To       Lenght
NUMBER_KEY           1           12         12 spaces
DEV NUMBER            13         44         32 spaces
BLANK                       45        46          1 space
DATA_LEVEL            47        50          3 spaces

and store the file with the following name "cntyfile.asc"

How can i do this?
Thanks,
jsctechy
SUB_PRIMARY
ASKER CERTIFIED SOLUTION
Avatar of brejk
brejk
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jsctechy

ASKER

how can i use bcp.exe command line utility?
Can you elaborate a little bit more
1. Create a table:

USE tempdb
GO

CREATE TABLE Format (
NUMBER_KEY char(12),
DEV NUMBER char(32),
BLANK char(1),
DATA_LEVEL char(3)
)

2. From command line do the following:

bcp "tempdb.dbo.Format" format nul -f c:\format.fmt -T

(if you use a named instance use -S YourServerName also, if you use SQL logins use -U LoginName -P Password instead of -T)

Press enter until you will be asked about field separator for the last column (SUB_PRIMARY) when you need to answer: \n.

3. From command line do the following:

bcp "SELECT  NUMBER_KEY, DEV NUMBER, DATA_LEVEL, SUB_PRIMARY FROM YourDatabase.dbo.CD_Main" queryout c:\cntyfile.asc -T -f c:\format.fmt

4. Use notepad or any other text editor to view c:\format.fmt.
is there a way to do this using the management window?
You can also use DTSWizard.exe utility (it's a GUI application). Choose Fixed Width format and use a query that uses CAST function to convert all columns to chars (just as I wrote it in my previous post).
I select this solution because this line:
CAST(NUMBER_KEY AS CHAR(12))
will keep the text growing if i do what you have suggested AS NUMBER_KEY that will put a space between my fields.
Thank you