import fixed format ascii file

[Former Member]

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

[former_member186498 · Accepted solution]

Hi Arnold,

I think it's related with the amounts that don't have a comma, f.e. xxx,00, but please see these SAP notes

http://service.sap.com/sap/support/notes/1382281

http://service.sap.com/sap/support/notes/1403571

http://service.sap.com/sap/support/notes/1470360

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

Note 1382281: I have no invalid record in that column, I have checked it again and it is all numeric values only.

Note 1403571: there are no letters or other invalid characters, the whole file does not contain a single comma.

Note 1470360: Even when I try with just the three lines shown above and MAXREJECT=-1 I get the same error.

So, I simply removed the trailing minus signs and the import works. However, I do need the minus signs, so does anyone have an idea of how to make it work with the trailing minus signs?

Thanks,

Arnold

[former_member186498 · Accepted solution]

Hi Arnold,

I didn't see the sign in your example, write the amount with the leading sign, i.e. -300,78, try with only few records it should work (the only difference I see with one of my transf. file is CREDITPOSITIVE=NO)

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

the problem is that I am not creating the file myself, it is a standard export from an ERP system and always has the minus sign trailing.

Thanks,

Arnold

[Former Member · Accepted solution]

Hi,

in a fixed format import, is there any way to concatenate two FCOL ranges? Something like

FCOL(107)+FCOL(93:106)?

That would allow me to move the minus sign to the front of the amount and make BPC happy.

Thanks

[former_member186498 · Accepted solution]

Hi Arnold,

BPC doesn't work with trailing sign, so or the ERP modify the download or you must change the input file.

I think you should use VB function like "right" or "join" to extract the sign and combine the 2 strings, please see

http://msdn.microsoft.com/en-us/library/eh8fefz1(v=vs.85).aspx

http://msdn.microsoft.com/en-us/library/yscc53h0(v=vs.85).aspx

http://msdn.microsoft.com/en-us/library/3ca8tfek%28v=vs.85%29.aspx

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

I got it to work like this

AMOUNT=*FCOL(107:107)+*FCOL(93:106)

I cannot change the export as it is a standard export from the source system and I do not want to manually change the file once it has been created as that is too error prone. But the above works and should be okay.

Thanks,

Arnold