BULK Import from a file with decimal values

• gofrancesc

  Hall of Fame

  Points: 3269

  More actions

  March 22, 2011 at 3:23 am

  #248524

  Hi all,

  I have a question about reading data from a file. My input file contains decimal numbers and, in my country, the decimal separator is a comma, this creates a problem reading the file.

  I tried to open my file with this instruction

  SELECT * FROM OPENROWSET (

  BULK 'myFile', FORMATFILE = 'myFormatFile') AS A

  and this formatfile

  <?xml version="1.0"?>

  <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <RECORD>

  <FIELD ID="UTMZ" xsi:type="CharFixed" LENGTH="7"/>

  </RECORD>

  <ROW>

  <COLUMN SOURCE="UTMZ" NAME="UTMZ" xsi:type="SQLDECIMAL" PRECISION="5" SCALE="1"/>

  </ROW>

  </BCPFORMAT>

  but it fails when reading a comma as a decimal separator. Then I tried to read it in this way,

  SELECT *, CAST(REPLACE(UTMZ_char, ',', '.') AS DECIMAL(5, 1)) AS UTMZ FROM OPENROWSET (

  BULK 'myFile', FORMATFILE = 'myFormatFile') AS A

  with this formatfile

  <?xml version="1.0"?>

  <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <RECORD>

  <FIELD ID="UTMZ" xsi:type="CharFixed" LENGTH="7"/>

  </RECORD>

  <ROW>

  <COLUMN SOURCE="UTMZ" NAME="UTMZ_char" xsi:type="SQLCHAR"/>

  </ROW>

  </BCPFORMAT>

  and it works well.

  My question is: does anyone know how to instruct SQL Server to use comma as a decimal separator?.

  Thanks in advance,

  Francesc

• Uripedes Pants

  Hall of Fame

  Points: 3701

  More actions

  March 23, 2011 at 10:53 am

  #1302603

  well, this is untested but nobody else has taken a stab at it.... you could try the "Regional and Language settings" applet in control panel. It allows you to set your number and currency styles. I believe that will require a restart of windows for it to take effect.

  also, a google search with these keywords

  SQL Server use comma as decimal separator

  returned approx 220K results

  K

• chris.stuart

  SSCarpal Tunnel

  Points: 4965

  More actions

  March 24, 2011 at 2:01 am

  #1302816

  As Uripedes Pants said, take a look at the reginal settings, find 1 which uses the decimal and set it to the standard on your pc and restart and try the import again.

• gofrancesc

  Hall of Fame

  Points: 3269

  More actions

  March 25, 2011 at 3:40 am

  #1303439

  ha ha, I'm sorry, a Google search was an option that I didn't think about.

  Changing the regional settings doesn't work, my machine works with spanish settings and this machine is also my server. And a Google search says there is no solution.

  Many thanks for your hints,

  Francesc