export fixed-length flat file

  • 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?

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

  • 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?

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

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

  • Can you list what was y our solution?

    Thank you

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

  • 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

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

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

  • 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

  • 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)

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

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