Solved

Why is there a Comma being input in the TXT output file?

Posted on 2012-03-19
6
343 Views
Last Modified: 2012-03-19
I've created a DTSx Package that runs the following Query:

SELECT '00000123456789002'+RIGHT ('00000000' + LTRIM(CAST(CHECKNO AS VARCHAR(20))), 15)
+ CONVERT(varchar(10),CHECKDT, 112)+ RIGHT ('0000000' + REPLACE (CAST(checknet AS VARCHAR(20)), '.',  ''), 10)+CASE(ckstatus)
WHEN 'O' THEN '' WHEN 'V' THEN 'C' END + '' as Val
FROM dbo.CHK

WHERE checkdate BETWEEN '03/14/12' and '03/14/12' and bankid='MYBANK'and ISNUMERIC(checkno)=1

The Results are put into a Txt File

At the end of the results it's adding a ,
000001234567890020000000020201203140000058878C,

Can anyone tell me why this is happening and how I can fix it?
0
Comment
Question by:Rmumpower00
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37739690
Perhaps it is a CSV text that you selected in the SSIS package.
You could manually try to export something like :

exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; SELECT ''00000123456789002''+RIGHT (''00000000'' + LTRIM(CAST(CHECKNO AS VARCHAR(20))), 15) + CONVERT(varchar(10),CHECKDT, 112)+ RIGHT (''0000000'' + REPLACE (CAST(checknet AS VARCHAR(20)), ''.'',  ''''), 10)+CASE(ckstatus) WHEN ''O'' THEN '''' WHEN ''V'' THEN ''C'' END + '''' as Val
FROM dbo.CHK
WHERE checkdate BETWEEN ''03/14/12'' and ''03/14/12'' and bankid=''MYBANK''and ISNUMERIC(checkno)=1;" -W -o"c:\output.txt"'
0
 

Author Comment

by:Rmumpower00
ID: 37739740
So if I ran that query it would export it to a Text?
0
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 37739758
If it's showing up only at the end of a row, then it could be the Row Delimiter. Edit your flat file connection, then select Columns. You should see a Row Delimiter and a Column Delimiter. If it's using Comma {,} then change it to {CR}{LF}. That will use the 'carriage return' or 'line feed' for the line break, instead of a comma.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Rmumpower00
ID: 37739760
I'll do that now Buttercup, I think you're dead on!
0
 

Author Closing Comment

by:Rmumpower00
ID: 37739774
That was it, Thanks a ton!
0
 
LVL 11

Expert Comment

by:Simone B
ID: 37739777
Excellent! Happy to help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

808 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