creating fixed column format text files

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

creating fixed column format text files

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-09 : 16:38:01

I need to create a dts package that will run once per day and create a new text file in a certain folder on the server. the text file is for ach transactions. it will have some data that will stay the same. for example, company name, etc... some data i will need to pull from the db itself. what will be the best way to do this? the file will have fixed length columns

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 16:53:04

Duuuuuuuuuude, I worked on something like this over a year ago! I'll try to find the code, but I can tell you one thing: DTS ain't the way to go. You *could* do it with DTS, but I got really frustrated and ended up writing a SQL query that could be called using bcp. All of the entry formatting and ordering was done in SQL. It's ugly code, but it works.

You might also want to check with the bank(s) you'll be dealing with whether they require balanced batches or not. Those that do will need an extra entry in each batch to offset the credit/debit of the entry. I think I have code to generate that too, but I gotta go digging.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-09 : 16:59:09

that would be great if you could find some code. i need all the help i can get!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 17:21:52

BTW, do you have specification documents on the format? Did you get them from the bank? If you didn't, do that first. If you're not familiar with the format there's no point in starting until you do. Also make sure to contact them about their particular info (financial institution ID, account numbers, and so on)

Also, do you have anything set up for this yet?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-09 : 22:18:03

For something like this I usually create the query in an sp

create mysp
as
select left(name + space(50), 50)
+ left(addr1 + space(50), 50)
+ left(convert(varchar(3),age) + space(4), 4)
+ left(convert(varchar(9),dob,112) + space(9), 9)
from mytbl
go

then when that looks good in query analyser just call it using bcp and queryout.
In that way you can test the format easily without having to look at disk files all the time.
I would also include datetime in the filename to give yourself a history of what's happened.

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

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-10 : 10:13:07

i am familiar with the process and file specs. i have all the banks information. so far all i have is a test file that i'm sending to the bank to make sure i have the format correct. if i use a sp to create the file, i have the following questions:
how do i put in a line break? how do i use bcp to write the contents to a dynamically created file name? need all the help i can get. much appreciated.....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-10 : 10:31:54

line break?...look into including char(10) and/or char(13) in your selct statements...both have come up in topics here before.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 10:50:02

The sp above will create format into lines. The bcp will automatically put a crlf at the end of each line.

The sp to do the bcp should be something like

declare @filename varchar(200)
select @filename = 'c:\exportfiles\mybankfile_' + convert(varchar(8),getdate(),112) + '_' + replace(convert(varchar(8),getdate(),108),':','') + '.txt'
declare @cmd varchar(2000)

select @cmd = 'bcp "exec mydb.dbo.s_exportformat" queryout ' + @filename + ' -c'
exec master..xp_cmdshell @cmd

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

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-10 : 10:51:36

ok, i'll figure out the line break. i now know how to use bcp. now how can i pass a dynamically created file name to bcp as well?

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-10 : 10:52:32

great! i'll get started using that! thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 11:18:43

To add a line break in the middle of a data row exported use char(13) + char(10).
You might want to decalre a variable and use that if it's used a lot.

create proc s_exportformat
as
select left(name + space(50), 50) + char(13) + char(10)
+ left(addr1 + space(50), 50)
+ left(convert(varchar(3),age) + space(4), 4)
+ left(convert(varchar(9),dob,112) + space(9), 9)
from mytbl
go

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-10 : 12:58:05

You can call bcp from T-SQL using the xp_cmdshell procedure:

DECLARE @filename varchar(100), @cmd varchar(8000)
SET @filename='some file name.txt'
SET @cmd='bcp "exec mydb..myproc" queryout ' + @filename + ' -Sserver -T -c'
EXEC master..xp_cmdshell @cmd

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-10 : 13:56:19

if i wanted to declare a line break as a variable what datatype do i use? i tried this and it doesn't work

declare @lb varchar(50)
select @lb = 'char(13) + char(10)'
select 'test' + @lb + 'test'

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 13:59:33

declare @lb varchar(2)
select @lb = char(13) + char(10)
select 'test' + @lb + 'test'

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 14:01:31

I tend to build the resultset a line at a time in a temp table rather than embedding newlines.
The final select sorts the lines in order based on columns in the temp table.

Depends on the format though

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-10 : 14:35:56

Why not just us a format ~~card?~~ FILE...damn DB2

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources