Skip to Content
0

BPC 10 – HOW TO UPLOAD A FLAT FILE WITH EMPTY FIELD(s)

Aug 09, 2017 at 05:03 PM

275

avatar image
Former Member

Hello Experts,

I’m trying to upload a flat file with transactional data and I would like to map those empty fields to specifics members ID.

See an example below (imagine that I cannot change the source file)

ACCOUNT ENTITY TIME CATEGORY SIGNEDDATA ACC1 USA1 2018.JAN 50 ACC2 USA2 2018.JAN BUDGET 100

In the first line, the field “Category” is empty so in those case I would like to map the External value “empty” to “ACTUALS”. Is this possible in the conversion file?

I tried:

*OPTIONS FORMAT = DELIMITED HEADER = YES DELIMITER = COMMA SKIP = SKIPIF = VALIDATERECORDS=YES CREDITPOSITIVE=YES MAXREJECTCOUNT=-1 ROUNDAMOUNT= *MAPPING ENTITY=*COL(2) ACCOUNT = *COL(1) TIME= *COL(3) CATEGORY = *COL(4) AMOUNT = *COL(5) *CONVERSION CATEGORY=CONVERSION.XLS!CATEGORY

And in the conversion:

EXTERNAL INTERNAL FORMULA ? ACTUALS ACTUALS <empty> ACTUALS

And didn’t work.

Do you have any suggestion?

Thanks!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Aug 10, 2017 at 02:12 PM
0

I see some other strange things - DELIMITER=COMMA

Has to be

DELIMITER=,

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Aug 09, 2017 at 06:46 PM
0

In mapping section use IF. Not in conversion !

And please remove incorrect tag - you are using BPC NW or MS ??????

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Vadim,

Thanks for your help.

I'm migrating from BPC10 MS to BPC10 NW so ideally I need a solution for both version (first I will try to solve it in MS and then in NW). In both cases this transformation file will be used only to upload flat files.

I tried to put an *IF condition in the mapping section but it didnt worked. This is what i tried (first in 10.0 MS):

CATEGORY = *IF(*COL(4)= *STR() THEN *STR(ACTUALS); *COL(4))

Error:

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

To have in mind:

1) I was not using a conversion file

2) When I restored my conversion file (with one simple dummy mapping) for the category, it didnt work.

3)Flat file contains valid data. I’m able to process the file if I complete the field category in it (and remove the *IF condition).

Can you help me with the correct syntax for the *IF condition?

Thank a lot!

0

I am not an expert in BPC MS, but for BPC NW the solution with IF will work.

0
Former Member

And how you will writte the *IF condition in NW? like this "CATEGORY = *IF(*COL(4)= *STR() THEN *STR(ACTUALS); *COL(4))"

thanks a lot

0
avatar image
Former Member Aug 10, 2017 at 12:14 PM
0

Try

CATEGORY=*IF(*col(4) = *STR() THEN *STR(ACTUALS);*col(4))

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Jim, thanks for your comments. Unfortunatly your suggestion didnt work. the error is the same:

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

In the conversion file is not possible to mapp an empty value with a fixed value, correct?

thanks again for your time!

0

In the conversion file it's not possible to map empty string. I have already told you about this limitation. You have to play wit transformation file.

0