davidle
Level 6

Business Adapter Import Error - Numeric Values

Hello,

I am trying to add a numeric value within the description field on each Cost Center we have imported previously.

However, I am getting the following error message when I execute the Simulate Action:

"Explicit conversion from data type int to ntext is not allowed."

Has anyone else seen this issue?

Thanks!

- David

0 Kudos

This thread has been automatically locked due to inactivity.

To continue the discussion, please start a new thread.

7 Replies
Nico_Erasmus
Level 7

Please share your xml (what you can) and sample data.
Is your source a file or is it sql?
When you say add, do you mean concatenate, so you are effectively editing the previous CC value or are you adding a new level to your structure?

If you can share the adapter as @Nico_Erasmus has suggested along with a debug log showing the import behavior and failure then that will likely allow a more specific response. However based on the error message I am guessing you will need to arrange for the source data query to bring in the description value as a text value rather than a numeric value - that can probably be arranged with some sort of type cast.

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
0 Kudos

Hi there.

Please share your xml (what you can) and sample data.

"See attached .csv source file and .xml business adapter file."

When you say add, do you mean concatenate, so you are effectively editing the previous CC value or are you adding a new level to your structure?

"I want to update the Description row of the Cost Centers based on the value that is listed in my .csv file."

Thanks!

- David

 

0 Kudos
ChrisG
Community Manager Community Manager
Community Manager

Try using a query like the following to get the source data from the CSV file - this will cast the Description values to text so they can be safely imported:

SELECT Name, [Parent Path], CSTR(Description) AS Description FROM [ArcherApplicationMappingTemplate.csv]

Alternatively, place an appropriately formed schema.ini file in the same directory as your .csv file to configure the data type of each column to be text.

Without either of these approaches, the Microsoft ODBC driver that reads CSV files will see the numbers you have in the "Description" column and treat the data from there as numeric - but you need it to be treated as text data to be imported into the Description property of cost centers.

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
sumiAtos
Level 2

Hi David,

you get this error message when you try to convert a ntext  field directly into a integer field.

DECLARE @MyTable TABLE ([Description] ntext )

INSERT INTO @MyTable
SELECT '123456789'

This table has one column 'Description' which has the data type = ntext  and the value '123456789'

SELECT CONVERT(INT, [Description] ) from @MyTable 

will return exactly that message. If you convert first into VARCHAR and then to int, that will work.

SELECT CONVERT(INT, CONVERT(VARCHAR(50),[Description]))  from @MyTable

So if you are using a database table as input data for BI, you need to convert this ntext field first into a varchar field. When the content of this column has only digits, then BI will be able to convert this content implicitly into an int and insert into the column which is defined as int.

Sumi

 

 

0 Kudos
milind_teli
Level 3

I am also getting same error can you please suggest on the same.

0 Kudos

@milind_teli 

If you're working with Excel (not CSV) files please make sure to format all cells as Text cells in Excel.

For CSV files I think that the recommendations already provided above in this thread provides the guidance needed...

However, please don't hesitate to share a sample of both the adapter configuration and a sample file with data if you need further guidance.

Thank,s

0 Kudos