Skip to main content
Question

Problem with numeric delimeter

  • November 29, 2014
  • 6 replies
  • 58 views
K
Hi,

 

 

I use FME 2014, and tried with quick translator to migrate popular AdventureWorks MS database to PostgreSQl server.

 

 

First I created PG database: 
 CREATE DATABASE adventure_works_2012   WITH OWNER = postgres        ENCODING = 'UTF8'        TABLESPACE = pg_default        LC_COLLATE = 'C'        LC_CTYPE = 'C'        CONNECTION LIMIT = -1;
 Then I added neccessary schemas, to allow FME Quick Translator do it's job.

 

 

However I get this error: 
 Bulk copy failed on table 'Production.Product' using delimiter ':'. Error was 'ERROR:  invalid input syntax for type numeric: "2,24" CONTEXT:  COPY Product, line 210, column weight: "2,24" '
 and I don't know where could this come from. Looking inside database in SSMS I don't see such separot on the field, it's "2.24", and my regional setting also doesn't provide comma as decimal separator, so I don't know where does FME pulls this value and makes process fail.

 

 

Thanks

 

 

 

 

6 replies

takashi
Influencer
  • takashiInfluencer
  • November 30, 2014
Hi,

 

 

I think FME usually uses a proper symbol for a decimal point according to locale setting of the system.

 

How about using the Logger or the Inspector to specify where the change has occurred?

 

 

Takashi

 

david_r
Celebrity
  • david_rCelebrity
  • December 1, 2014
Hi,

 

 

decimal separators can also be influenced by client libraries, so they're not always so easy to debug. The database locale settings are not always taken into account on the client side, as the client sometimes transforms data between the client and server locale for you.

 

 

The easiest solution is probably to use a StringReplacer on your float attributes before the writer.

 

 

David
S
Forum|alt.badge.img
  • stian
  • January 8, 2018

Like to revive this again because I'm seeing the same issue. Manage to translate fine on FME 2017 on both Windows and Linux with "." as decimal delimiter, but it fails from FME Server 2017 running on Linux with the log outputting same as klo mentions above. All machines has locale set to nb_NO.utf8 since I'm in Norway. Any hints would be much appreciated!

lau
Forum|alt.badge.img+3
  • lau
  • January 8, 2018
stian wrote:

Like to revive this again because I'm seeing the same issue. Manage to translate fine on FME 2017 on both Windows and Linux with "." as decimal delimiter, but it fails from FME Server 2017 running on Linux with the log outputting same as klo mentions above. All machines has locale set to nb_NO.utf8 since I'm in Norway. Any hints would be much appreciated!

The string replacer mentionned by David cannot help you?

 

 

S
Forum|alt.badge.img
  • stian
  • January 9, 2018
lau wrote:
The string replacer mentionned by David cannot help you?

 

 

Actually, I tried that as well. Tried both the PostgreSQL writer and the Feature writer with Postgresql capabilities without any luck unfortunately.

 

 

helmoet
Forum|alt.badge.img+8
  • helmoet
  • April 23, 2018

Hi guys, not sure if this works for you, there appears to be a tool DISM withing CMD.exe that allows for changing the locale (though in my experience FME does not answer quite well to decimal point or comma issues using locale). However, might be something I'm not doing the right way, I use a stringreplacer and that works fine.

Reply


Helpful Members This Week

Community Stats

31,341
Posts
118,864
Replies
38,931
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.