FIXED DELIMITED FILE

[elucero2]

Hi, I'm just starting to write awk scripts. In the code below I'm trying to create a fixed delimeter file so I can load it into a MS db using dts. It doesn't seem to recogonize the fixed width or field separator. Does anyone have any ideas how I can separate these fields? Thanks.

BEGIN {FS="\|"
printf("%-18s %-10s %-20s %-3s %-50s %-50s\n",
"company_id","start_dt", "amt_outsd", "co_cd", "Filename", "Filedate")
}
NR==1 {VAR1=$0}
NR>=3{printf("%-18s %-10s %-20d %-3s %-50s %-50s\n",$1,$2,$3,$4,ARGV[1],VAR1)
}

 

[vgersh99]

what IS your input file: is it fixed-width OR delimited file [CSV-like]?

A sample file would be helpful.

vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[PHV]

An example of input file and expected result would surely help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[elucero2]

My data looks like this. I need it to be delimited, but when I insert a field separator ms dts does not recognize it. So the awk code below I tried to make it fixed width, but ms dts does not recogonize that either. Seems like all the fields are put into one column. Thanks. The first row of this data will become a field in my table VAR1 and the second row are fields.
AHDN|20040627|14:30:23|51866
asset_id|amt_out_dt|amt_outsd|currency_cd
0x0000340000846ede|19920501|0|USD
0x00003400008a3c82|19941215|0|USD
0x0000340000850dbc|19940516|0|USD
0x0000340000880eb2|19921228|0|USD
0x00003400008c71c9|19931209|0|USD
0x00003400008c71c9|19911209|5000|USD
0x0000340000db7c93|19920917|60000|USD
0x0000340000db7c93|20020916|0|USD
0x00003400008a7ac4|19950601|0|USD
0x00003400008d10a9|19940620|0|USD
0x0000340000b8da56|20000615|0|USD
0x0000340000b8da56|19900607|5000|USD
0x0000340000bb9817|19940415|0|USD
0x0000340000bb9817|19891010|5000|USD
0x0000340000b89e12|19920819|0|USD
0x00003400008a42fc|19941117|0|USD
0x00003400006fbbe0|20010615|0|USD
0x00003400006fbbe0|19890629|15000|USD
0x00003400008966db|19920921|0|USD
0x0000340000bbd45b|19931101|0|USD
0x00003400008b4cf3|19950626|0|USD
0x000034000083e156|19911114|0|USD
0x000034000084a717|19930526|0|USD
0x00003400008c338b|19930504|0|USD
0x0000340000876fdc|19970929|0|USD
0x0000340000876fdc|19910927|15000|USD
0x0000340000bcde56|19910319|500|USD
0x0000340000bcde56|19970319|0|USD
0x00003400010888b5|19951113|0|USD
0x00003400010888b5|19921113|150000|USD

 

[vgersh99]

seems like it's a problem with your data loader - 'ms dts'.
You're pushing the data that the loader is recongizing as valid the way it's configured.

I'd suggesting reading on the loader specifications FIRST and come up iwth the clear definition of the requirements for for the data layout to be produced.

vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[PHV]

I.E. a working example of expected result from the input example.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[elucero2]

yes,thank you. I think that's most of the problem. But when I run the awk script
BEGIN {FS="\|"
printf("%-18s %-10s %-20s %-3s %-50s %-50s\n",
"company_id","start_dt", "amt_outsd", "co_cd", "Filename", "Filedate")
}
NR==1 {VAR1=$0}
NR>=3{printf("%-18s %-10s %-20d %-3s %-50s %-50s\n",$1,$2,$3,$4,ARGV[1],VAR1)
}
on the above data. I get the following
0x0000340000846ede 19920501 0 USD
0x00003400008a3c82 19941215 0 USD

how can I get rid of the spaces?

 

[vgersh99]

define your OFS appropriately.

vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[elucero2]

Thanks, I did define the OFS, but I don't think I have the syntax correct. I don't get a ; between the fields. Isn't that what OFS is suppose to do?
BEGIN {FS="\|" ; OFS ="\;"
printf("%-18s %-10s %-20s %-3s %-50s %-50s\n",
"company_id","start_dt", "amt_outsd", "co_cd", "Filename", "Filedate")
}
NR==1 {VAR1=$0}
NR==3{printf("%-18s %-10s %-20d %-3s %-50s" %-50s\n",$1,$2,$3,$4,ARGV[1],VAR1)
}

 

[PHV]

how can I get rid of the spaces?
By not putting space in the format:
printf("%-18s%-10s%-20d%-3s%-50s%-50s\n",$1,$2,$3,$4,ARGV[1],VAR1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244