Fixed width in Seq file

prasad111 · Post by **prasad111** » Mon Apr 02, 2007 11:52 am

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

ray.wurlod · Post by **ray.wurlod** » Mon Apr 02, 2007 11:58 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 02, 2007 12:03 pm

Look what Code tags can do! Except for the Code tags, this is copy/paste from your original.

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 

----------------------------------------------------

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 

----------------------------------------------------

prasad111 · Post by **prasad111** » Mon Apr 02, 2007 12:09 pm

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

I changed it, is that because the SQL Type was Varchar??
as you said, nothing related to the prolem. I am getting the same error mentioned

ray.wurlod · Post by **ray.wurlod** » Mon Apr 02, 2007 12:10 pm

Your DATA are wrong. Misaligned. You have to fix this.

prasad111 · Post by **prasad111** » Mon Apr 02, 2007 12:23 pm

ray.wurlod wrote:Your DATA are wrong. Misaligned. You have to fix this. ...

thats a copy paste mistake from my side, sorry
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

Madhusv · Post by **Madhusv** » Mon Apr 02, 2007 12:46 pm

Looks like data contains Null Value or Low value, Because of which irecords are not getting imported properly. Check the data.

Thanks,
Madhu

novneet · Post by **novneet** » Mon Apr 02, 2007 1:32 pm

Do you actually have spaces in the data or is just for the representation purpose you have put the sapces?
Please verify the data as per the metadata you have provided.

prasad111 · Post by **prasad111** » Mon Apr 02, 2007 2:15 pm

novneet wrote:Do you actually have spaces in the data or is just for the representation purpose you have put the sapces?
Please verify the data as per the metadata you have provided.

Yes there is a space in the data, I have not written it manually

novneet · Post by **novneet** » Mon Apr 02, 2007 2:25 pm

In that situation the test data that u have posted is wrong. As there are spaces for the numeric columns(that to in between two numbers of same field).

prasad111 · Post by **prasad111** » Mon Apr 02, 2007 2:47 pm

novneet wrote:In that situation the test data that u have posted is wrong. As there are spaces for the numeric columns(that to in between two numbers of same field).

The data is not a problem, so do I need to change the numeric into varchar is that you are telling????

novneet · Post by **novneet** » Mon Apr 02, 2007 3:06 pm

You can check for two more things:

* Delete the property "Delimiter" completely.
* There are spaces at the end of each record or not so that the total character count in a record becomes 91.

prasad111 · Post by **prasad111** » Mon Apr 02, 2007 3:20 pm

us1aslam1us · Post by **us1aslam1us** » Mon Apr 02, 2007 3:30 pm

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.

novneet · Post by **novneet** » Mon Apr 02, 2007 3:39 pm

Please let us know what was the resolution for the issue?