Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

export fixed-length flat file Expand / Collapse
Author
Message
Posted Thursday, May 22, 2008 9:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:03 AM
Points: 2,490, Visits: 1,026
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?
Post #505272
Posted Thursday, May 22, 2008 10:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267
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...
Post #505325
Posted Thursday, May 22, 2008 10:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:03 AM
Points: 2,490, Visits: 1,026
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?
Post #505331
Posted Thursday, May 22, 2008 11:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267
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...
Post #505350
Posted Thursday, May 22, 2008 12:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:03 AM
Points: 2,490, Visits: 1,026
Thank you for the help. I think we have a solution to the fixed length export. Your example can help for ad hoc exports. :D
Post #505395
Posted Friday, June 13, 2008 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 2:33 PM
Points: 3, Visits: 23
Can you list what was y our solution?

Thank you
Post #516911
Posted Friday, June 13, 2008 11:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:03 AM
Points: 2,490, Visits: 1,026
The datatype of the table was nvarchar. I changed datatype in table to varchar, and used bcp export utility.
Post #516926
Posted Friday, June 13, 2008 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 2:33 PM
Points: 3, Visits: 23
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
Post #516936
Posted Friday, June 13, 2008 12:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:03 AM
Points: 2,490, Visits: 1,026
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.
Post #516951
Posted Monday, June 16, 2008 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 2:33 PM
Points: 3, Visits: 23
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. ??
Post #517736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse