Skip to Content
0
Former Member
Aug 08, 2012 at 10:01 AM

import fixed format ascii file

49 Views

Hi,

I am trying to import a fixed format ascii file, the first three lines of which look like this

ACCOUNT0000ACCTDETAIL0000CATEGORY0000CONSOLVIEW0000DATASRC0000ENTITY0000INTCO0000SEGMENTS0000TIME0000TRNCURRENCY0000SIGNEDDATA

1111000000F_ADD0000ACTUAL0000LC0000INPUT0000E.10000000I_NONE0000S_NONE00002011.DEC0000T_NONE00000037000000

1111000000F_CLO0000ACTUAL0000LC0000INPUT0000E.10000000I_NONE0000S_NONE00002011.DEC0000T_NONE00000200000000

1111000000F_DEC0000ACTUAL0000LC0000INPUT0000E.10000000I_NONE0000S_NONE00002011.DEC0000T_NONE00000011000000-

My transformation file looks like this:

*OPTIONS

FORMAT = FIXED

HEADER = NO

DELIMITER =

SKIP = 1

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=50

ROUNDAMOUNT=

*MAPPING

ACCOUNT=*FCOL(1:6)

ACCTDETAIL=*FCOL(11:15)

CATEGORY=*FCOL(20:25)

CONSOLVIEW=*FCOL(30:31)

ENTITY=*FCOL(45:50)

DATASRC=*FCOL(36:40)

INTCO=*FCOL(55:60)

SEGMENTS=*NEWCOL(S_NONE)

TIME=*FCOL(75:82)

TRNCURRENCY=*NEWCOL(T_NONE)

SIGNEDDATA=*FCOL(97:107)

*CONVERSION

When I validate and save the transformation file it runs through without errors and the output looks like this:

ACCOUNT,ACCTDETAIL,CATEGORY,CONSOLVIEW,ENTITY,DATASRC,INTCO,SEGMENTS,TIME,TRNCURRENCY,AMOUNT

111100,F_ADD,ACTUAL,LC,E.1000,INPUT,I_NONE,S_NONE,2011.DEC,T_NONE,0037000000

111100,F_CLO,ACTUAL,LC,E.1000,INPUT,I_NONE,S_NONE,2011.DEC,T_NONE,0200000000

111100,F_DEC,ACTUAL,LC,E.1000,INPUT,I_NONE,S_NONE,2011.DEC,T_NONE,0011000000-

To me this looks okay(ish) but when I try to import I get this error message

TOTAL STEPS 2

1. Convert Data: completed in 0 sec.

2. Load and Process: Failed in 1 sec.

3. Import: completed in 1 sec.

[Selection]

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

FILE=\\fixed_format.txt

TRANSFORMATION=\\fixed_format.xls

CLEARDATA= No

RUNLOGIC= No

CHECKLCK= No

[Messages]

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

Convert Data

Success

Record Count : 33

Accept Count : 33

Reject Count : 0

Skip Count : 0

The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (8004202c): TransformCopy 'DTSTransformation__11' conversion error: General conversion failure on column pair 1 (source column 'SIGNEDDATA' (DBTYPE_STR), destination column 'SIGNEDDATA' (DBTYPE_NUMERIC)).)

The characters shown in the amount column are all numeric, apart from the - being at the end rather than the beginning which I didn't think should be a problem.

Does anyone know what the problem is?

Thanks,

Arnold