0

I need help in following-

Old CSV file has 3 columns- id, fname, lname

New CSV file has 4 columns- id, fname, lname, dob

I have u updated the external schema as below-

CREATE EXTERNAL TABLE [dbo].[employee]
(
    [id] [int] NOT NULL,
    [fname] [nvarchar](50) NULL,
    [lname] [nvarchar](50) NULL,
    [dob] [datetime2](7) NULL
    
)
WITH (DATA_SOURCE = [EmpWarehouseStorage],LOCATION = N'Employee/New',FILE_FORMAT = [PipeFileFormat],REJECT_TYPE = VALUE,REJECT_VALUE = 10)

External Data source Query-

CREATE EXTERNAL DATA SOURCE [EmpWarehouseStorage] 
WITH (TYPE = HADOOP, LOCATION = N'abfss://emp@****.dfs.core.windows.net', 
CREDENTIAL = [EmpWarehouseStorageKey])

External File Format -

CREATE EXTERNAL FILE FORMAT [PipeFileFormat] WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', USE_TYPE_DEFAULT = False))

Querying external table throws the error -

1*** rows rejected from external table [Employee] in plan step 2 of query execution: Location: 'Employee/New/Employee_2***.csv' Column ordinal: 79, Expected data type: datetime2.

Just for debugging purposes, I have increased the REJECT_VALUE = 10000. I get to know that external shows only the new files data and does not show old files data.

Sagar Malandkar
  • 49
  • 7
  • The error seems to hint that the datatype in the file does not match a valid datetime2 datatype. Can you supply an example of a data column (anonymized if necessary)? – SchmitzIT May 07 '21 at 10:55
  • Hi @Sagar Malandkar, is the error solved now? If my answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you. – Leon Yue May 12 '21 at 01:08

1 Answers1

0

Just from the error message, it seams that the column data of dob can not be converted to data type datetime2(7).

We don't know what string your dob data look like, please check it follow this link:Supported string literal formats for datetime2.

So the solution could be that change the datetime2 to other date data type.

Update:

Glad to hear you found the cause:

  • It is not just because of datetime2Actually it is related with any data type. As previous files doesn't have that column it is considered as error data.
Leon Yue
  • 15,693
  • 1
  • 11
  • 23