Problem Reading a Flat file

[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....

[sud]

It looks like you have fixed length columns and not space delimited. May be you can give that a try and see.

[just4geeks]

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

[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.

[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?

[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

[pradeepleon]

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....

[chulett]

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.

[pradeepleon]

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....

[just4geeks]

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.

[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.

[pradeepleon]

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!