Hi ​@Marcodenijs 

Which data source are you using?

This one uses:

Provider name:

Microsoft Excel

Provider type:

Managed ADO.NET

Provider version:

24.0.9033.0

​@Marcodenijs can you please share a sample excel file? either here or if you could please send to support@timextender.com

File sent! (via email)

Hi ​@Marcodenijs 

The issue is the source. When you are using a CData Excel provider this one will first try to convert it to a date and this fails. It doesn’t get to the part where it attempts to convert the field.

You may be able to handle it by setting up culture settings.

A better option would be to use our own TX Excel provider

https://support.timextender.com/data-sources-112/timextender-excel-data-source-2181?tid=2181&fid=112#Date+formats

We have a setting where you can state the date format, for example yyyy-MM-dd. This will then either change the setup or make it see the field as a nvarchar.

Hi ​@Marcodenijs 

There are some records with different date formats in the excel file (see row 17769 in the excel file)

The Cdata excel provider has the type detection scheme set to ColumnFormat by default since the column format is set to date in the excel file, the provider determines that the data type should be datetime

If I change this the type detection scheme to row scan and set the row scan depth property to 0 then the provider will scan all rows in the column to determine the data type. After making these changes, and running the import metadata task and synchronizing

This will make the field varchar(2000) when you bring it into the prepare instance

If you try to change the type to datetime in your prepare table it will fail with the following error (similar to before)

So there are 3 options to fix this:

1. Fix the data in the file so that the dates are inputted in correct format
2. Create a query table for the ingest data source to convert records formatted like 25-mei-1991 to have the same format as other dates in the file MM/DD/YYYY (i.e. 05/25/1991)
3. columns will be ingested as text in the data source and the transformation from text to datetime will be done in the prepare instance.

Solution 2:

Query table with data type override

SELECT 
    >Geboorte Datum],
    CASE
        -- If the value is already a valid date in MM/DD/YYYY format, return it as is
        WHEN ISDATE(DGeboorte Datum]) = 1 THEN 
            FORMAT(CAST(CGeboorte Datum] AS DATE), 'yyyy-MM-dd')

        -- If the value is NULL, return NULL
        WHEN WGeboorte Datum] IS NULL THEN 
            NULL

        -- Otherwise, apply the transformation logic for Dutch month names and format it
        ELSE
            FORMAT(
                CAST(
                    CONCAT(
                        RIGHT(IGeboorte Datum], 4), -- Extract the year
                        '-',
                        CASE
                            WHEN CHARINDEX('-mei-', iGeboorte Datum]) > 0 THEN '05'
                            WHEN CHARINDEX('-jan-', nGeboorte Datum]) > 0 THEN '01'
                            WHEN CHARINDEX('-feb-', bGeboorte Datum]) > 0 THEN '02'
                            WHEN CHARINDEX('-mrt-', tGeboorte Datum]) > 0 THEN '03'
                            WHEN CHARINDEX('-apr-', rGeboorte Datum]) > 0 THEN '04'
                            WHEN CHARINDEX('-jun-', nGeboorte Datum]) > 0 THEN '06'
                            WHEN CHARINDEX('-jul-', lGeboorte Datum]) > 0 THEN '07'
                            WHEN CHARINDEX('-aug-', gGeboorte Datum]) > 0 THEN '08'
                            WHEN CHARINDEX('-sep-', pGeboorte Datum]) > 0 THEN '09'
                            WHEN CHARINDEX('-okt-', tGeboorte Datum]) > 0 THEN '10'
                            WHEN CHARINDEX('-nov-', vGeboorte Datum]) > 0 THEN '11'
                            WHEN CHARINDEX('-dec-', cGeboorte Datum]) > 0 THEN '12'
                        END,
                        '-',
                        LEFT(LGeboorte Datum], CHARINDEX('-', 'Geboorte Datum])) -- Extract the day
                    ) AS DATE
                ),
                'yyyy-MM-dd'
            )
    END AS DGeboorte Datum Converted]
FROM FActure dump_test.xlsx].lFactuurregels]

Solution 3:

first I created a column using a custom transformation to get rid of the time part (i.e. T00)

CASE
        -- If the value contains 'T00', truncate the string at 'T00'
        WHEN CHARINDEX('T00', 0Geboorte_Datum]) > 0 THEN LEFT(LGeboorte_Datum], CHARINDEX('T00', 0Geboorte_Datum]) - 1)
        -- Otherwise, return the value as is
        ELSE EGeboorte_Datum]
END

Then I added another column with the following custom transformation to convert values such as 25-mei-1991

    CASE
        -- If the value is already a valid date in MM/DD/YYYY format, return it as is
        WHEN ISDATE( AGeboorte_Datum2] ) = 1 THEN TGeboorte_Datum2]
        -- Otherwise, apply the transformation logic for Dutch month names
        ELSE
            CAST(
                CONCAT(
				RIGHT( GGeboorte_Datum2] , 4), -- Extract the year
				'-',
				CASE
                        WHEN CHARINDEX('-mei-', iGeboorte_Datum2]) > 0 THEN '05'
                        WHEN CHARINDEX('-jan-', nGeboorte_Datum2]) > 0 THEN '01'
                        WHEN CHARINDEX('-feb-', bGeboorte_Datum2]) > 0 THEN '02'
                        WHEN CHARINDEX('-mrt-', tGeboorte_Datum2]) > 0 THEN '03'
                        WHEN CHARINDEX('-apr-', rGeboorte_Datum2]) > 0 THEN '04'
                        WHEN CHARINDEX('-jun-', nGeboorte_Datum2]) > 0 THEN '06'
                        WHEN CHARINDEX('-jul-', lGeboorte_Datum2]) > 0 THEN '07'
                        WHEN CHARINDEX('-aug-', gGeboorte_Datum2]) > 0 THEN '08'
                        WHEN CHARINDEX('-sep-', pGeboorte_Datum2]) > 0 THEN '09'
                        WHEN CHARINDEX('-okt-', tGeboorte_Datum2]) > 0 THEN '10'
                        WHEN CHARINDEX('-nov-', vGeboorte_Datum2]) > 0 THEN '11'
                        WHEN CHARINDEX('-dec-', cGeboorte_Datum2]) > 0 THEN '12'
                    END,
					'-',
                    LEFT( EGeboorte_Datum2]  , CHARINDEX('-',(Geboorte_Datum2] ) - 1) -- Extract the day



                ) AS VARCHAR
            )
    END

Hey ​@Christian Hauggaard 

Thanks for the effort put in finding out what the issue is. 

Maybe I completely misunderstood, but is this not exactly what the override data type function in TX was supposed to do?

I know/knew there were wrongly formatted dates in the column, so I want TX to ignore the format and override the format and read it as text. 

That was my understanding of how the ‘override data type’ function was supposed to work. 

And in case not, what is it supposed to do?

edit: Just read ​@Thomas Lind his answer. 

I guess the override data type funcation simply does not work in the way I would expect it to work then. 

I did try the TX Excel provider, with multiple other excel/csv sources we have aswell. Per my experience it’s a real pain to get it to work. Every file I tried so far gave me numerous errors and simply took quite some time to setup. The Cdata provider always worked fine. 

This exact same file for example works fine with the Cdata excel connector in TX 20.10.x

Cheers,

Marco

​@Marcodenijs which type detection scheme are you using when you use the cdata provider?

I use row scan and have set the row scan depth to 0

With these setting the column is identified as varchar(2000)

If I try to convert to data type override I get an error, because of the different date formats in the file

If I try to edit the data type to date in the prepare table I also get an error

So it seems the Cdata provider is not handling these dates any differently than the TX provider with these settings.

Can you please send some screenshots of the settings you used in the cdata provider and the column data types identified in the metadata manager?

If I use rowscan and the rowscan set to 0 it does work indeed. All the columns are set to text. 

So this method does work, the only downside being I end up with all my columns as text and depending on the size of the table that can be annoying. 

Problem is solved I guess, I always just assumed the ‘override data type’ feature would work in a different way than it actually does.