export fixed-length flat file

Question

export fixed-length flat file

BarbW

SSCarpal Tunnel

Points: 4759
More actions
May 22, 2008 at 9:31 am

#188149

Hello,
I am trying to export a 512 char fixed-length flat file with line feed at end of line
using SQL 2005, but the export does not allow the option. It exports the data into
1 long continuous string.
I tried ragged-right option, but the row length varies depending on what text is in
the last column.
Can anyone point me in the right direction?
Should I be using BCP utility for a fixed-length export?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

What I've done in the past to accomplish this is to concatenate all my fields in my select list together while converting to char values with hardcoded lengths. This way your result set is one column that has all the correct fixed lengths. To automate the delivery of it I created an SSRS report with a subscription to deliver it in CSV format.

Hope this helps!

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 2

I tested with bcp utility and have a result to test. If it doesn't work, I will try your

method. Can you print a small sample select statement?

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 3

Like so:

SELECT

CONVERT(CHAR(10), Title)

+CONVERT(CHAR(75), FirstName)

+CONVERT(CHAR(75), LastName)

+CONVERT(CHAR(100), EmailAddress)

FROM

AdventureWorks.Person.Contact

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 4

Thank you for the help. I think we have a solution to the fixed length export. Your example can help for ad hoc exports. 😀

sqlguru-532944 SSC Enthusiast Points: 191 More actions · Answer 5

sqlguru-532944

SSC Enthusiast

Points: 191

June 13, 2008 at 11:07 am

#828683

Can you list what was y our solution?

Thank you

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 6

The datatype of the table was nvarchar. I changed datatype in table to varchar, and used bcp export utility.

sqlguru-532944 SSC Enthusiast Points: 191 More actions · Answer 7

Do you know how we can do it in C#? I am building a C# program to retrieve data from SQL Server and write it out to a file using fixed length format. any ideas?

Thank you

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 8

http://msdn.microsoft.com/en-us/library/ms162802.aspx

BCP is a command line utility used to transfer data from SQL to text files. It is supported by SQL and the exe is located in the BIN folder on the SQL server. There are many on-line support sites where others have developed processes using bcp. I think you should be able to use it with C#, but for fixed-length, you may need a format file (layout). Hope this helps.

sqlguru-532944 SSC Enthusiast Points: 191 More actions · Answer 9

Will BCP allow me to manipluate the data and format it any way I want ? How can I have BCP to run automatically and load data from SQL into a text file? I don't want to run it from the command prompt. I want to be able to load the data weekly and off schedule automatically!!. That's why I want to use C# but if BCP can allow to do the format and run automaticlly, I would use BCP. Any ideas. ??

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 10

No, formatting fixed length fields needs to be done by a using format file or by the table structure. The BCP command is inserted into a scheduled job as a command line. xp_cmdshell must be enabled with SQL 2005 using the surface area configuration tool. I tried some code like this today and it works in SQL 2005. I'm pretty sure SQL 2000 will work also.

Declare @cmd as varchar 1000

Set @cmd = 'bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c'

EXEC master.dbo.xp_cmdshell @cmd

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

barb.wendling (6/16/2008)
No, formatting fixed length fields needs to be done by a using format file or by the table structure. The BCP command is inserted into a scheduled job as a command line. xp_cmdshell must be enabled with SQL 2005 using the surface area configuration tool. I tried some code like this today and it works in SQL 2005. I'm pretty sure SQL 2000 will work also.
Declare @cmd as varchar 1000
Set @cmd = 'bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c'
EXEC master.dbo.xp_cmdshell @cmd

You can run BCP from the command line and schedule it with Windows Task Scheduler. Make a batch file with the BCP command and schedule it. No need for all the xp_CmdShell stuff that way...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)