Remove trailings spaces in a flat files from SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Remove trailings spaces in a flat files from SQL

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-15 : 15:46:08

I have an Osql batch flie that call store procedures that will pipe the data into a flat file(output file). Basically,my flat file on the line 07 and multiple line 18 and 19 have trailing spaces that causes the conversion from flat flie to EDI to fail. If I maually removed the trailing spaces the conversion will succeed. Does anyone know to fix my SQL statement to remove the trailing spaces. Below is osql, my SQL statement and my flat file results. Thanks for your help.

Osql
------
osql -Sservername -ddatabasename -Usa -P**** -h-1 -n -s* -w5000 -iC:\EdiTest.txt -oC:\DX-FX-VF.080

Select statement in the Store Procedure
---------------------------------------

select
'//STX12//850ABCDEFTest 850'+char(13)+
'0100NE'+a.PO_No+a.PODate+char(13)+
'05ST ZZ'+char(13)+
'06'+a.ShipTowhsecode+char(13)+
'07'+'GHIJKL'
from tableA a, tableB b
where a.id_num = b.Countrow
Union Select
'17'+AssgnID+'+'+OrderQty+'EA'+' '+' '+'MG'+char(13)+
'18'+ItemID+char(13)+
'19'+'F'
from tableC

Results in Flat file
--------------------

//STX12//850ABCDEFTest 850
0100NE000000000000000004074020031105
05ST ZZ
0655109998
07GHIJKL
171 +000000000000002 EA MG
18DUPH70247
19F
172 +000000000000004 EA MG
18DUPH69575
19F
173 +0000000000000013EA MG
18DUPH80064
19F
174 +0000000000000037EA MG
18DUPH7997A
19F
175 +0000000000000028EA MG
18DUPH8017A
19F
176 +0000000000000029EA MG
18TECSPINPAPER236
19F
177 +0000000000000048EA MG
18TECSPINPAPER242
19F
178 +000000000000004 EA MG
18DUPD11507939
19F

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 16:24:18

Use RTRIM to remove the trailing spaces.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-15 : 17:06:19

Below is the changes that I made in my store procedure and it works on remving the trailing spaces but created empty row between line 07 and 17 and line 17 is a loop and there is a row gap between the 17s' how can I remove the rows gap? Thanks.

DECLARE @string_to_trim varchar(6)
SET @string_to_trim =' '

select
'//STX12//850ABCDEFTest 850'+char(13)+
'0100NE'+a.PO_No+a.PODate+char(13)+
'05ST ZZ'+char(13)+
'06'+a.ShipTowhsecode+char(13)+
'07'+(a.CompanyName)+char(13)+RTRIM(@string_to_trim)
from tableA a, tableB b
where a.id_num = b.Countrow
Union Select
LTRIM(@string_to_trim)+'17'+AssgnID+'+'+RTRIM(OrderQty)+'EA'+' '+' '+'MG'+char(13)+
'18'+RTRIM(ItemID)+char(13)+RTRIM(@string_to_trim)
from tableC

Results from the flat file
---------------------------

//STX12//850ABCDEFTest 850
0100NE000000000000000004004820030910
05ST ZZ
0655109998
07GHIJKLM

171 +0000000000000010EA MG
18DUPH80064

172 +0000000000000011EA MG
18DUPH80013

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 17:18:22

I am not seeing the gap between those lines. You'll need to provide us with the CREATE TABLE statements for TableA, TableB, and TableC. Also, please provide INSERT INTO statements for sample data. Do not just copy the data into this window, you have to put them in the form of INSERT INTO statements in order for us to help.

Here is what I ran without your table:

DECLARE @string_to_trim varchar(6)
SET @string_to_trim =' '

select
'//STX12//850ABCDEFTest 850'+char(13)+
'0100NE'+char(13)+
'05ST ZZ'+char(13)+
'06'+char(13)+
'07'+char(13)+RTRIM(@string_to_trim)
Union Select
LTRIM(@string_to_trim)+'17'+'EA'+' '+' '+'MG'+char(13)+
'18'+char(13)+RTRIM(@string_to_trim)

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-15 : 18:06:58

IF OBJECT_ID('dbo.EDI') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.EDI
IF OBJECT_ID('dbo.EDI') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.EDI >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.EDI >>>'
END
go
CREATE Proc EDI
AS

set nocount on

Declare @date1 varchar(8)
,@time1 varchar(6)
,@countrow int

select @date1 = Convert(varchar(10), GetDate(), 112)
select @time1 = Convert(varchar(10), GetDate(), 108)

/*
insert A(POkey)
SELECT POkey
FROM tpopurchorder
WHERE NOT EXISTS
(SELECT POkey
FROM A
WHERE POkey = tpopurchorder.POkey)
and status ='1'
and Vendkey ='174'
and DfltDropship = 0
*/

Create Table A
(id_num int IDENTITY(1,1)
,POkey int null
,POlinekey int null
,PO_No char(22)null
,PODate char(8) null
,ShipTowhsekey int null
,ShipTowhsecode char(78)Null
,CurrentDate varchar(6)null
,CurrentTime varchar(4) null
,TranID char(30) null --int identity(3,1)
,Docno char(10) null
,CreateDate varchar(6) null
,CreateTime varchar(4) null
,CompanyName char(7) null
)

Create Table B
(Countrow varchar(7) Null
,Pokey int Null
,PO_No varchar(22)null
)

insert A(id_num,POkey,POlinekey,PO_No,PODate,ShipTowhsekey,ShipTowhsecode,CurrentDate,CurrentTime,TranID,Docno,CreateDate,CreateTime,CompanyName)
values(29,13173,'34550','0000000000000000040830','20031112','1','55109998','040115','1727','000000000000000000000401151727','0401151727','040115','1727','GHIJKL')

/*
update A set
PO_No = '000000000000'+a.tranno
,PODate = Convert(varchar(10), a.CreateDate, 112)
,polinekey = b.polinekey
,CurrentDate = Substring(@date1, 3,6)
,CurrentTime = Substring(@time1, 1,2) + Substring(@time1, 4,2)
,CreateDate = Substring(@date1, 3,6)
,CreateTime = Substring(@time1, 1,2) + Substring(@time1, 4,2)
,TranID = Substring(@date1, 3,6) + Substring(@time1, 1,2) + Substring(@time1, 4,2)
,Docno = Substring(@date1, 3,6) + Substring(@time1, 1,2) + Substring(@time1, 4,2)
,CompanyName ='GHIJKL'
from tpopurchorder a, tpopoline b
where A.pokey = a.pokey
and A.pokey = b.pokey

update A set ShipTowhsekey = c.ShipTowhsekey
from tpopolinedist c
where A.polinekey = c.polinekey
*/
update A set TranID = (select case when CurrentDate < 030000 then '00000000000000000000'
else '' end + TranID)

update A set ShipTowhsecode =
(select case when ShipTowhsekey = 1 then '55109998'
when ShipTowhsekey = 3 then '55109997'
when ShipTowhsekey = 2 then '55109999'
else ''
end)

--- the insert is to insert a 0 value when you drop and recreate the table in order to get the count
insert B (Countrow, Pokey, PO_No)
values(29 13173 '000040830')

-- the update is to 0 out and start from 1 again. when there already has a value in it
--update B set Countrow = '0'

select @countrow = Countrow from B

update B set CountRow = @countrow + 1

update B set
Pokey = A.pokey
,PO_No =A.PO_No
from A
where B.CountRow = A.id_num --pick up the one Row at a time each time the SP runs.

create table #C
(Pokey int null
,Polinekey int null
,AssgnID char(20) null
,QtyOrd int null
,OrderQty char(16) null
,ItemID char(48) null
)

insert #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
select '1', 13173, 11111,'DUPH77411',1.0000

insert #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
select '2', 13173, 11112,'DUPH55555',2.0000

insert #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
select '3', 13173, 11113,'DUPH56667',1.000000

update #C
set OrderQty = convert(char(16),QtyOrd)

update #C set OrderQty = (select case
when OrderQty < 10 then '000000000000000'+ substring(OrderQty, 1,1)
when OrderQty between 10 and 99 then '00000000000000' + substring(OrderQty, 1,2)
when OrderQty between 100 and 999 then '0000000000000' + substring(OrderQty, 1,3)
when OrderQty between 1000 and 9999 then '000000000000' + substring(OrderQty, 1,4)
else '' end )

drop table C

select * into C from #C

DECLARE @string_to_trim char(6)
SET @string_to_trim =' '

set nocount on

select
'//STX12//850ABCDEFTest 850'+char(13)+
'0100NE'+a.PO_No+a.PODate+char(13)+
'05ST ZZ'+char(13)+
'06'+a.ShipTowhsecode+char(13)+
'07'+(a.CompanyName)+char(13)+RTRIM(@string_to_trim)
from A a, B b
where a.id_num = b.Countrow
Union Select
LTRIM(@string_to_trim)+'17'+AssgnID+'+'+RTRIM(OrderQty)+'EA'+' '+' '+'MG'+char(13)+
'18'+RTRIM(ItemID)+char(13)+RTRIM(@string_to_trim)
from #C
go

----Store procedure end here---
My osql
osql -Sservername -ddatabasename -Usa -P**** -h-1 -n -s* -w5000 -iC:\EdiTest.txt -oC:\DX-FX-VF.080

Let me know this work for you. Thanks for your help.

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 18:12:36

The code that you posted doesn't compile. When I add two commas to the insert that is complaining, it compiles. But then when I run it, it errors. Please provide an example that does not require us to troubleshoot typos or other things like that. The example also needs to be coded so that we can run it over and over again. As of now, it can't because it is creating tables but not dropping them at the end.

Once we have a working example, someone here will be able to quickly fix your problem.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-15 : 20:48:35

What you see ("gaps") is exactly what you asked for, namely
"asked" by this: + char(13) + RTRIM(@string_to_trim)
You order new lines and you get them.

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-16 : 09:45:41

Tara,
I apologize for the hassle, The A & B tables are permanent tables as well as the insert statments for A & B are not supposed to be in the store procedures when you compile it. The #C tables are temp table and should be in the store procedes. Let me do this again for you.Below statements should be good. Thanks again.

--Create this A and B tables first

Create Table A
(id_num int IDENTITY(1,1)
,POkey int null
,POlinekey int null
,PO_No char(22)null
,PODate char(8) null
,ShipTowhsekey int null
,ShipTowhsecode char(78)Null
,CurrentDate varchar(6)null
,CurrentTime varchar(4) null
,TranID char(30) null --int identity(3,1)
,Docno char(10) null
,CreateDate varchar(6) null
,CreateTime varchar(4) null
,CompanyName char(7) null
)

Create Table B
(Countrow varchar(7) Null
,Pokey int Null
,PO_No varchar(22)null
)

insert A(POkey,POlinekey,PO_No,PODate,ShipTowhsekey,ShipTowhsecode,CurrentDate,CurrentTime,TranID,Docno,CreateDate,CreateTime,CompanyName)
values(13173,'34550','0000000000000000040830','20031112','1','55109998','040115','1727','000000000000000000000401151727','0401151727','040115','1727','GHIJKL')

--- the insert is to insert a 0 value when you drop and recreate the table in order to get the count
insert B (Countrow, Pokey, PO_No)
values(1, 13173, '000040830')

--- End creating A and B tables and insert statement

----Store procedure starts here---

IF OBJECT_ID('dbo.EDI') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.EDI
IF OBJECT_ID('dbo.EDI') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.EDI >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.EDI >>>'
END
go
CREATE Proc EDI
AS

set nocount on

Declare @date1 varchar(8)
,@time1 varchar(6)
,@countrow int

select @date1 = Convert(varchar(10), GetDate(), 112)
select @time1 = Convert(varchar(10), GetDate(), 108)

update A set ShipTowhsecode =
(select case when ShipTowhsekey = 1 then '55109998'
when ShipTowhsekey = 3 then '55109997'
when ShipTowhsekey = 2 then '55109999'
else ''
end)

update B set
Pokey = A.pokey
,PO_No =A.PO_No
from A
where B.CountRow = A.id_num --pick up the one Row at a time each time the SP runs.

create table #C
(Pokey int null
,Polinekey int null
,AssgnID char(20) null
,QtyOrd int null
,OrderQty char(16) null
,ItemID char(48) null
)

insert #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
select '1', 13173, 11111,'DUPH77411',1.0000

insert #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
select '2', 13173, 11112,'DUPH55555',2.0000

insert #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
select '3', 13173, 11113,'DUPH56667',1.000000

update #C
set OrderQty = convert(char(16),QtyOrd)

update #C set OrderQty = (select case
when OrderQty < 10 then '000000000000000'+ substring(OrderQty, 1,1)
when OrderQty between 10 and 99 then '00000000000000' + substring(OrderQty, 1,2)
when OrderQty between 100 and 999 then '0000000000000' + substring(OrderQty, 1,3)
when OrderQty between 1000 and 9999 then '000000000000' + substring(OrderQty, 1,4)
else '' end )

DECLARE @string_to_trim char(6)
SET @string_to_trim =' '

set nocount on

select
'//STX12//850ABCDEFTest 850'+char(13)+
'0100NE'+a.PO_No+a.PODate+char(13)+
'05ST ZZ'+char(13)+
'06'+a.ShipTowhsecode+char(13)+
'07'+(a.CompanyName)+char(13)+RTRIM(@string_to_trim)
from A a, B b
where a.id_num = b.Countrow
Union Select
LTRIM(@string_to_trim)+'17'+AssgnID+'+'+RTRIM(OrderQty)+'EA'+' '+' '+'MG'+char(13)+
'18'+RTRIM(ItemID)+char(13)+RTRIM(@string_to_trim)
from #C
go

----Store procedure end here---
My osql
osql -Sservername -ddatabasename -Usa -P**** -h-1 -n -s* -w5000 -iC:\EdiTest.txt -oC:\DX-FX-VF.080

Let me know this work for you. Thanks for your help again.

k

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-16 : 11:47:08

I don't understanf the Union to #c or the relationship of the rows to each other...

But might I propose a different train of thought...


if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EDI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDI]
GO

CREATE Proc EDI
AS

SET NOCOUNT ON
DECLARE @date1 varchar(8),@time1 varchar(6),@countrow int

SELECT	  @date1 = Convert(varchar(10), GetDate(), 112)
	, @time1 = Convert(varchar(10), GetDate(), 108)

UPDATE A 
   SET ShipTowhsecode = (SELECT CASE WHEN ShipTowhsekey = 1 THEN '55109998'
			 	     WHEN ShipTowhsekey = 3 THEN '55109997'
				     WHEN ShipTowhsekey = 2 THEN '55109999'
							    ELSE ''
				END)
UPDATE B 
   SET Pokey = A.pokey,PO_No = A.PO_No 
  FROM A 
 WHERE B.CountRow = A.id_num --pick up the one Row at a time each time the SP runs.

CREATE TABLE #C (
		  Pokey     int null
		, Polinekey int null
		, AssgnID   char(20) null
		, QtyOrd    int null
		, OrderQty  char(16) null 
		, ItemID    char(48) null 
)


INSERT INTO #C (AssgnID,POkey,Polinekey,ItemID,QtyOrd)
SELECT '1', 13173, 11111,'DUPH77411',1.0000 UNION ALL
SELECT '2', 13173, 11112,'DUPH55555',2.0000 UNION ALL
SELECT '3', 13173, 11113,'DUPH56667',1.000000

UPDATE #C
   SET OrderQty = RIGHT(REPLICATE('0',16) + CONVERT(char(16),QtyOrd),16)


DECLARE @string_to_trim char(6)
SET @string_to_trim =' ' 

CREATE TABLE #d (id_num int, RowOrder int, ResultSet varchar(8000))

INSERT INTO #d (id_Num, RowOrder, ResultSet)
SELECT a.id_num, 1, '//STX12//850ABCDEFTest 850' FROM A INNER JOIN B ON a.id_num = b.Countrow UNION ALL
SELECT a.id_num, 2, '0100NE'+a.PO_No+a.PODate 	 FROM A INNER JOIN B ON a.id_num = b.Countrow UNION ALL
SELECT a.id_num, 3, '05ST ZZ' 			 FROM A INNER JOIN B ON a.id_num = b.Countrow UNION ALL
SELECT a.id_num, 4, '06'+a.ShipTowhsecode 	 FROM A INNER JOIN B ON a.id_num = b.Countrow UNION ALL
SELECT a.id_num, 5, '07'+(a.CompanyName) 	 FROM A INNER JOIN B ON a.id_num = b.Countrow


/* I don't get this part  the local varuiable is set to a space...
UNION ALL 
SELECT 
LTRIM(@string_to_trim)+'17'+AssgnID+'+'+RTRIM(OrderQty)+'EA'+' '+' '+'MG'+char(13)+
'18'+RTRIM(ItemID)+char(13)
+RTRIM(@string_to_trim) 
FROM #C
*/

SELECT ResultSet FROM #d ORDER BY id_Num, RowOrder

DROP TABLE #c
DROP TABLE #d
GO


EXEC EDI

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 12:54:13

When I ran your stored procedure in Query Analyzer, I am not seeing a gap between the result sets. Do you see the gaps in Query Analyzer (forget about osql for now)?

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-16 : 14:14:25

Tara,
IF you use Query analyzer you probably won't see the trailing spaces on row 18 but if you use a batch file with osql in it and pipe out to a output files you will see the trailing spaces on line 18. Use the syntax below on the DOS prompt and you will see the trailing spaces. Another thing on using Query analyzer the result in last row of row 18 if you place the cursor on the last character and use your --> arrow key to move 2 times you can see that the cursor stop at 2 rows down. Which i need the cursor to stop exactly at the last character of the last row of row 18. any idea. Thanks.

osql -Sservername -ddatabasename -Usa -P**** -h-1 -n -s* -w200 -iC:\EdiTest.txt -oC:\DX-FX-VF.080

Brett,
the Union to A and #C tables are 1 to many keys relationships. #C tables is also a loop which contain mutiple line items. A table is the PO nos the primary table that contain 1 PO# and #C supposingly to be the multple line items under 1 PO# from A table. The reason I did not join the tables in the select statement is because it will return duplicate PO nos. Which I do not need it.

k

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-16 : 15:48:09

>any ideas....

quote:

What you see ("gaps") is exactly what you asked for, namely
"asked" by this: + char(13) + RTRIM(@string_to_trim)
You order new lines and you get them.

Second thing.
Careless +char(13)+ instead of +char(13)+char(10)+
is equal to blank sa password.

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-16 : 16:22:55

Stoad,
I don't understand what you meant. The +char(13)+char(10)+ what is there to do with the sa password. All I need to do is How to remove the trailing spaces and returns in my output files from a SQL statement with a Union operator.

k

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-16 : 16:35:53

quote:
Originally posted by fredong

Stoad,
I don't understand what you meant. The +char(13)+char(10)+ what is there to do with the sa password. All I need to do is How to remove the trailing spaces and returns in my output files from a SQL statement with a Union operator.

k

That's why he's the freaky YAK linguist...

I have no idea...but it's not because there isn't a gem hidden in there...

Brett

8-)

EDIT: It might be a SQL injection reference

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-16 : 16:42:49

Hmm.. it's just a metaphor..

Note: I did not even mention such rubbish as indentations.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-17 : 00:50:15

ah.. I thought you already got rid of those trailing spaces..

How about just not use UNION in your final select?

Break it into two sequential selects and spaces will go away.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 12:33:54

Stoad, he uses the UNION because he wants both result sets to appear right after each other in the output file. Without the UNION, there will be one line in between them. This question started from another thread not too long ago.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-19 : 12:48:12

Tara,
I have use isql/w with the configuration file. The only problem I got now is the cursor supposed did stop at the last row of the last characters but when i use the right arrow key the cursor will move to 2 returns at the first positions then it will stop. anyone how to remove the 2 returns?Thanks.

--Below is the result test and I need th cusor to stop exactly after the last character of 18TEKTDSDUPH91260 -- if you try to place your cursor after the 0 and move your arrow key it will stop at the 2nd returns.

Results in Text
---------------
//STX12//850ABCDEFGTest 850
0100NE000000000000000004075320031105
05ST ZZ
0655109998
07HIJKLMN
171 +000000000000002EA MG
18DUPH7997A-TEKPROOF
172 +000000000000003EA MG
18DUPH80005-TEKPROOF
173 +0000000000000001EA MG
18DUPH80064-TEKPROOF
174 +0000000000000001EA MG
18DUPH80099-TEKPROOF
175 +0000000000000011EA MG
18TEKTDSDUPH91260

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 12:52:28

Look into VBScript to fix your file format after osql completes. If you don't know VBScript:

http://www.devguru.com/Technologies/vbscript/quickref/vbscript_intro.html

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources