August 9, 2011 at 11:36 am
I have to write a query that will output a ---fixed lenght -- sequential text file? for example I have 100 records (100 rows) and the way client needs it is all those records in a single row.
record1|record2|record3|til end ...
Any help would be appreciated..
Thank you..
August 9, 2011 at 12:37 pm
Hopefully this will give you some ideas.
declare @Tmp table
(test1 varchar(5))
insert into @Tmp
select '1234'
union
select '4321'
union
select 'abcd'
union
select 'dcba'
Declare @teststrng varchar(100)
set @teststrng=''
select @teststrng=@teststrng + test1 + '|' from @Tmp
select @teststrng
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 9, 2011 at 2:03 pm
maynor_ramirez (8/9/2011)
I have to write a query that will output a ---fixed lenght -- sequential text file? for example I have 100 records (100 rows) and the way client needs it is all those records in a single row.record1|record2|record3|til end ...
Any help would be appreciated..
Thank you..
I have a couple questions.
Did the client give you a layout that includes how they want the fields justified?
When the field within the row/record line is not the total length of the field do you need to fill the unused characters with spaces, leading or ending 0's if numeric?
Fixed length means that each row within the set you are sending must be so many characters.
field1 = alpha-Numeric length 5
field2 = numeric length 10
field3 = character length 3
field4 = character length 8
field5 = character length 1
results could be different depending on the client see below
abcd12|0000001009|ABC|_______r|0
abcd12|______1009|ABC|r_______|0
abcd12|1009______|ABC|_______r|0
Please look at the specs and post what they are. I had to use _ 's in my example since the spaces were removed when I did a preview of the post.
August 9, 2011 at 2:53 pm
Yes,
The total size is 46 characters, divided by
1-17 acct # right justified,
18-27 left justified, 0 filled,
etc..etc,to 46 , I have managed that piece -
SELECT
LEFT(CAST(CAST('123456789' as int) as varchar(9))+ REPLICATE(' ',17),17)+
RIGHT(REPLICATE('0',10) + CAST(CAST('123456' as int) as varchar(10)),10)+'0'+'0'+
CAST(CAST('070511' as int) as varchar(6))+
RIGHT(REPLICATE('0',12) + CAST(CAST('27807' as int) as varchar(12)),12)+'0'
FROM db..table1
but I do not know how cannot start the next record in the same row, which in this case it will have to start on position 47. the record count is 2,893 records.
Thanks
August 9, 2011 at 3:03 pm
write the records as a string and add them to a variable as I did in my example this will cause the ouput of the variable to output one long line.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 9, 2011 at 3:06 pm
Somethings along those ling could do it too... just to have 2 options.
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
August 9, 2011 at 3:15 pm
When I used to do extracts for third party clients that needed this format we usually used a char(13) return to start the next record in the txt file.
August 9, 2011 at 3:51 pm
Would you please provide an example - for the variable?
Thanks
August 9, 2011 at 4:11 pm
using the select statement you listed and changing it a bit. try this. Since I can only see an example of one record and not the table definition makes this a bit difficult.
I ran this to return as text in the query window. you probably can remove the +char(13) and get the same results I did replace your ' ' with an underscore so the spaces would not be trimmed here. I also added the | between the fields
/*------------------------
SELECT
LEFT(CAST(CAST('123456789' as int) as varchar(17))+ REPLICATE('_',17),17)+ '|' +
RIGHT(REPLICATE('0',10) + CAST(CAST('123456' as int) as varchar(10)),10)+ '|' + '0' + '|' +'0'+ '|' +
CAST(CAST('070511' as int) as varchar(6))+ RIGHT(REPLICATE('0',12) + '|' +
CAST(CAST('27807' as int) as varchar(12)),12) + '|' +'0' + char(13)
union
SELECT
LEFT(CAST(CAST('3456789' as int) as varchar(17))+ REPLICATE('_',17),17)+ '|' +
RIGHT(REPLICATE('0',10) + CAST(CAST('123456' as int) as varchar(10)),10)+ '|' + '0' + '|' +'0'+ '|' +
CAST(CAST('070511' as int) as varchar(6))+ RIGHT(REPLICATE('0',12) + '|' +
CAST(CAST('27807' as int) as varchar(12)),12) + '|' +'0' + char(13)
union
SELECT
LEFT(CAST(CAST('1234569' as int) as varchar(17))+ REPLICATE('_',17),17)+ '|' +
RIGHT(REPLICATE('0',10) + CAST(CAST('123456' as int) as varchar(10)),10)+ '|' + '0' + '|' +'0'+ '|' +
CAST(CAST('070511' as int) as varchar(6))+ RIGHT(REPLICATE('0',12) + '|' +
CAST(CAST('27807' as int) as varchar(12)),12) + '|' +'0' + char(13)
------------------------*/
------------------------------------------------------
123456789________|0000123456|0|0|70511000000|27807|0
1234569__________|0000123456|0|0|70511000000|27807|0
3456789__________|0000123456|0|0|70511000000|27807|0
(3 row(s) affected)
August 10, 2011 at 9:11 am
I've read from your request that you want one long string returned for the entire file: i.e. each row is stored in 47 characters with no row separator in between. Apart from the fact that this may result in a very long line with which most editors will have problems, MS SQL Management Studio has issues with columns with long texts in it. Your query will return a single row with a single column in it. By default SSMS truncates each column at 256 characters and the longest string it can return per column is 64K characters. In other words, you will probably not be able to show the entire length of your column in SSMS. If you have some other program that will execute the statement and write the result to a file, you might be able to do it though.
Anyway here is a statement that will generate a fixed length string per row, then concatenate each row into one long string, with no row separators.
Please note that if any of the columns can contain null's, you will have to use f.e. isnull() to provide an alternative value for that column if it contains null, or the entire row with a null value in one of it's columns will disappear from your output.
declare @table1 table (
acct int not null,
col1 varchar(12) not null,
col2 datetime null,
primary key (acct)
);
insert @table1(acct, col1, col2)
select 1, 'nr 1', {d '2011-01-01'}
union all select 675876, 'nr 2', null
union all select 123456789, '123456', getdate();
declare @result varchar(max);
select @result = (
SELECT right(replicate(' ', 17) + convert(varchar(17), t1.acct), 17)
+ '|' + left(convert(varchar(9), t1.col1) + replicate('0',9), 9)
+ '|' + left(isnull(convert(varchar(10), t1.col2, 121), '') + replicate(' ', 10), 10) as [text()]
-- + up to 47 characters
from @Table1 t1
for xml path(''), type
).value('.', 'varchar(max)');
select datalength(@result), @result;
August 10, 2011 at 9:38 am
I bet that you have misuderstood the client requirements.
Or misrepresented them in your post:
Most likely they want standard fixed-length extract, which means that every field will be of the fixed length:
FIELD1FIELD2FIELD3
FIELD1FIELD2FIELD3
where each field is always of the same size in every row, hence no field separator in required.
However if your client really wants something like this:
FIELD1FIELD2FIELD3|FIELD1FIELD2FIELD3
Then I would still advice output results into file in the standard format and then replace all NewLine chars with whatever you want eg. bar. You can do it using dos command, vb or Java script or using PowerShell.
Sorry mate, but concatinating all of extracted data into one string in T-SQL is plainly stupid.:w00t:
August 10, 2011 at 12:18 pm
Thank you guys...
August 10, 2011 at 3:29 pm
No need to fuss with all that. Just do a BCP out to the file with no row terminator. 😉
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply