Loading Transactional Data Transformation Error

[former_member196865]

Hi Experts,

When loading transactional data with the help of Import Package we are getting the error

"More than max reject count records rejected, please check transformation file"

The transformation file is:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = TAB

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

AssetProject = *COL(1)

CAP_Acct = *COL(17,4:8)

DataSrc = *NEWCOL(Load_SSRS)

Region = *IF(*COL(17,10:13)=*STR(0000) then *STR(1840);*COL(17,10:13)=*STR(xxxx) then *STR(1840);*COL(17,10:13))

RptCurrency = *NEWCOL(LC)

Time = *MVAL(24:94)

Version = *NEWCOL(Actual)

*CONVERSION

Time = XXXTime.xls

Sample Records are:

2007_1504_005

Construction

39446

42094

LEASE IMP 10YR

1

7

10

0

2

244

4946541

41221.18

123663.54

3627463.42

1319077.58

01-79200-0000-0-70305-00-000-000

01-14200-0000-0-00000-00-000-000

Facilities & Fulfillment

70305

43096

41221.18

123663.54

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

41221.18

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

453432.98

494654.16

494654.16

-

-

-

70305

0

Facilities & Fulfillment

Default/Direct/Overhead

We not able to import the data from the file and it has been failing. What could be the reason?

[Former Member · Accepted solution]

Hi David,

if you set MAXREJECTCOUNT=-1 it will end without errors.

Difficult to understand how are the columns of your input file, please add it as a text file with separator like ";" for us.

Further, the Conversion file for time doesn't have the last year in it.

For ex: Data in the txt file is until Mar 2020 but the conversion file ends at Mar 2019. Could it also cause the issue?
 

Conversion file and input file should be aligned.

I don't find the source but if I remember well, MVAL works only one year at once try please setting so if doesn't work try inserting only one year in conversion and also in inpout file changing consequently the range of MVAL.

Regards

     Roberto

[former_member196865 · Accepted solution]

Hi Roberto,

I changed the conversion file with the extended Months and put Maxrejectcount as -1

If I put that, I am getting this error

Validate Records=Yes

Convert

"Error :Conversion result file is empty; either the source data file was empty or no records were saved during conversion"

[Former Member · Accepted solution]

Hi david,

I believe then issue is in the your flat file please check is their any extra spaces or any character or something.

Please check rejected record list and confirm them why they are getting rejected.

If possible shre rejected list and your sample data few records and conversion file.

[former_member196865 · Accepted solution]

Hi

Please find the sample records in txt

scn.txt.zip

[Former Member · Accepted solution]

Hi David,

As i said before there is issue in the flat file when i compare both record are different there is blank space or there is one extra TAB in the record.

Have a look at below screen shot. Look at column no 17 first record and last record that you have shared.

So please check your flat file once again.

Hope this will solve your problem.

[former_member196865 · Accepted solution]

Hi,

We removed the blank record and still it says MAxrejectcount exceeded.

Should i create a new transformation file?

Should there be a space after comma in *col(17,4:8)?

[former_member196865 · Accepted solution]

Further,

I looked at the rejected list and it says

"head dimension count and column count are not matched"

I tried to check for spaces etc but cannot find it.

[Former Member · Accepted solution]

Hi David,

Instead of loading whole file at a time first try to load few record from your file. If those records gets loaded then for sure there is issue in flat file. Just to ensure try to few records.

If you have a  CSV file and you are importing you may find you have numbers that appear in double quotes, like this:

"12,345.01"

[former_member196865 · Accepted solution]

Used this excel formula to check for spaces in the first record

=IF(OR(LEFT(A2,1)=" ",RIGHT(A2,1)=" "),TRUE,FALSE)

Found a space in one cell. Removed it.

Changed the conversion file to reflect the correct months.

Changed the transformation file to reflect the correct columns.

Changed - to 0 in the file.

Removed the x's at the end of the file.

Worked after I did all these.

Thanks.

[Former Member]

Hi David,

1) Can you please validate your flat file.

2) The record that you have provided looks like something wrong in the file.

E.X column 17 if you check the length of column is 10 and in transformation you have maintained like it has 13 characters or more.if your file contains zeros at end then these zeros are not in your flat file.So please validate your file again

Region = *IF(*COL(17,10:13)

2) CAP_Acct = *COL(17,4:8) if i use it it will return 9077.

So please validate your records with master data once again.

For MAXREJECTCOUNT refer below link.

Data Transformations - SAP BusinessObjects Planning and Consolidation - SAP Library

Hope this will help you to solve.

[former_member196865]

Hi I am seeing this error after I validate and process it.

[former_member196865]

Hi column 17 is

01-79200-0000-0-70305-00-000-000

So

Region = *IF(*COL(17,10:13)=0000

CAP_Acct = *COL(17,4:8) = 79200

[former_member196865]

Further, the Conversion file for time doesn't have the last year in it.

For ex: Data in the txt file is until Mar 2020 but the conversion file ends at Mar 2019. Could it also cause the issue?