SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DTS Export to formated TXT file using variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

luisfilipenunes
Starting Member

Portugal
4 Posts

Posted - 10/30/2012 :  21:47:57  Show Profile  Reply with Quote
Hi,


I need to export data from a SQL table into a text file with fixed positions defined as constants.

I wrote the following script for this export using Nortwind DB:

declare @arm char(2), @cliente char(5), @typ char(3), @na1 char(2), @ref char(15), @str char(27)
select @arm = '51', @cliente = '00717', @typ = 'REF', @na1 = ' '
set @ref = (SELECT CustomerID FROM Northwind..Customers where CustomerID = 'CACTU')
select @str = @arm + @cliente + @typ + @na1 + @ref
select @str

The result is:
-------------------

5100717REF CACTU

----------------------

How can I use this script on a DTS in order to export all records from the Customers table keeping the format "5100717REF" and adding the CustomerID as variable to get the different rows on the table to the export file.

Example:

The expected result is a text file containing all table rows:
-------------------

5100717REF ALFKI
5100717REF ANATR
5100717REF ANTON
...
5100717REF CACTU
...
5100717REF WOLZA


Thanks for your help in advance!


Luis Nunes

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 10/31/2012 :  03:36:03  Show Profile  Visit webfred's Homepage  Reply with Quote
select @arm = '51', @cliente = '00717', @typ = 'REF', @na1 = ' ', @ref = CustomerID from Customers



Too old to Rock'n'Roll too young to die.
Go to Top of Page

luisfilipenunes
Starting Member

Portugal
4 Posts

Posted - 11/03/2012 :  15:59:11  Show Profile  Reply with Quote
Thanks for the hint, but when I try to go deeper on my task I have an issue regarding formatting this table record.
I need format the CustomerID to char(15) and the other table records to char (20)

This script export the information:

declare @arm char(2), @client char(5), @typ char(3), @na1 char(2), @ref char (50), @descpr char (35), @descabr char (15),
@zloc char(1), @corr char(2), @srt1 char (32)
select @ref = ContactName from Customers
select @arm = '51', @client = '00717', @typ = 'REF', @na1 = ' ', @zloc = ' ', @corr = 'LS'
select @arm, @client, CustomerID , @typ, @na1, @ref, @zloc, @corr, Address from [Customers]

but I need @ref to give me the correspondent exported line and is returning always the last one.



Luis Nunes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/03/2012 :  22:09:44  Show Profile  Reply with Quote
why do you need all these variables? wont you need just this?


select  '51','00717', CustomerID ,'REF', '',ContactName,'','LS', Address from [Customers]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

luisfilipenunes
Starting Member

Portugal
4 Posts

Posted - 11/04/2012 :  05:21:37  Show Profile  Reply with Quote
Thanks a lot! I was pretty sure I was not going the right way.

The only thing I need now to solve/investigate is how to change the format of the selected record, i.e., I need "CustomerID" to have 15 fixed positions (char15) and "ContactName" and "Address" with char(35) when exported.

Who can I do this?

My script now is simplified to this:


SELECT '51', '00717', 'REF', ' ', CustomerID,ContactName, Address, ' ', ' ', '00000', '00000', '00000', '000000000',
'00000000', '00000', '00000', '00000', '000000000', '00000000', '00000', '00000', '00000', '00000', '000000000',
'00000000', '00000', '0000000', 'N'
FROM Northwind..[Customers]



Luis Nunes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/04/2012 :  11:36:42  Show Profile  Reply with Quote

SELECT '51', '00717', 'REF', ' ', CAST(CustomerID AS char(15)) AS CustomerID,CAST(ContactName AS Char(35)) AS ContactName, CAST(Address AS Char(35)) AS Address, ' ', ' ', '00000', '00000', '00000', '000000000', 
'00000000', '00000', '00000', '00000', '000000000', '00000000', '00000', '00000', '00000', '00000', '000000000', 
'00000000', '00000', '0000000', 'N'
FROM Northwind..[Customers]



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

luisfilipenunes
Starting Member

Portugal
4 Posts

Posted - 11/06/2012 :  04:31:18  Show Profile  Reply with Quote
My question is solved and it was quite simple after all ;) but i'll never get there fast enough.

Thanks a lot for your hints!


Luis Nunes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2012 :  12:07:44  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000