Reading decimal mark comma with an external table

by SNR   Last Updated January 13, 2018 16:06 PM

SQL-server 2016, SQL server management studio (SSMS). Creating external tables with transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot?

When using float and decimal it fails and throws an error. When using money data type it succeeds, but misread decimals and integers are returned instead.

Revised the file format standard clause, no option to configure such a thing. Collation already set for my region.

Sample data:

2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;1.000,55
2017;4;2017;601PPP;183,63;2,5
2017;4;2017;601PPP;183,63;7,5
2017;4;2017;601PPP;405,28;17,5

Example of a create table that would fail to read the sample data:

CREATE EXTERNAL TABLE [dbo].[STG_Table] (
    field1           smallint     NULL
   ,field2           tinyint      NULL
   ,field3           smallint     NULL
   ,fieldn_2         varchar(10)  NULL
   ,fieldn_1         money        NULL
   ,fieldn           float        NULL
)
WITH (LOCATION='/STG_Table/',
    DATA_SOURCE = AzureDataSource, 
    FILE_FORMAT = FileFormat
);

Thanks in advance!!



Related Questions