2

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 misreads 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
);

Query used to read data:

SET @dynamicSQL = N'SELECT * INTO ##' + @someTable + ' FROM STG_' + @someTable 
EXEC sp_executesql @dynamicSQL 

Specifying fields implies more cases to be added (working inside a procedure) and it can be avoided by configuring such a thing. I found more simple configuring this, than adding more code. No configuration found in database configuration.

0

1 Answer 1

1

It seems that interpreting '1.000,55' as a float isn't simple, but as a numeric is possible. From that answer

select try_parse('1.000,55' as numeric(10,2) using 'es-ES') as x

x
-------
1000.55

I've tried every which way with other target data types. None work. Obviously a CAST() can be wrapped around the try_parse to get the desired final type.

So what I'd suggest is you define your external table with the difficult columns as varchar(n). Then define a view which reads the external table and uses try_parse to convert the difficult columns to the desired data type. This view is accessible to the application and end users; the table is visible only to the view.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.