Fixed width in Seq file
Moderators: chulett, rschirm, roy
Fixed width in Seq file
Hi All,
I was reading a seq file in PX,
seqfile------->transformer-----> seqfile
in properties tab:
File = path
Read Method = Specific File
options default
In format tab:
Record level
Record Length = fixed
Field defaults
Delimiter = none
Type defaults
General
Field width = 91 {these many data will come to the file}
In columns tab,
I have set the Length and also set the Field properties by selecting the postions.
column ------SQLType --------Length-------------Field Properties
------------------------------------------------------------------------
col1 --------char----------------2 -------------------position=0
col2---------char---------------1---------------------position=2
col3--------varchar------------9---------------------position=3
col4---------numeric----------3---------------------position=12
col5--------numeric----------6----------------------position=15
col6--------varchar----------6------------------------position=21
col7-------numeric----------6------------------------position=27
col8--------varchar---------58------------------------position=33
-------------------------------------------------------------------------------
sample data looks like this
XYZABCDEF MS060000000 116707096ABCDET QWER ASDER 0001250 CT 1 000000
XYZABCDEF MS060000000 1167072007-03-2600226 00000 0000226
XYZMABCDEF MS060000000 1167070752890500080000100075289050008UP 00075289050008UP
XYZABCDEF MS060000000 211698032HJULJOPIK CHAR WERTS SS000132 OZ 1 000000
XYZMCLANE MS060000000 2116982007-03-2600240 00000 0000240
XYZABCDEF MS060000000 2116980446007117510000100044600711751AB 00044600711751AB
----------------------------------------------------
I am getting the error like this
Sequential_File_0,0: Field "col4" has import error and no default value; data: {2 4 1 0 0 0 0 0 0 20 20 20 20 20 20 0 0 4 5 2 3 0 5 4 A B C D E 20 X X X X X 20 X R N X X Y 20 K G 20 S Z 20 0 0 1 8 3 . 1 0 20 O Z 20 1 20 0 0 0 0 0 0 20 20 20 20 20 20 20 20 20 0a I C A M C L A N E 20 N W}, at offset: 12
Sequential_File_0,0: Import warning at record 0.
Sequential_File_0,0: Import unsuccessful at record 0.
the error repeats for all the records
--
--
Sequential_File_0,0: 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).
Sequential_File_0,0: Output 0 produced 3 records.
Sequential_File_0,0: Expected 124 bytes, got 20.
Sequential_File_0,0: Import error at record 38.
---------------------------
I was referring to this link still I was not able to reslove it.
viewtopic.php?t=107968&highlight=fixed+width+seq
-------------------------------
Please give me suggestions where I am going wrong.......
Thanks & Regards
Prasad
I was reading a seq file in PX,
seqfile------->transformer-----> seqfile
in properties tab:
File = path
Read Method = Specific File
options default
In format tab:
Record level
Record Length = fixed
Field defaults
Delimiter = none
Type defaults
General
Field width = 91 {these many data will come to the file}
In columns tab,
I have set the Length and also set the Field properties by selecting the postions.
column ------SQLType --------Length-------------Field Properties
------------------------------------------------------------------------
col1 --------char----------------2 -------------------position=0
col2---------char---------------1---------------------position=2
col3--------varchar------------9---------------------position=3
col4---------numeric----------3---------------------position=12
col5--------numeric----------6----------------------position=15
col6--------varchar----------6------------------------position=21
col7-------numeric----------6------------------------position=27
col8--------varchar---------58------------------------position=33
-------------------------------------------------------------------------------
sample data looks like this
XYZABCDEF MS060000000 116707096ABCDET QWER ASDER 0001250 CT 1 000000
XYZABCDEF MS060000000 1167072007-03-2600226 00000 0000226
XYZMABCDEF MS060000000 1167070752890500080000100075289050008UP 00075289050008UP
XYZABCDEF MS060000000 211698032HJULJOPIK CHAR WERTS SS000132 OZ 1 000000
XYZMCLANE MS060000000 2116982007-03-2600240 00000 0000240
XYZABCDEF MS060000000 2116980446007117510000100044600711751AB 00044600711751AB
----------------------------------------------------
I am getting the error like this
Sequential_File_0,0: Field "col4" has import error and no default value; data: {2 4 1 0 0 0 0 0 0 20 20 20 20 20 20 0 0 4 5 2 3 0 5 4 A B C D E 20 X X X X X 20 X R N X X Y 20 K G 20 S Z 20 0 0 1 8 3 . 1 0 20 O Z 20 1 20 0 0 0 0 0 0 20 20 20 20 20 20 20 20 20 0a I C A M C L A N E 20 N W}, at offset: 12
Sequential_File_0,0: Import warning at record 0.
Sequential_File_0,0: Import unsuccessful at record 0.
the error repeats for all the records
--
--
Sequential_File_0,0: 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).
Sequential_File_0,0: Output 0 produced 3 records.
Sequential_File_0,0: Expected 124 bytes, got 20.
Sequential_File_0,0: Import error at record 38.
---------------------------
I was referring to this link still I was not able to reslove it.
viewtopic.php?t=107968&highlight=fixed+width+seq
-------------------------------
Please give me suggestions where I am going wrong.......
Thanks & Regards
Prasad
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Look what Code tags can do! Except for the Code tags, this is copy/paste from your original.
Here's the same, tidied up. It is apparent that your data do not match the "fixed width" claimed in your metadata. The third row (row #2 in a parallel job) is the first culprit.
Code: Select all
column ------SQLType --------Length-------------Field Properties
------------------------------------------------------------------------
col1 --------char----------------2 -------------------position=0
col2---------char---------------1---------------------position=2
col3--------varchar------------9---------------------position=3
col4---------numeric----------3---------------------position=12
col5--------numeric----------6----------------------position=15
col6--------varchar----------6------------------------position=21
col7-------numeric----------6------------------------position=27
col8--------varchar---------58------------------------position=33
-------------------------------------------------------------------------------
sample data looks like this
XYZABCDEF MS060000000 116707096ABCDET QWER ASDER 0001250 CT 1 000000
XYZABCDEF MS060000000 1167072007-03-2600226 00000 0000226
XYZMABCDEF MS060000000 1167070752890500080000100075289050008UP 00075289050008UP
XYZABCDEF MS060000000 211698032HJULJOPIK CHAR WERTS SS000132 OZ 1 000000
XYZMCLANE MS060000000 2116982007-03-2600240 00000 0000240
XYZABCDEF MS060000000 2116980446007117510000100044600711751AB 00044600711751AB
----------------------------------------------------
Code: Select all
column ------SQLType --------Length-------------Field Properties
------------------------------------------------------------------------
col1 --------char----------------2 -------------------position=0
col2---------char----------------1--------------------position=2
col3---------varchar-------------9--------------------position=3
col4---------numeric-------------3--------------------position=12
col5---------numeric-------------6--------------------position=15
col6---------varchar-------------6--------------------position=21
col7---------numeric-------------6--------------------position=27
col8---------varchar------------58--------------------position=33
-------------------------------------------------------------------------------
sample data looks like this
XYZABCDEF MS060000000 116707096ABCDET QWER ASDER 0001250 CT 1 000000
XYZABCDEF MS060000000 1167072007-03-2600226 00000 0000226
XYZMABCDEF MS060000000 1167070752890500080000100075289050008UP 00075289050008UP
XYZABCDEF MS060000000 211698032HJULJOPIK CHAR WERTS SS000132 OZ 1 000000
XYZMCLANE MS060000000 2116982007-03-2600240 00000 0000240
XYZABCDEF MS060000000 2116980446007117510000100044600711751AB 00044600711751AB
----------------------------------------------------
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I changed it, is that because the SQL Type was Varchar??ray.wurlod wrote:Set the Width property (rather than the Max_Width property) for col3. And for col8, even though that's unrelated to the exact problem reported. ...
as you said, nothing related to the prolem. I am getting the same error mentioned
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
thats a copy paste mistake from my side, sorryray.wurlod wrote:Your DATA are wrong. Misaligned. You have to fix this. ...
the data format is
XYZABCDEF MS060000000 116707096ABCDET QWER ASDER 0001250 CT 1 000000
XYZABCDEF MS060000000 1167072007-03-2600226 00000 0000226
XYZABCDEF MS060000000 1167070752890500080000100075289050008UP 00075289050008UP
XYZABCDEF MS060000000 211698032HJULJOPIK CHAR WERTS SS000132 OZ 1 000000
XYZABCDEF MS060000000 2116982007-03-2600240 00000 0000240
XYZABCDEF MS060000000 2116980446007117510000100044600711751AB 00044600711751AB
---
after the data width 34 they will be not alligned properly
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
I think, the code tags Ray referred is about your past. when you are posting in the forum there is a option CODE which makes data properly aligned which helps the other people to read it properly.
As far as your problem is concerned, You need to change your Varchar field to Char and also it seems that the first and second columns are either having Nulls or Spaces. As suggested by others check the Format of the Data.
As far as your problem is concerned, You need to change your Varchar field to Char and also it seems that the first and second columns are either having Nulls or Spaces. As suggested by others check the Format of the Data.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)