bcp and regional settings not working for "decimal comma"

Last Post 04 Jan 2013 01:47 PM by gunneyk. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
MS
New Member
New Member

--
03 Jan 2013 11:03 AM
In English, the decimal symbol is ".". In european languages such as Germam/italian, it is ",".

Even though BCP has an option (-R) to use regional settings, the decimal symbol is not taken into account even when using this setting to load DECIMAL and/or FLOAT data.

One would expect that an option to "use regional settings" would use ALL relevant regional settings.

Even though BOL mentions implicitly that this is not available ("-R Specifies that currency, date, and time data is bulk copied into SQL Server using the regional format defined for the locale setting of the client computer.", http://msdn.microsoft.com/en-us/lib...802.aspx), this still seems like missing /not supported.

So far I have come across known bug/hot fix for SQL Server 2000, but seems like the problem still persist in SQL 2005 or higher.
-Possible Hotfix for the -R support
http://support.microsoft.com/kb/292648

BCP with format file and -R ignores regional settings
http://support.microsoft.com/kb/293155


Does anybody know of any hot fixes /solution that would apply in this scenario that applications can leverage to workaround this problem?
Is there any option to prep the format files such that it can process the decimal "comma" properly, as it should be handled automatically by BCP/SQL Server.

Thanks
Mitesh
gunneyk
New Member
New Member

--
04 Jan 2013 09:30 AM
I am not aware of that excat issue or any fixes for it however I want to make a few suggestions. First off have you tried using BULK INSERT instead of bcp and see if that works any better? Also what about using SSIS or the Import Wizard instead of bcp? And finally you might be able to prep the files by using a REGEX expression to find and replace the commas with decimal points.
MS
New Member
New Member

--
04 Jan 2013 10:00 AM
Thanks for the suggestions. We are in the process of exploring other techniques, but in this scenario, BCP is our only option. It seems that there are some challenges with BCP and editing/replacing the source data file is not feasible in our use case.


Btw, I also came across the following BUG regarding bcp support of regional setting (-R) in SQL Server 2012 (which is the most current release)
http://connect.microsoft.com/SQLSer...ol#details

Has anyone seen this or is there a way to confirm it from Microsoft?



gunneyk
New Member
New Member

--
04 Jan 2013 10:15 AM
Why not import that column as a string and then do the necessary conversions in the table? Once it's in the table it should be easy to transform to what ever you need.
MS
New Member
New Member

--
04 Jan 2013 11:03 AM
Yes, there are other options to manipulate the data before or after, but in this case we are trying to get it working with BCP as it is supposed to handle it via the regional settings option(-R). We don't have much choice other than BCP at this point. So it would help if there's a way to confirm if the SQL Server BUG is for real and if there are any hot fixes available similar to what they did with SQL Server 2000 in the past.
gunneyk
New Member
New Member

--
04 Jan 2013 01:47 PM
Well you could open a case with MS and they can give you an official answer one way or the other.


Acceptable Use Policy
---