Data factory error copying to informix

Question

Data factory error copying to informix

Emilio Salas Roura 65

I have a simple datafactory that reads from a CSV file and writes to an informix table. I have a problem with decimal columns in informix database. I can save integers but when I try to save numbers with decimals I have this error:

ERROR [22018] [Informix][Informix ODBC Driver][Informix]A character to numeric conversion process failed,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Informix][Informix ODBC Driver][Informix]A character to numeric conversion process failed,Source=iclit09b.dll,'

the CSV contains the number with this format, example:

XX;22.22;xx

If I send the file with this format XX;22,22;xx Azure gives an error

Amira Bedhiafi 33,071 Reputation points Volunteer Moderator

2024-02-27T19:21:38.93+00:00

There's a mismatch between the expected numeric format in the database and the format of the numeric data being inserted. This is often due to differences in decimal separators since Informix (or the local settings on the database server) might be expecting a comma , as the decimal separator, whereas your CSV file uses a dot .. So you neef first to check the Locale Settings on the Informix Server. If changing server settings is not feasible, consider adjusting the format of the decimal numbers in your CSV file to match the expected format of the Informix database. You can use a text editor or a script to replace dots . with commas , for decimal separators.
Emilio Salas Roura 65 Reputation points

2024-02-28T07:51:32.15+00:00

Hi, change the local settings of Informix is not an option. I changed the . with commas and then Azure is having an error before sending to Informix Thanks,
Art Kagel 0 Reputation points

2024-02-28T12:22:26.7066667+00:00

There is an environment variable that Informix uses to define the numeric format. It seems to be defaulting in your system to using the comma as the decimal separator. You can change that. The variable is DBMONEY and is set to a string made up of <front>[comma or period}<back> where "front" and "back" are the currency strings to display before and after values of type MONEY in Informix. The "comma or period" specifies the decimal separator to use in display and also to decode incoming numeric strings. The default is: "$." in the US locale with no "back" string in most European locales it would default to the Euro symbol and comma instead of the dollar sign and period. You just need to change the comma to period. Here's an example changing from the US standard period to the European standard comma on my system:
art@Elezar-II:~$ cat >fil.unl
4,3|5,2|
art@Elezar-II:~$ dbaccess testdb -

Database selected.

load from fil.unl insert into numbers;

1213: A character to numeric conversion process failed

847: Error in load file row 1.
Error in line 1
Near character position 38

Database closed.

art@Elezar-II:~$ DBMONEY="$," dbaccess testdb -

Database selected.

load from fil.unl insert into numbers;

1 row(s) loaded.

Notice the setting of DBMONEY inline with the execution of dbaccess there.

Please note that there are support forums for Informix on the IBM web site:
https://community.ibm.com/community/user/datamanagement/communities/community-home?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=groupdetails You'll need an IBM ID to log in, but that is free for the asking when you click on <join/log in>.
QuantumCache 20,366 Reputation points Moderator

2024-02-29T06:25:38.5533333+00:00

Emilio Salas Roura If any of the below response is helpful, please click "Accept Answer".
Emilio Salas Roura 65 Reputation points

2024-02-29T15:38:56.15+00:00

Hi, I think that the problem is related with the ODBC driver [Informix ODBC Driver][Informix]A character to numeric conversion process failed I cannot change the database locale but maybe changing the client locale? I tried this and its not working
QuantumCache 20,366 Reputation points Moderator

2024-02-29T20:16:19.8633333+00:00

@Emilio Salas Roura

I See Art Kagel on the Informix Forum....!

Did you try to ask the Informix forums about this error? if anyone has any suggestions? Please let us know if you are still stuck and we will try our best to help you!

Your answer

Amira Bedhiafi 33,071 Reputation points Volunteer Moderator

2024-02-27T19:21:38.93+00:00

There's a mismatch between the expected numeric format in the database and the format of the numeric data being inserted. This is often due to differences in decimal separators since Informix (or the local settings on the database server) might be expecting a comma , as the decimal separator, whereas your CSV file uses a dot .. So you neef first to check the Locale Settings on the Informix Server. If changing server settings is not feasible, consider adjusting the format of the decimal numbers in your CSV file to match the expected format of the Informix database. You can use a text editor or a script to replace dots . with commas , for decimal separators.
Emilio Salas Roura 65 Reputation points

2024-02-28T07:51:32.15+00:00

Hi, change the local settings of Informix is not an option. I changed the . with commas and then Azure is having an error before sending to Informix Thanks,
Art Kagel 0 Reputation points

2024-02-28T12:22:26.7066667+00:00

There is an environment variable that Informix uses to define the numeric format. It seems to be defaulting in your system to using the comma as the decimal separator. You can change that. The variable is DBMONEY and is set to a string made up of <front>[comma or period}<back> where "front" and "back" are the currency strings to display before and after values of type MONEY in Informix. The "comma or period" specifies the decimal separator to use in display and also to decode incoming numeric strings. The default is: "$." in the US locale with no "back" string in most European locales it would default to the Euro symbol and comma instead of the dollar sign and period. You just need to change the comma to period. Here's an example changing from the US standard period to the European standard comma on my system:
art@Elezar-II:~$ cat >fil.unl
4,3|5,2|
art@Elezar-II:~$ dbaccess testdb -

Database selected.

load from fil.unl insert into numbers;

1213: A character to numeric conversion process failed

847: Error in load file row 1.
Error in line 1
Near character position 38

Database closed.

art@Elezar-II:~$ DBMONEY="$," dbaccess testdb -

Database selected.

load from fil.unl insert into numbers;

1 row(s) loaded.

Notice the setting of DBMONEY inline with the execution of dbaccess there.

Please note that there are support forums for Informix on the IBM web site:
https://community.ibm.com/community/user/datamanagement/communities/community-home?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=groupdetails You'll need an IBM ID to log in, but that is free for the asking when you click on <join/log in>.
QuantumCache 20,366 Reputation points Moderator

2024-02-29T06:25:38.5533333+00:00

Emilio Salas Roura If any of the below response is helpful, please click "Accept Answer".
Emilio Salas Roura 65 Reputation points

2024-02-29T15:38:56.15+00:00

Hi, I think that the problem is related with the ODBC driver [Informix ODBC Driver][Informix]A character to numeric conversion process failed I cannot change the database locale but maybe changing the client locale? I tried this and its not working
QuantumCache 20,366 Reputation points Moderator

2024-02-29T20:16:19.8633333+00:00

@Emilio Salas Roura

I See Art Kagel on the Informix Forum....!

Did you try to ask the Informix forums about this error? if anyone has any suggestions? Please let us know if you are still stuck and we will try our best to help you!

Share via

Data factory error copying to informix

Your answer