Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-11-28 : 08:17:02
|
I have to export some of the data from my database to a flat file of fixed length fields. I'm thinking about bcp..any other suggesstions?Since the output has to be fixed length lets say column fname should be 10 character and age should be 3 charif the column has the data something like thisfname agekk 28then in flat file kk should have 8 trailing spaces and 28 should be 028, how do I achieve this? After going through some threads in the forum,I tried with ansi_padding setting for fname column it didnt work. Here is the codeset ansi_defaults offset ansi_padding oncreate table test (fname varchar(10),age int)insert into testselect 'kk',28select * from testdrop table testset ansi_defaults onset ansi_padding off ThanksKarunakaran |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-11-28 : 09:58:37
|
quote:
Originally posted by X002548
look up format cards in Books Online...and post the DDL of your tableWhat version of SQL Server?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
EDIT: Oops...Didnt post the reply at all.... Here is the reply:Version Sql2k EE. I looked up format file, couldnt understand it clearly. Bascially I want to push some data from 3-4 tables using joins, so I thought if I can get the output using some sample table I can apply the same for the original query. Thanks |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-11-28 : 12:03:22
|
| It would help if you read the hint link in my sig...if you bcp out using dos, it will create a format file for you...I use bcp a lot and it is easy (ok, it's easier the more you use it), but why not DTS?besides DTS is going away, or it is being reincarnated...they didn't even mention it during the launch....do a search here at sql team on format file..should be lots of examples....but read the hint linkBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-28 : 13:00:12
|
| I usually do that by formatting the data in the queryselect right(' ' + convert(varchar(3),age), 3)+ left(convert(varchar(3),name) + space(80), 80)+ ...then use queryout in the bcp.For format files have a look athttp://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-11-29 : 08:15:22
|
quote:
Originally posted by X002548
It would help if you read the hint link in my sig...if you bcp out using dos, it will create a format file for you...I use bcp a lot and it is easy (ok, it's easier the more you use it), but why not DTS?besides DTS is going away, or it is being reincarnated...they didn't even mention it during the launch....do a search here at sql team on format file..should be lots of examples....but read the hint linkBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
Brett,I do agree the need to post the DDL and DML, not only to get the answers quick but also to save others time who help.There are some situations where you dont have the option of posting the same. I'm in a kind of that situation. Thats the main reason why I'm trying with some sample table I posted first.My first option was DTS, but since I have not used BCP much, I thought I'll try with BCP and my belief is this could be easily done in BCP than going for DTS.ThanksKarunakaran |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-11-29 : 08:20:09
|
quote:
Originally posted by nr
I usually do that by formatting the data in the queryselect right(' ' + convert(varchar(3),age), 3)+ left(convert(varchar(3),name) + space(80), 80)+ ...then use queryout in the bcp.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
I think this might be what I wanted..Thanks nrKarunakaran |
|
|
|
|
|
|
|
|
BCP to flat file with fixed length fields