Solved

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

Posted on 2012-03-19
6
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

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
Technology Partners: 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!

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

626 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