To homepage To homepage
Question

Read Excel decimal data with CDATA

  • 14 March 2023
  • 6 replies
  • 55 views
L
Rank

I have an Excel sheet with percentages (11 decimals). The decimal sign is comma. See example below.

Year    Period    CostPercentage
2020    1    0,01800188
2020    2    0,016852519

But when I read the data in TX then I see the below result: 
 

 

I have tried to use the culture feature see below but no success. Find a screen dump below

Why does TX / Cdata not read the data as is? The “,” is interpreted as a Thousand separator.

Any help will be appreciated.

When I have a file with a “.” (dot) as a decimal sign I convert the data to numeric precision 38,scale 11 and that works.

 

6 replies

erik.van.mastrigt
Rank
Badge

You can try to use the Data Type Override function and set the data type to float (data type Numeric, check ‘Is Float’).

In most cases this is how I solve issues with thousand/decimal separators as well as scientific notation.

https://legacysupport.timextender.com/hc/en-us/articles/115002775923-Using-the-Override-Data-Type-feature

rory.smith
Rank
Userlevel 3
Badge +2

@Lorenzo ,

is your CData Excel version the most recent for the version of TimeXtender you are running? There have been quite some improvements lately.

What region / locale settings does your server or user have? Have you tried experimenting with the TypeDetectionScheme  and EnableOleDbCompatibility settings to see if that detects the column types differently?

Your culture and client-culture settings should perform the conversion you expect, so that is worth diving into.

L
Rank

Unfortunately nothing works using CDTA Excel when you have comma as decimal and the server is en-US. When you use a CSV file however (and CDATA csv) then it works without any problems.

Does this means that TX (CDATA) is not able to process an Excel file with comma as decimal?

Christian Hauggaard
Rank
Userlevel 4
Badge +5

Hi @Lorenzo 

With the attached excel file, I am able to return the decimal value despite a comma as the decimal sign and my PC being en-US culture.

I connect to the file using the Cdata Excel provider using all the default settings.

Then I create a Query Table by right clicking on my data source and selecting Manage Query Tables using the following SQL statement, which replaces the comma with a dot, and then casts as float.

SELECT [RowId], [Year], [Period], 
CAST(REPLACE([CostPercentage],',','.') AS FLOAT) AS [CostPercentage] FROM [Excel].[Sheet1]

Then I execute the synchronize and transfer task for my data source. 

When I right click on my data source and select Data Source Explorer to view the data, it appears correct.

Then I bring in my table into my DW and edit my field to include 11 decimals

Alternatively you can bring the data in as text into the ODX storage (skip the query table) and then edit the field in the DW to include 11 decimals, this seems to work as well.

 

1 Attachment
rory.smith
Rank
Userlevel 3
Badge +2

Hi Christian,

the workaround is nice - but you would expect it should not be required (given certain conditions).

 

I guess if your data is Dutch culture in Excel and your server uses the American culture settings, Excel will not recognize 1000,2 as a number representing 1000 and two tenths. Then the culture settings will not allow for a conversion. I think if your data culture and server culture match, you should be able to modify the data going into TX.

This is partially an Excel issue, but quite thorny if you are an international enterprise without a centralized culture/regional setting.

It would be good to verify that when the Excel file's culture and the server culture match, you can successfully convert going into TX.

 

Christian Hauggaard
Rank
Userlevel 4
Badge +5

@Lorenzo @rory.smith 

It works for me when I select the whole column (i.e. click on column C) and then change the format to number. It is important to select and format the whole column and not just the values / individual cells. After I executed the synchronize task again, the data type was returned as float.

 

Reply


Terms and ConditionsCookie settings