Import decimal with comma

Question

Import decimal with comma

Navigation

#1 by (2 votes)

0

In SQL Server, I am trying to execute 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 looks 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 rather than a period. Is there any way to resolve this in format.xml with something like COLLATION without having to manually change the file? Unfortunately, for what I saw COLLATION only applies to texts.

sql sql-server xml decimal csv

asked by anonymous 23.02.2017 / 22:26

1 answer

Delete or change sql data using php How to protect against a malicious entry through a select dropdown?

score 2 · Accepted Answer

Can be resolved 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>