Geek Answers Handbook

Export data as a fixed-width file from SQL Server 2005

I thought a very simple task was to export the data in the view from SQL Server 2005 to a fixed-width text file. But a wizard is a pain. The format is incorrect. Does anyone know how to deal with this? or is the best way to do this?

+3
source share
4 answers

Use bcp with queryout parameter http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

bcp "SELECT * FROM AdventureWorks.Person.Contact" queryout Contacts.txt -c -T

Use format file if you want fixed width output

http://weblogs.sqlteam.com/brettk/archive/2006/07/06/10504.aspx

I just tried exporting to AdventureWorks, Fixed Width also gave me a lot of problems (compared to column separators) I had to ignore GUID columns, not include column names in the first row, etc. Finally, export the Sales.Customer table

Without BCP, you might have to search in SSIS or SQLCMD

+5
source

One option is to use OpenDataSource to write strings to a text file. This requires that the text file already exist, but is relatively easy.

This article (for disclosure, I wrote it, and it focuses on reading, but also affects writing) explains the basics of reading and writing from text files using OpenDataSource .: http://www.sqlservercentral.com/articles/OpenDataSource/61552/

+1
source

If the wizard format does not meet your needs, you will need to develop your own SSIS package. Are you sure you really need a fixed-width file? A delimited file is likely to be easier to get right, as they are much more common.

0
source

I found that SQL2005 / SSIS is nothing more than just doing seemingly simple tasks. Things always take me 5 times longer than necessary. I know that I'm not alone on this.

BCP should work.

I do not know about sqlcmd - it seems that it does not fix the width, unless you filled in your sql statement, which would also be a pain.

 usage: Sqlcmd [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-z new password] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit] [-k[1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p[1] print statistics[colon format]] [-R use client regional setting] [-b On error batch abort] [-v var = "value"...] [-A dedicated admin connection] [-X[1] disable commands, startup script, enviroment variables [and exit]] [-x disable variable substitution] [-? show syntax summary]
0
source

More articles:


All Articles