Help with formatting data

Question

Post reply

Help with formatting data

Stephanus-466433

SSC-Addicted

Points: 427
More actions
July 18, 2007 at 1:09 pm

#69362

Hi there,
I am a very new user to DTS and vb script, that being said I was given the task to supply our coupon book printing company with the data, formatted in the correct format. Every field has to be of a specific length and the output must be a txt file and each record 328 caracters long with a CR delimmeter and 1 record per block.This will most probably involve some visual basic scripting.
The data that I am extracting is supposed to be matched up to certain
specifications. For example if I query the name field the output
must be 9 characters in length and even if the name is not that
long it has to be padded with spaces. For numberical fields it
has to be padded with zeros to the desired length.
Any help will be greatly appreciated
thanks
Stephanus.
PS.
I am using SQL 2000 with enterprise manager

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

to pad spaces convert to char

select

convert(char(9),fieldname)

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 2

Hi,

Tnx for reply, If I understand this correctly then after my select statement i then insert the convert statement, so if I have multiple fields I will instert multiple convert statements for example;

Select as.fname, as.lname, as.address

convert(char(9),fname)

convert(char(9),lname)

convert(char(9),address)

The convert statement get inserted before any From or Join statements?

Thanks

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

No you are actually selecting the converted fields

Select convert(char(9),as.fname)

convert(char(9),as.lname)

convert(char(9),as.address)

from as

where yada yada yada.

I am looking up the 0 padding which is the replicate function and I'll have it in a minute.

What I do is create a view using the query above and then export the view using Data transformation to a fixed length text file.

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 4

duh!!

It should be like

Select

convert(char(9),as.fname), convert(char(9),as.lname), convert(char(9),as.address)

FROM

Is this the correct syntax?

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 5

Ah ok, then the fix length output in DTS will preserve the field lengths. I will give this a try and see how it works out.

Thanks

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

This will pad 0's on the front of an int field where @myint is the int field and 9 is the length you want

select LEFT (RIGHT (REPLICATE('0', 9) + convert(varchar(9),@myint), 9), 9)

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 7

Ok, I ran the query and exported it to a fixed width text file. Now, when i view the text file with notepad++ this is what I see;

Joe.........Smith.........12.somewhere.street...............crlf

query:

SELECT convert(char(9),sa.fname), convert(char(9),sa.lname), convert(char(15),sa.address)

FROM sa

Problem:

The fname field should be a total of 9 character, not 9 chars after the name. the same goes for the other 2

Thanks

mrpolecat SSCertifiable Points: 7014 More actions · Answer 8

when you set up the export did you specify the correct field lengths in the destination text file?

If you run your select statement in query analyzer is the spacing correct?

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 9

Hmm, nope I did not specify any lengths in the destination text file, I will have to investigate that and see how I am going to go about doing that.

Thanks for the help

mrpolecat SSCertifiable Points: 7014 More actions · Answer 10

WHen you set up the transformation and clicked on the destination tab it should have popped a window that showed the fields that would be created and their lengths.

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 11

Well thank you very much... it could not been easier...this will same me tons of time, before i would massage the data in excel...ouch! Just one more question. Say we have two companies one is abr. STL and the other one DNR in data base, but when we do the coupon books these abbreviations need to be replaced with codes 014 and 415. Is there any way to do this at the time when i query the data and write the codes to the txt file intead of the abbr.

thanks

mrpolecat SSCertifiable Points: 7014 More actions · Answer 12

couple of ways. If it is really just 2 companies then you could use a case statement

select case

when abr = 'STL' then '014'

when abr = 'DNR' then '415'

else '000'

end

this is the quick and dirty way

if there are a lot of companies then you should create a table to hold the abbreviation and code and join it to your table. This is the right way thinking ahead to a growing product.

Stephanus-466433 SSC-Addicted Points: 427 More actions · Answer 13

So i pop the select case statement directly into my query?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 14

quick and dirty huh?

yes although to keep your fixed length format you will want to wrap it in a convert(char(3),case...)