Error importing decimals

Stetson

I'm importing a csv file using a template and getting errors on a numeric column when it contains decimals. For example, 1234 will import, 1234.5 and 1234.56 will both generate error 'Error converting data type nvarchar to decimal.. Row will be ignored'. The field is mapped to decimal(18,2) in SQL Server, and to a number with format '#.00' in the BO and import template. I don't know where 'nvarchar' is coming into play.

So the following sample rows:

1068511166,1295.12
1068511167,2380.00
1068511168,1190.1
1068511170,1190
2003595372,-1190
1068511171,2380

are generating the following log output:

Importing business object STATEMENT
Failure for row 1: 1068511166,1295.12
Error persisting business object STATEMENT Error converting data type nvarchar to decimal.. Row will be ignored
37 Record for row 2 imported successfully
Failure for row 3: 1068511168,1190.1
Error persisting business object STATEMENT Error converting data type nvarchar to decimal.. Row will be ignored
37 Record for row 4 imported successfully
37 Record for row 5 imported successfully
37 Record for row 6 imported successfully

I've tried using a display format of '#.##' rather than '#.00' with no luck. Any ideas? Thanks.

Stetson

The BAS table for this BO defines the field as float. (I thought for a moment that might be where the nvarchar is coming from, but not.)

Stetson

When I remove the period characters (.) out of the .csv file entirely, all records import successfully (but with incorrect amounts), ex:

Invoice Amount

1068511166 129512.00
1068511167 238000.00
1068511168 11901.00
1068511170 1190.00
1068511171 2380.00
2003595372 -1190.00

So '1295.12' imports as '129512.00'. Seems I need to import the numeric values as strings and convert them to numbers once imported? I'm looking into how I might use the AS_NUMBER function in the import template. Thanks for any feedback.

aware_support

Specify the format of the number you are importing in the corresponding column in the header of the CSV separated by space:

Object.Attr1, Object.Attr2, Object.NmbAttr #.00, Object.Attr3

Or import using Import Templates feature

Stetson

Thank you. The file being imported is a user-provided file and cannot have a header (though I will try your header suggestion to see the result). I'm importing using a template and specifying a Type of Number and a Format of #.00 in the 'Column Names and Formatting' dialog of the AwareIM import template definition. As soon as the import hits a value with decimals, it chokes. Records where the field is an integer load successfully. I've also tried defining a rule in the import definition such as AS_NUMBER({field}) but no luck.

Stetson

No luck adding the format to the csv header. Loading this file:

Invoice,Amount #.00
1068511166,1295.12
1068511167,2380.00
1068511168,1190.1
1068511170,1190
2003595372,-1190
1068511171,2380

I get this output:

Importing business object STATEMENT
Failure for row 1: 1068511166,1295.12
Internal error. Error persisting business object STATEMENT Error converting data type nvarchar to decimal.. Row will be ignored
37 Record for row 2 imported successfully
Failure for row 3: 1068511168,1190.1
Internal error. Error persisting business object STATEMENT Error converting data type nvarchar to decimal.. Row will be ignored
37 Record for row 4 imported successfully
37 Record for row 5 imported successfully
37 Record for row 6 imported successfully
36 Data import has been completed. 4 records have been processed

Any other ideas?

Thanks.

Jaymer

Version & Build # ?

Stetson

Hi, Jaymer. Thanks. Version 8.3, build 2621.

Stetson

I just realized that numeric values ending literally in '.00' DO get loaded, so it's not necessarily the decimal point character that seems to be causing the issue. For example, record two in the following file loads successfully. So do the integers. Just not the non-'.00' decimals:

Input CSV:

1068511166,1295.12
1068511167,2380.00
1068511168,1190.1
1068511170,1190
2003595372,-1190
1068511171,2380

log.txt:

Loaded to SQL Server:

Invoice Amount

1068511167 2380.00
1068511170 1190.00
1068511171 2380.00
2003595372 -1190.00

Jaymer

Stetson
I hate to tell you this, but I got this working with no issues, soup to nuts in about 6 minutes.
Copied data from post in this thread. Pasted into .txt file. No headers.
Created a new BO with 2 fields.
Went into Test.
Created Import Template (sniffed the file, assigned the 2 fields).
Activated it
Went to Import (pic)
Browsed data in SQL (pic)

v8.3
Build 2629

Stetson

Thanks, Jaymer. It must have something to do with my table being external. It doesn't reside in the Aware db (though it is on the same db server). I have an external stored procedure that uses it, and the table is in the same db as the sproc. It's such a simple thing though, you'd think Aware could handle it. I'm sure it can - that's why this is so frustrating. I keep getting 'value of STATEMENT could not be resolved' on the csv rows where the number has one or two non-zero decimal digits. Somewhere it's trying to do a conversion of a value that cannot be converted to a number. (Btw, I EXPORTed the table with ease - no issues.)

These are the errors I'm getting from the control panel:

2019-10-18 14:49:21,668 RecAP#admin -30 Updating business object BAS_UDIT 16281
2019-10-18 14:49:21,684 RecAP#admin -16 Finished updating business object BAS_UDIT 16281
2019-10-18 14:49:21,684 RecAP#admin -17
2019-10-18 14:49:21,684 RecAP#admin -17
2019-10-18 14:49:52,606 RecAP#admin -Importing business object STATEMENT
Value of STATEMENT could not be resolved
Value of STATEMENT could not be resolved
2019-10-18 14:49:52,637 RecAP#admin -Failure for row 1: 1068511162019-10-18 14:54:40,919 RecAP#admin -17
6,1295.12
Error persisting business object STATEMENT Error converting data type nvarchar to decimal.. Row will be ignored
Value of STATEMENT could not be resolved
Value of STATEMENT could not be resolved
2019-10-18 14:49:52,668 RecAP#admin -37 Record for row 2 imported successfully

('STATEMENT' is both my BO name and import template name.) I've tried defining the underlying SQL table both with and without a primary key, with fields nullable and non-nullable, etc.)

This is the definition of my SQL table (at the moment):

CREATE TABLE [dbo].[RAP_STATEMENT](
[Invoice] varchar NULL,
[Amount] [decimal](18, 2) NULL
)

So simple yet so maddening. I may have to make the table internal to Aware, but I hate doing that because I have a non-Aware version of the app that I am also using.

Stetson

It works for me as well, if I make the table internal.

Importing business object STATEMENT
37 Record for row 1 imported successfully
37 Record for row 2 imported successfully
37 Record for row 3 imported successfully
37 Record for row 4 imported successfully
37 Record for row 5 imported successfully
37 Record for row 6 imported successfully
36 Data import has been completed. 6 records have been processed

SO - I guess my issue is with importing decimals to externally persisted tables.

Jaymer

Well, at least that gives something more for support to go on in debugging the issue -up until now, you had never mentioned that fact. So any effort support might’ve put into it up till now, was trying to double check this on regular aware tables, just like I did.

Tools like Aware really shouldn’t care what the back end data is.
So I would assume that’s something that support can correct, although that may not help you in the short term.

Stetson

Thanks, Jaymer. My apologies, you're exactly right. I finally got it working and what I did was define the numeric field as datatype float rather than decimal in SQL Server. I generated an Aware table and seeing as it defined the numeric field as float, I put that into my external table def and the import now works. Phew. That one took a LOT of time. Thanks for all of your help!