SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


export fixed-length flat file


export fixed-length flat file

Author
Message
BarbW
BarbW
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1130
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?
Ben Sullins-437405
Ben Sullins-437405
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 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...
BarbW
BarbW
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1130
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
Ben Sullins-437405
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 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...
BarbW
BarbW
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1130
Thank you for the help. I think we have a solution to the fixed length export. Your example can help for ad hoc exports. BigGrin
sqlguru-532944
sqlguru-532944
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 23
Can you list what was y our solution?

Thank you
BarbW
BarbW
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1130
The datatype of the table was nvarchar. I changed datatype in table to varchar, and used bcp export utility.
sqlguru-532944
sqlguru-532944
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
BarbW
BarbW
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1130
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
sqlguru-532944
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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. ??
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search