Page 1 of 1
Problem Reading a Flat file
Posted: Fri Apr 27, 2007 9:52 am
by just4geeks
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....
Re: Problem Reading a Flat file
Posted: Fri Apr 27, 2007 10:21 am
by sud
It looks like you have fixed length columns and not space delimited. May be you can give that a try and see.
Re: Problem Reading a Flat file
Posted: Fri Apr 27, 2007 10:50 am
by just4geeks
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
Posted: Fri Apr 27, 2007 10:53 am
by DSguru2B
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.
Posted: Fri Apr 27, 2007 10:55 am
by nick.bond
>##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?
Posted: Fri Apr 27, 2007 12:15 pm
by prasadduvasi
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
Re: Problem Reading a Flat file
Posted: Fri Apr 27, 2007 3:06 pm
by pradeepleon
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....
Re: Problem Reading a Flat file
Posted: Fri Apr 27, 2007 3:09 pm
by chulett
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.
Re: Problem Reading a Flat file
Posted: Fri Apr 27, 2007 3:20 pm
by pradeepleon
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....
Posted: Fri Apr 27, 2007 3:34 pm
by just4geeks
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.
Posted: Fri Apr 27, 2007 3:53 pm
by sud
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.
Posted: Fri Apr 27, 2007 7:34 pm
by pradeepleon
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!