Exporting Fixed Width Text Files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exporting Fixed Width Text Files

Author

Topic

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-11-06 : 18:02:00

I am having trouble exporting data to a text file in a fixed width format using the wizard in SQL Server 2005. When I try and export the data in fixed width format it is scattered all over the file. Has anyone had this same issue? I would like to create a file that looks like this. I have been able to work around this by exporting the file in delimited format and using a query like this.

SELECT RecordNumber + State + LineOfBusiness ...

01CA00214
02CA00214
03FL00216
04FL00216
05NY00217

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 00:15:29

What rowterminator are you using?

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-07 : 05:10:32

I had similar problems and at least in my case it was the collation of each column that messed things up. I used BCP (no wizards) and I had to create a format file to make it work.

SQL store procedure:

SELECT CAST(RecordNumber AS char(10)) 
  + CAST(State AS CHAR(20))
  AS AllColumns
FROM ...

Format file:

8.0
1
1  SQLCHAR  0   268  "\r\n"  1  AllColumns  Danish_Norwegian_CI_AS

BCP-command:

bcp "EXEC mydb.dbo.myproc" queryout "D:\filepath\filename.txt" 
-T -S mydbinstance -f D:\filepath\bcp.fmt

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-11-07 : 13:12:09

I solved my issue I had to use the "Ragged right" setting in the Export wizard instead of "Fixed Width" It works but still does not make any sense to me. Can the format file be used to allign text right or left justified?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-08 : 06:59:37

Hm, don't think so...you can do the formattign in sql though.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources