Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
just4geeks
Premium Member
Posts: 644 Joined: Sat Aug 26, 2006 3:59 pm
Location: Mclean, VA
Post
by just4geeks » Fri Apr 27, 2007 9:52 am
I am trying to read a flat file in a Seq file stage.
Snapshot of flat file.
Code: Select all
012345678901 890 89012345 12456 259 1489.236
012345678901 901 90123456 12 458 12545.454
012345678901 012 01234567 0123 589 154505445
012345678901 123 12345678 11111 145 021425450
012345678901 123 12345678 10.20 5.23 1256.12
012345678901 234 23456789 11.40 23.5 1454.6
I have set the delimiter as whitespace.
Snapshot of Column Def
When I run a View Data, I get the following error.
Code: Select all
##W TOIX 000000 10:34:45(000) <Sequential_File_0,0> Field "upc_cd" has import error and no default value; data: {0 1 2 3 4 5 6 7 8 9 0 1}, at offset: 0
##W TOIX 000154 10:34:45(001) <Sequential_File_0,0> Import warning at record 0.
##W TOIX 000018 10:34:45(002) <Sequential_File_0,0> Import unsuccessful at record 0.
##W TOIX 000000 10:34:45(003) <Sequential_File_0,0> Field "upc_cd" has import error and no default value; data: {0 1 2 3 4 5 6 7 8 9 0 1}, at offset: 0
##W TOIX 000154 10:34:45(004) <Sequential_File_0,0> Import warning at record 1.
##W TOIX 000018 10:34:45(005) <Sequential_File_0,0> Import unsuccessful at record 1.
##W TOIX 000000 10:34:45(006) <Sequential_File_0,0> Field "upc_cd" has import error and no default value; data: {0 1 2 3 4 5 6 7 8 9 0 1}, at offset: 0
##W TOIX 000154 10:34:45(007) <Sequential_File_0,0> Import warning at record 2.
Any idea how to get around the problem. Thanks for your time....
Attitude is everything....
sud
Premium Member
Posts: 366 Joined: Fri Dec 02, 2005 5:00 am
Location: Here I Am
Post
by sud » Fri Apr 27, 2007 10:21 am
It looks like you have fixed length columns and not space delimited. May be you can give that a try and see.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
just4geeks
Premium Member
Posts: 644 Joined: Sat Aug 26, 2006 3:59 pm
Location: Mclean, VA
Post
by just4geeks » Fri Apr 27, 2007 10:50 am
sud wrote: It looks like you have fixed length columns and not space delimited. May be you can give that a try and see.
I tried fixed width and I get the following error.
Code: Select all
>##E TOIX 000139 11:48:59(008) <Sequential_File_0> Import validation failed.
>##E TFIG 000000 11:48:59(009) <Sequential_File_0> At field "units": When validating import/export function: APT_GFIX_Decimal::validateParameters: the decimal "text" format is variable length, and no external length is specified;
>you should possibly specify an appropriate "width" property; external format: {text, padchar=32, nofix_zero, precision=5, scale=3, round=trunc_zero, ascii}.
>##E TFIG 000119 11:48:59(010) <Sequential_File_0> At field "units": Error validating import/export function
>##E TFIG 000000 11:48:59(011) <Sequential_File_0> At field "dollars": When validating import/export function: APT_GFIX_Decimal::validateParameters: the decimal "text" format is variable length, and no external length is specified;
>you should possibly specify an appropriate "width" property; external format: {text, padchar=32, nofix_zero, precision=3, scale=2, round=trunc_zero, ascii}.
>##E TFIG 000119 11:48:59(012) <Sequential_File_0> At field "dollars": Error validating import/export function
>##E TFIG 000000 11:48:59(013) <Sequential_File_0> At field "price": When validating import/export function: APT_GFIX_Decimal::validateParameters: the decimal "text" format is variable length, and no external length is specified;
>you should possibly specify an appropriate "width" property; external format: {text, padchar=32, nofix_zero, precision=9, scale=4, round=trunc_zero, ascii}.
>##E TFIG 000119 11:48:59(014) <Sequential_File_0> At field "price": Error validating import/export function
Attitude is everything....
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Fri Apr 27, 2007 10:53 am
Try searching on the errror message. I remember a few posts on the same "specify width" error. Or read them as char and downstream change the type.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
nick.bond
Charter Member
Posts: 230 Joined: Thu Jan 15, 2004 12:00 pm
Location: London
Post
by nick.bond » Fri Apr 27, 2007 10:55 am
>##E TFIG 000000 11:48:59(009) <Sequential_File_0> At field "units": When validating import/export function: APT_GFIX_Decimal::validateParameters: the decimal "text" format is variable length, and no external length is specified;
can you show us the definitions you have for the rest of the fields?
Regards,
Nick.
prasadduvasi
Participant
Posts: 19 Joined: Wed Feb 15, 2006 11:08 am
Post
by prasadduvasi » Fri Apr 27, 2007 12:15 pm
You have mentioned all fields as not nullable.
So you need to mention the default value for those not null fields in the sequential file stage iorder to read it properly.
you can set the default value for the fileds in seq file stage by clicking in front of columns in columns tab page.a new window will open for setting the default value
Prasad Duvasi
pradeepleon
Participant
Posts: 32 Joined: Fri Dec 02, 2005 10:44 pm
Location: Chicago,IL
Post
by pradeepleon » Fri Apr 27, 2007 3:06 pm
just4geeks wrote: I am trying to read a flat file in a Seq file stage.
Snapshot of flat file.
Code: Select all
012345678901 890 89012345 12456 259 1489.236
012345678901 901 90123456 12 458 12545.454
012345678901 012 01234567 0123 589 154505445
012345678901 123 12345678 11111 145 021425450
012345678901 123 12345678 10.20 5.23 1256.12
012345678901 234 23456789 11.40 23.5 1454.6
I have set the delimiter as whitespace.
Snapshot of Column Def
When I run a View Data, I get the following error.
Code: Select all
##W TOIX 000000 10:34:45(000) <Sequential_File_0,0> Field "upc_cd" has import error and no default value; data: {0 1 2 3 4 5 6 7 8 9 0 1}, at offset: 0
##W TOIX 000154 10:34:45(001) <Sequential_File_0,0> Import warning at record 0.
##W TOIX 000018 10:34:45(002) <Sequential_File_0,0> Import unsuccessful at record 0.
##W TOIX 000000 10:34:45(003) <Sequential_File_0,0> Field "upc_cd" has import error and no default value; data: {0 1 2 3 4 5 6 7 8 9 0 1}, at offset: 0
##W TOIX 000154 10:34:45(004) <Sequential_File_0,0> Import warning at record 1.
##W TOIX 000018 10:34:45(005) <Sequential_File_0,0> Import unsuccessful at record 1.
##W TOIX 000000 10:34:45(006) <Sequential_File_0,0> Field "upc_cd" has import error and no default value; data: {0 1 2 3 4 5 6 7 8 9 0 1}, at offset: 0
##W TOIX 000154 10:34:45(007) <Sequential_File_0,0> Import warning at record 2.
Any idea how to get around the problem. Thanks for your time....
It isn't a fixed flat file.Have you got this resolved?...If not reply....
Thanks,
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Fri Apr 27, 2007 3:09 pm
pradeepleon wrote: It isn't a fixed flat file.Have you got this resolved?...If not reply....
How do you know this? Sure looks like it
could be to me. Usually one would know or be told by whomever provides the file, you shouldn't have to guess.
-craig
"You can never have too many knives" -- Logan Nine Fingers
pradeepleon
Participant
Posts: 32 Joined: Fri Dec 02, 2005 10:44 pm
Location: Chicago,IL
Post
by pradeepleon » Fri Apr 27, 2007 3:20 pm
chulett wrote: pradeepleon wrote: It isn't a fixed flat file.Have you got this resolved?...If not reply....
How do you know this? Sure looks like it
could be to me. Usually one would know or be told by whomever provides the file, you shouldn't have to guess.
You are right chulett
I guessed it after a few trials....
Thanks,
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.
just4geeks
Premium Member
Posts: 644 Joined: Sat Aug 26, 2006 3:59 pm
Location: Mclean, VA
Post
by just4geeks » Fri Apr 27, 2007 3:34 pm
DSguru2B wrote: Or read them as char and downstream change the type.
I read them as char and fixed width limited. I get the following error.
Code: Select all
Short read encountered on import; this most likely indicates one of the following possibilities:
>1) the import schema you specified is incorrect
>2) invalid data (the schema is correct, but there is an error in the data).
This is the table defs I have now.
Code: Select all
column1 char 13
column2 char 4
column3 char 9
column4 char 6
column5 char 5
column6 char 13
I wasn't told if this is a fixed width or delimited file. I am guessing it by looking at the records.
Attitude is everything....
sud
Premium Member
Posts: 366 Joined: Fri Dec 02, 2005 5:00 am
Location: Here I Am
Post
by sud » Fri Apr 27, 2007 3:53 pm
Ended up spending too much time on this ... :
column1 varchar 13
column2 varchar 4
column3 varchar 9
column4 varchar 6
column5 varchar 5
column6 varchar 10
(watch your last record ... has less spaces)
In the format tab:
Record level --> Record length = fixed
Field defaults --> delimiter = none
Field defaults --> Quote = none
And was able to read the data you pasted.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
pradeepleon
Participant
Posts: 32 Joined: Fri Dec 02, 2005 10:44 pm
Location: Chicago,IL
Post
by pradeepleon » Fri Apr 27, 2007 7:34 pm
sud wrote: Ended up spending too much time on this ... :
column1 varchar 13
column2 varchar 4
column3 varchar 9
column4 varchar 6
column5 varchar 5
column6 varchar 10
(watch your last record ... has less spaces)
In the format tab:
Record level --> Record length = fixed
Field defaults --> delimiter = none
Field defaults --> Quote = none
And was able to read the data you pasted.
Good Job!
Thanks,
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.