Issue with source Flat File with comma separator","

[former_member1239796]

Hi

I am getting the following error at PSA while loading a .txt(tab(de limited) file in to BW thru Flat file loading

the column which is causing the error is number with upto 2 decimals

Error 'The argument '"1,097.00"' cannot be interpreted asa number' on assignment field /BIC/ZDATAVALU record 12436 value "1,097.00"

Message no. RSDS013

This is the column from txt file.

currently am fixing this manually is there a way to automate this like writing a routine ?

[karthik_vasudevan · Accepted solution]

Hi Rahul

Are you converting an excel file and creating a text file.

If its an excel or csv, the format of the cell should be general instead of number which will work perfectly in flat file loads.

You could try to change the format before saving the file as a text file. If this is not the case, let us know. We could try something else.

Regards

Karthik

[karthik_vasudevan · Accepted solution]

Also please share your infopackage (extraction tab) and datasource (field tab) screenshots

[former_member1239796 · Accepted solution]

Thank you karthik for replying

am formatting that particular column on the excel file and clicking save as "tab delimited(.txt)"

and then loading this formatted txt file manually to make the load successful to PSA.

[RamanKorrapati · Accepted solution]

hi,

ZDATAVALU - what kind of key fig youd efined as?

Whts this value 1097.00, please recheck yoour value formate and again reload to psa.

Thanks

[former_member1239796 · Accepted solution]

@Karrapati:It is defined as number and Data type is Dec -Counter or amount field with comma and sign

[karthik_vasudevan · Accepted solution]

The before saving, change the format of that particular column as General in Excel and then save the file as tab delimited.

In Excel format, it should be in general and not number. It will work fine.

We have lot of flat file loads and it works this way.

Regards

Karthik

[former_member1239796 · Accepted solution]

ok,

Is there way can we remove those " "  and , in the BW by writing any routine at IP level?

[karthik_vasudevan · Accepted solution]

I am not sure if you could write a routine for a keyfigure in infopackage level. But we can try resolving in other ways.

Please provide the screenshots which I have requested earlier. We will see how to go further

Regards

Karthik

[former_member1239796 · Accepted solution]

Field

[karthik_vasudevan · Accepted solution]

Thanks

In datasource, change the format as external and try loading the same file which you have loaded earlier.

You can try this in dev system in few mins hopefully.

Please change and let us know how it behaves. According to many changes that we have done in our project, this worked in many cases.

Let check your system's luck!

Regards

Karthik

[former_member1239796 · Accepted solution]

Karthik,

I have tried changing it to external and had loaded the same file.

am getting the following error

Error 'An exception with the type CX_SY_CONVERSION_NO_NUM' at conversion exit RSDS_CONVERT_NUMBER (field /BIC/ZDATAVALU record 12759, value "1,103.00")

Message no. RSDS012

[former_member1239796 · Accepted solution]

Any Thoughts?    

[karthik_vasudevan · Accepted solution]

Hi

In the extraction tab of your datasource, please change the data separator as , and escape sign as "

Change this and preview data.

Regards

Karthik

[former_member1239796 · Accepted solution]

I have changed the settings but I am not getting any data on the columns from .txt file

and in the data for the column "ZDATAVALU" is all zeros

can we think of any kind of routine ?

any suggestion on that?

[karthik_vasudevan · Accepted solution]

In Infopackage level, we could only filter the data. Only in transformation, we could modify it. I don't think routine will work for you.

Now I have replicated the same scenario in my dev system and it seems to be fine.

See the below.

Initially, it gave me the same error that you have got.

So the error is with the keyfigure G_AVVFIN and the value is 1,386.92. This is not being accepted.

Then I have changed the datasource field tab setting as External and then the load was successful, but the value was wrong showing 1.39 which is wrong.

Then I did change my user default setting using TCODE SU01 as below

The decimal notation should be as shown in the image below.

Now when I loaded the data using the infopackage, the load is successful with correct values populated.

So it does work mate. Please try this again carefully

Regards

Karthik

[former_member1239796 · Accepted solution]

Thank you Karthik for the inputs.

I tried with external it doesn't work when the value is "1,132.00" ( you missed the quotes (" ")).

and with external the value 1,132.00(without( " ")) the data load works fine (thats why this scenario  was working for both of us)

and without external both 1,132.00 and "1,132.00"  values got failed.

Any thoughts?

[karthik_vasudevan · Accepted solution]

ok understood now. What is the source of the file. A number should never have a quote, so there should be something basically wrong with the file preparation. Give a little background of this please

When the csv file has number as 1,132.00 and when you open the file as notepad, you will find the quote in it.

But in csv file, you should not have a quote which is wrong. We need to fix this in the file level and cant be solved in infopackage.

[former_member1239796 · Accepted solution]

am converting the  .xlsx   file and am saving that file into .txt(tab delimited)

and on some of the data on the "Data value " column when am converting(from xlsx to .txt) am getting " " (quotes) on some of the values.

[karthik_vasudevan · Accepted solution]

Ofcource its in number format and it will saved with quotes only.

The only way to resolve this is to change the format and then save the file which I have already explained you.

In excel, change the format of the column to GENERAL and then save it as txt file. There are some limitations in BI in accepting the data. This is one of them.

Anyways you are converting the file and saving into txt, along with that change the format of the column into GENERAL and then save it. This will not be a big burden.

Try this once and let me know if it works please

Regards

Karthik

[former_member1239796 · Accepted solution]

I have tried this option earlier which worked fine for me but as i came to know that it might be an overhead to the user who uploads it, so i was looking for other options if any.

Really appreciate a lot for your help. Thank you once again.

[karthik_vasudevan · Accepted solution]

Sounds good since the issue is resolved now. But I am afraid if there is any alternatives for this issue. If you find something, please update it in forums please. It would be useful for me as well. I am changing the format everytime and loading it.

Have a good weekend