Solved

# Data to text file with each record containing different fields

Posted on 2005-05-15
213 Views
I need to export data to a text file.  The problem is each record needs to write out four lines in the text file.  Here's an example of what a couple records need to transform to on output.

field1,field2,field3,field4,field5
field6,field7,field8
field9,field10,field11,field12,field13
field1,field2,field3,field4,field5
field6,field7,field8
field9,field10,field11,field12,field13

I'm open to using DTS as well.

0
Question by:bigwahoo2

LVL 42

Accepted Solution

0

LVL 75

Assisted Solution

Assuming that field5 and field8 are character, than write your query as follows:

Select field1,field2,field3,field4,field5 + CHAR(13) + CHAR(10),
field6,field7,field8 + CHAR(13) + CHAR(10),
field9,field10,field11,field12,field13

If they are not, you may be able to do somehting like this:
Select field1,field2,field3,field4,field5, CHAR(13) + CHAR(10),
field6,field7,field8, CHAR(13) + CHAR(10),
field9,field10,field11,field12,field13
0

Author Comment

You've both gotten me very close, however I'm getting ocassional spaces where they are not wanted and I've tried the rtrim/ltrim to no avail.  Any ideas how to get rid of these.  Here's a sample of the output in the format of

field1,field2
field3

Tech ,Tech
,Tech
RCode,Res Code
,Resolution Code
DsChn,Distr Chan
,Distribution Channel
0

LVL 75

Expert Comment

0

Author Comment

It appears when a value is not the full length of the field it puts spaces on the next line.

select hrsdesc sdesc, ltrim(rtrim(hrmdesc)) + CHAR(13) + CHAR(10) as mdesc, hrldesc ldesc
from stcshr10
0

LVL 75

Expert Comment

bigwahoo2,

You have been here long enough to know better.  Please take the time to re-read the EE Guidelines regarding grading standards at:
What's the right grade to give?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0

Author Comment

acperkins,

You are correct, I had wanted to give a 'B'.

TheLearnedOne, can you make this edit?
0

LVL 75

Expert Comment

>>You are correct, I had wanted to give a 'B'.<<
See the link I posted.  Again:
Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0

## Featured Post

Performance is the key factor for any successful data integration project, knowing the type of transformation that youâ€™re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternatâ€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.