How to generate ASCII Fixed Length file
-
I need to generate ASCII Fixed Length file
and be able to specify the position of the columns.
For example, CUSTUMER_NAME should start at position 12
and end at position 18. How do I do that?
DTS generates fixed-length file but the positions are chosen randomly.
basically DTS does a very poor job generating fixed-length file.
Fields are incorrectly concatenated and it's basically a garbage ouput.
Thanks,
Robert
-
Yes it can. Using the export wizard just select destination type of text file, when you get to specifying the details, fixed width will be one of the choices.
-
Ed,
DTS generates fixed length file but ....
you dont' have any control
over which position the columns are in. For example,
you need your CUSTOMER_NUMBER at position (12-18).
It means column should start at position 12 and end at position 14.
DTS can not do that.
-
You can generate a format file that has the column and its length of the text file. Then you use bulk insert to copy the data from the table to the text file and indicate using the format file.
-
Hi Loner,
You seem to know more about this stuff.
How exactly I generate a format file?
Do you mean format file for BCP?
It looks like this:
8.0
4
1 SQLCHAR 0 7 "," 1 BENEFICIARY-NUMBER
2 SQLCHAR 0 100 "," 2 ADDRESS-ID-NUMBER
3 SQLCHAR 0 100 "," 3 RESIDENCE-CODE
4 SQLCHAR 0 100 "\r\n" 4 STREET-LINE1
But this format file does not allow you to specify positions?
Only 7 columns are provided:
field_number
data_type
prefix
data_length
delimiter
SQL_column_number
SQL_column_name
And it doesn't look like it will allow to generate fixed-length file.
It's always "delimited".
Here is what I was able to produce with BCP.
Comma delimited
--------------------
bcp CSTrust..gh_BENE_NUM out c:\bcp\gh_BENE_NUM.txt -c -t"," -Urobert -P19718 -SCSSAN\TNV00PCDSQL
generated ASCII Comma delimited file
000010021,2429,CAN,13 Ferrutti Street,Floor 2,,,MAPLE,ON,L6A 0H5,CAN
000010212,2431,USA,52 Avenue Road,Floor 11,,,NEW YORK,NY,12985,USA
000017812,2454,,,,,,,,,
000196246,2455,,,,,,,,,
TAB delimited
---------------
bcp CSTrust..gh_BENE_NUM out c:\bcp\gh_BENE_NUM.txt -c -Urobert_opeshansky -P197189632807 -SCSSAN\TNV00PCDSQL
generated ASCII TAB delimited file
000010021 2429 CAN 13 Ferrutti Street Floor 2 MAPLE ON L6A 0H5 CAN
000010212 2431 USA 52 Avenue Road Floor 11 NEW YORK NY 12985 USA
000017812 2454
Obviously BCP is not goig to help to produce fixed-length with controlled position file ...is it?
Thanks,
Robert
-
The best way I've found to control the exact layout is to write a select that formats each field as a fixed length character field using string manipulation functions and concatenation as needed . Once you have all the individual fields lined up, change the "," in the select statement to +. You end up with a single field which is the concatenation of all the individual fields. Export that single field using the export fixed and you are done.
If the specs change, (when don't they) you have each field identified and manageable.
-
You mean something like:
SELECT Field1+'.......'+CUSTOMER_NAME+'.......'+
But how do I force SQL to generate an output file
where for example CUSTOMER_NAME starts at position 12?
ASCII fixed-length file all organised by line number
and column. How do I tell in SQL to start CUSTOMER_NAME
at column 12?
Gan you give me a sample of SQL?
Thanks,
Robert
-
Field poisitions are not random. They on the trnasformation tab. If you have a single transformation for all columns then you pick that transformation and edit it. If you have one per column you just need to adjust the this with that link.
-
assuming all fields are Char
if not you need to use cast or convert (sql functions see bol) to make them char
Sample spec
PosStart PosEnd Data
1 3 blank
4 6 field1
7 12 field2
13 13 Literal #
14 20 field3
Build each element individually using text manipulation functions - Left,right,substring, etc see BOL for " string functions"
So above spec becomes
PosStart PosEnd Data sql code
1 3 blank cast(' ' as char(3))
4 6 field1 left(field1,2)
7 12 field2 left(field2,6)
13 13 Literal # cast('#' as char(1)) or just '#' result is the
14 20 field3 left(field3,7)
But what if you are not sure how long field3 is then
use Left(field3+' ',7) you are still going to get a fixed length 7 charactors
You get to work on each field individually. once they are all done, it looks like this
Select
cast(' ' as char(3)) ,
left(field1,2) ,
left(field2,6),
cast('#' as char(1)) ,
left(field3,7)
FROM sample_table
Last step concatinate all column together into a single column
Select
cast(' ' as char(3)) +
left(field1,2) +
left(field2,6) +
cast('#' as char(1)) +
left(field3,7)
FROM sample_table
save this code when someone decides they want 2# instead of one, you change the cast('#' as char(1)) to cast('##' as char(2)) and everything shifts to the right one position
-
Sorry - The post has a typo, sample spec shows field1 to be 3 positions long. Example should read left(field1,3) not left(field1,2)
Also multiple blanks were suppressed between my typing and the posting
Were I posted Left(field3+' ',7) should read left(field3+space(7),7)
You should also be familiar with string functions ltrim and rtrim (they remove leading or trailing blanks)
It's really just a matter of learning the the general approach and the tools necessary to manipulate the fields to get what you want.
-
Ed,
You're not a Newbie!
You know sime stuff... Ah?
Thanks fro you help.
I'll try quick SQL script right now and see if the positions in output file
is what i need.
Robert
-
Hi Ed,
I constructed a script and ran it.
Looks good Ed.
It's just these guys who set the requerements for file layout I think
are a bit out of their minds.
Apparently they want to include HEADER/TRAILER
and long Field Names.
How can I include the first Field Name "BENEFICIARY-NUMBER"
which is 18 characters long in the file if the specs say
BENEFICIARY-NUMBER pos 1-9
I think if it's position based
it's like the same lenght for all the values in that field, right?
BENEF-NUM
000012021
000012263
000011346
They are crazy.
Robert
-
Yes they are crazy. You are now beyond technology and into interpersonal skills, (which my wife insists I don't have). My approach would be to point out that they have obviously thought about this a great deal and have something specific in mind. It would be "SO VERY Helpful" if they could provide you with a sample of exactly what they want the header to look like. That way you can ensure that your humble contribution to the endeavor is just what they are looking for. Just something that can be opened in Notepad. They did ask for a text file right? Then proceed to apply their specs to their sample and force them to reconcile.
But be warned as you have noted my "Newbie" status is accurate relative to this board, not to my job status. I'm past retirement, and keep working only to get out of the house and I firmly believe that once you quit thinking everyday, your mind rots and the body is shortly behind. You may need to maintain gainful employment. In that context, my strategy may not be the best, but it will be fun.
-
..or why they can't deal with a "standard format" file + something like the output from a sp_help <table>.
Maybe I'm biased, but every time I hear "fixed width file" I think "Mainframe". Sounds like they're looking for some type of load file, and the schema for it. Still strange.....
Just remember - smile, and charge them accordingly
You gotta LOVE them oddball requests.----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt you're right.
They need to send this file to a big company that
maintains their data in Mainframe. But still
I cannot produce ASCII fixed-length position based file
with different length in the HEADER, Field Names and the actual values.
Robert
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply