Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-19
6
Medium Priority
?
350 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 2000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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