January 4, 2007 at 3:10 pm
I have a unusual export problem, where one system that we export to requires 2 lines per row (fixed field), and the starting positions for each field in each line are different: for example, the first of a pair might have the 3rd field start at col 34, whereas the second of the pair might have it's 3rd field at col 41.
I've created separate views for each of the row pairs, but now I have to join them for a DTS export. I'm currently trying to create a 3rd view to join the line pairs with a large text field wherein I add all the fields. It works well, EXCEPT if there are consecutive blanks, as in
case when ssnum is null then space(9) else ssnum end+
case when address is null then space(30) else address end
etc.
If there are no values for consecutive fields being joined, the number of spaces gets compressed. I've tried replicate(' ',9) but I get the same results. It only works if I put in ' ', which to me is nuts.
Is there perhaps a better way of doing this?
Another problem is that I need to sort to get the line pairs together, and you can't use ORDER BY in a view.
January 5, 2007 at 3:19 am
Can you post some sample rows of data and table structures that demonstrates the problem you're having?
/Kenneth
January 5, 2007 at 7:11 am
January 5, 2007 at 7:44 am
Just a wild shot... any chance that the data is VARCHAR (or is converted to it at some moment during the export)? That would explain missing trailing blanks.
You can use ORDER BY in a view if you specify TOP in select clause. Since you probably want to export all data, you'd have to write SELECT TOP 100 PERCENT... however, performance of such views is often very poor and I wouldn't recommend it. There should be some other way how to do this... not using two views.
Maybe simply inserting CR+LF at the right position would do the job? I'm not familiar enough with DTS to tell you how to do it, but I feel there has to be some better solution.
January 5, 2007 at 11:06 am
Thank you all for your replies, and sorry it's taken me a while to get back to you (work, finding the right ex. with sensitive info, which I X'd out.)
Here is the code and the results:
correct spaced:
doctype+rtrim(Docname)+space(25-len(rtrim(docname)))+' '+
case when phone is null then ' ' else phone+space(18-len(phone)) end +
case when deanumber is null then ' ' else deanumber+space(9-len(deanumber)) end +
case when statelicense is null then ' ' else statelicense+space(15-len(statelicense)) end +
case when street is null then ' ' else street+space(30-len(street)) end +
01XXXX,Alasdair K 617XXXXXXX AC94XXXXXXXXX3 Massachusetts
01XXX,Brian D 617XXXXXXX Massachusetts
------------------------------------------------
Improperly spaced:
doctype+rtrim(Docname)+space(25-len(rtrim(docname)))+space(3)+
case when phone is null then space(18) else phone+space(18-len(phone)) end +
case when deanumber is null then space(9) else deanumber+space(9-len(deanumber)) end +
case when statelicense is null then space(15) else statelicense+space(15-len(statelicense)) end +
case when street is null then space(30) else street+space(30-len(street)) end +
01XXXX,Alasdair K 617XXXXXXX AC94XXXXXXXXX3 Massachusetts
01XXX,Brian D 617XXXXXXX Massachusetts
This wraps unforunately, but you can see that between the phone number and "Massachusetts" there are 32 cols in both rows of the above example, but the bottom example, there are 32 cols in the first line, but only 25 in the 2nd--which doesn't have the 2 fields that the first line of each example.
As I mentioned I've tried replicate(' ',#) with the same results. Is there a better way to do this?
Thanks, Arthur
January 6, 2007 at 6:59 am
Hi,
unfortunately you didn't supply table definition and data, just result, so I had to do some guessing.. but I think I got it.
--Test data
CREATE TABLE #export(doctype char(20),docname char(20), phone char(18), deanumber char(9), statelicense char(15), street char(30))
INSERT INTO #export (doctype,docname,phone,deanumber,statelicense,street)
VALUES ('01XXXX','Alasdair K','617XXXXXXX','','AC94XXXXXXXXX3 ','Massachusetts')
INSERT INTO #export (doctype,docname,phone,deanumber,statelicense,street)
VALUES ('01XXX','Brian D','617XXXXXXX',NULL,NULL,'Massachusetts')
INSERT INTO #export (doctype,docname,phone,deanumber,statelicense,street)
VALUES ('01QQQ','Brian D','617XXXXXXX','','','Massachusetts')
/*I have added one more row to demonstrate another possible problem if there is empty string instead of NULL*/
--Your original query
select
doctype+rtrim(Docname)+space(25-len(rtrim(docname)))+space(3)+
case when phone is null then space(18) else phone+space(18-len(phone)) end +
case when deanumber is null then space(9) else deanumber+space(9-len(deanumber)) end +
case when statelicense is null then space(15) else statelicense+space(15-len(statelicense)) end +
case when street is null then space(30) else street+space(30-len(street)) end
from #export
/*this query fails to produce correct result on both "Brian" rows*/
--New query using different approach
SELECT doctype+
LEFT(ISNULL(docname,'')+space(25),25)+space(3)+
LEFT(ISNULL(phone,'')+space(18),18) +
LEFT(ISNULL(deanumber,'')+space(9),9)+
LEFT(ISNULL(statelicense,'')+space(15),15)+
LEFT(ISNULL(street,'')+space(30),30)
FROM #export
/*as far as I can tell, the result is correct for all 3 rows - you should be able to test it directly on your data*/
--cleanup
DROP TABLE #export
As you see, I'm adding always the maximum number of spaces at the end, and then just take the appropriate LEFT part of the string - so any superfluous spaces from the end are omitted. You may need to adjust the number of spaces or characters taken with LEFT, I may have overlooked something in this part.
ISNULL replaces NULLs with empty string, which makes sure that the result will be the same, no matter whether there is NULL or empty string in the column.
If this didn't help, let us know and post CREATE TABLE with INSERT statement for a few rows, so that we can work with correct data.
PS. And what about the rows being broken to 2 lines in export? Do you still need some help with it? If yes, please supply some example as well.
January 6, 2007 at 7:25 am
Just tested something... CRLF should be enough if you are exporting into a text file with fixed length:
Don't do any views dividing the lines into "first" and "second". Simply put this at the place in your query, where you want to end first line
+CHAR(13)+CHAR(10)+
As an example, if you'd want to end the first line after "deanumber", you can write the export :
SELECT doctype+
LEFT(ISNULL(docname,'')+space(25),25)+space(3)+
LEFT(ISNULL(phone,'')+space(18),18) +
LEFT(ISNULL(deanumber,'')+space(9),9)+CHAR(13)+CHAR(10)+
LEFT(ISNULL(statelicense,'')+space(15),15)+
LEFT(ISNULL(street,'')+space(30),30) as myexp
FROM #export
It works when you use the export wizard in EM and specify that you want to export using a query, and then put such SQL into the query. Destination must be "Fixed field" text file with CRLF as row delimiter. Also an alias ("myexp") had to be specified in order to work. I'm sure you can do the same in DTS in some way.
January 8, 2007 at 9:33 am
Thank you all again!
I had come to the same solution as you Vladan in your last suggestion, which works well with the view.
To summarize, I needed two rows per individual record, which I accomplished by having 1 view per line, and then joining them for a summary view. There are 3 fields in the summary view: one for the data to be exported, an index, and a lastupdate field. This way I can sort on the index, and also select records to be exported based on the date. So the result is something like
01XXXX,Alasdair K 617XXXXXXX AC94XXXXXXXXX3
0200000XXXXXXXXXXXXXXXXXXXXXX311
01XXX,Brian D 617XXXXXXX
02000001XXXXXXXXXXXXXXXXXXXXX514
Since I only export the one field, this effectively creates fixed format, which is what I wanted.
The issue now is to put this into a DTS. And it appears to work well.
Thank you!
Arthur
January 9, 2007 at 1:23 am
I still don't understand why you need to create several views (you can export 2 lines of text from 1 row of a view), but if you are happy with this solution, I guess then it's all. Good luck!
January 9, 2007 at 6:32 am
Frankly I wouldn't know how to export 2 lines from 1 row of a view. If you're willing, please let me know.
I've had other working solutions. I'm just looking for the best one.
Thanks,
Arthur
January 9, 2007 at 7:47 am
Since you are already concatenating, you could do something like this...
create view myView
as
SELECT col1 + char(13) + char(10) + col2
FROM myTable
GO
/Kenneth
January 9, 2007 at 7:51 am
Well, I already posted that above, Arthur... If the only column of a view is defined like this:
SELECT doctype+
LEFT(ISNULL(docname,'')+space(25),25)+space(3)+
LEFT(ISNULL(phone,'')+space(18),18) +
LEFT(ISNULL(deanumber,'')+space(9),9)+CHAR(13)+CHAR(10)+
LEFT(ISNULL(statelicense,'')+space(15),15)+
LEFT(ISNULL(street,'')+space(30),30) as myexp
FROM #export
then export into fixed length text file without text delimiters (unquoted) will result in two lines. Here anouther example:
CREATE TABLE tst_export (col1 varchar(500))
INSERT INTO tst_export (col1)
SELECT 'This is the first row, line 1 of the text.'+CHAR(13)+CHAR(10)+'And this is the first row, line 2.'
INSERT INTO tst_export (col1)
SELECT 'This is the second row, line 1 of the text.'+CHAR(13)+CHAR(10)+'And this is the second row, line 2.'
Set Results in Grid in QA , run
SELECT * FROM tst_export
- each row is now shown in one line.
Now, if you set Results in Text in QA and run the same, you will see how the text wraps and creates 2 lines from each row... and precisely the same way it works with DTS (I have even created a package to make sure, since I use DTS rarely :-))
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply