Import decimal with comma

Question

On SQL Server, I’m trying to run a SELECT in a *.csv file as follows:

SELECT *
FROM OPENROWSET(BULK 'C:\Minha_Pasta\meu_arquivo.csv',
                FORMATFILE = 'C:\Minha_Pasta\format.xml') AS Contents

meu_arquivo.csv is as follows:

1;Joãozinho;172,1
2;Mariazinha;163,5

format.xml is like this:

<?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="1"   xsi:type="CharTerm" TERMINATOR=";" />
        <FIELD ID="2"   xsi:type="CharTerm" TERMINATOR=";" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD ID="3"   xsi:type="CharTerm" TERMINATOR="\r\n" />
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1"  NAME="ID"      xsi:type="SQLBIGINT"    NULLABLE="YES" />
        <COLUMN SOURCE="2"  NAME="Nome"    xsi:type="SQLNVARCHAR"  NULLABLE="YES" />
        <COLUMN SOURCE="3"  NAME="Altura"  xsi:type="SQLDECIMAL"   NULLABLE="YES" />
    </ROW>
</BCPFORMAT>

The problem is that the decimal value is being separated by a comma instead of a dot. Is there any way to solve this in format.xml with something like COLLATION without having to change the file manually? Unfortunately, from what I saw COLLATION applies only to texts.

Answer 1

It is possible to settle with replace:

SELECT *, CAST(REPLACE(AlturaOrig, ',', '.') AS DECIMAL(5, 1)) AS Altura
FROM OPENROWSET(BULK 'C:\Minha_Pasta\meu_arquivo.csv',
                FORMATFILE = 'C:\Minha_Pasta\format.xml') AS Contents

With this format.file:

<?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="1"   xsi:type="CharTerm" TERMINATOR=";" />
        <FIELD ID="2"   xsi:type="CharTerm" TERMINATOR=";" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD ID="3"   xsi:type="CharTerm" TERMINATOR="\r\n" />
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1"  NAME="ID"      xsi:type="SQLBIGINT"    NULLABLE="YES" />
        <COLUMN SOURCE="2"  NAME="Nome"    xsi:type="SQLNVARCHAR"  NULLABLE="YES" />
        <COLUMN SOURCE="3"  NAME="AlturaOrig"  xsi:type="SQLCHAR"   NULLABLE="YES" />
    </ROW>
</BCPFORMAT>