Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,624 questions
How to parse DateTime using multiple format string in column mapping ADF copy activity.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 28.000000000000004%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAK%3C/text%3E%3C/svg%3E)
Abhishek Kumar
0
Reputation points
I want to parse multiple Datetime column with different format string from string to Datetime(interim) in ADF copy activity using custom mapping.
I am getting the below error in the very first column.
I am using the below mapping for it.
I don't want to use 'DateTime format' field for specifying the format string as I have multiple format strings, and I want to specify them for each column separately.
Below is the sample source data in csv format.
Azure Data Factory
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 38%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EQ%3C/text%3E%3C/svg%3E)
QuantumCache 20,366 Reputation points Moderator2024-02-09T17:45:04.8+00:00 Abhishek Kumar Looks like your Source data is not consistent with respect to the Date Format. What if it keeps changing the Date Format every time? the pipeline will fail, right? Can you please post here few examples of Sample Source data text file and expected Format?
-
Abhishek Kumar 0 Reputation points
2024-02-12T07:09:57.8333333+00:00 Hi StaishBoddu-MSFT,The date format for a column is constant. For example, column 1 has date format 'yyyyMMdd' and column 2 had date format as 'dd.yyyy.MM'. Below are some sample data. The input file is a csv file (comma separated).
-
QuantumCache 20,366 Reputation points Moderator
2024-02-12T08:33:04.4366667+00:00 @Abhishek Kumar Thanks for the sample data, will get back on this.
-
QuantumCache 20,366 Reputation points Moderator
2024-02-15T23:14:02.8566667+00:00 @Abhishek Kumar
Do you want the output in the below format? Below is the sample CSV data which i used.col1_date,col2_date> 20231123, 23.2023.05> 20220118, 01.2021.07> 20230719, 18.2022.12
-
QuantumCache 20,366 Reputation points Moderator
2024-02-21T03:50:50.2066667+00:00 @Abhishek Kumar Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
Sign in to comment
Sign in to answer