?
Solved

Data to text file with each record containing different fields

Posted on 2005-05-15
9
Medium Priority
?
218 Views
Last Modified: 2010-03-19
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
Comment
Question by:bigwahoo2
  • 4
  • 3
8 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z earned 600 total points
ID: 14007379
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 14007384
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

by:bigwahoo2
ID: 14007929
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14010371
Post your query.
0
 

Author Comment

by:bigwahoo2
ID: 14010551

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

by:Anthony Perkins
ID: 14246497
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

by:bigwahoo2
ID: 14285094
acperkins,

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

TheLearnedOne, can you make this edit?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14286311
>>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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question