question

KimAveryadmin-9674 avatar image
0 Votes"
KimAveryadmin-9674 asked PRADEEPCHEEKATLA-MSFT commented

CSV File Loads Dates as Null values Azure Data Factory

While loading CSV files with dates formatted like 6/14/2007, if the Azure SQL table field allows Null values, then the date fields load, but as Null and not the date value. If the field is Not Null regardless of datatype I get an error that the field does not allow null values. I've Googled and tried adding a derived column and putting several different versions of the following code: toString(toTimestamp(<Your_Column_Name>,'MM/dd/yyyy HH:mm:ss'),'yyyy-MM-dd HH:mm:SS') but always return null values. Also after Googling I've tried changing the format in the Source Projection tab to yyyy-MM-dd and I still get the error that I can't load Null values although there are no Null values. It appears that I'm not the only one who has had this problem. Please advise.
I'm using Azure Data Factory

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you PCHEEKATLA-MSFT. I have used the Derived column transformation. FYI...when I attempted to write the expression toDate($$,'yyyy-MM-dd') I get the error that toDate or toTimstamp does not accept 'any'. Referring to the $$. I can only write toDate(toString($$,'yyyy/MM/dd'),'yyyy-MM-dd') Any advice? I'm still working on this and I will update you on my progress.

0 Votes 0 ·

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @KimAveryadmin-9674,

Thanks for the question and using Microsoft Q&A platform.

Use the derived column transformation to generate new columns in your data flow or to modify existing fields.

In ADF, you can use the toDate expression language to convert input data string to date.

toDate expression: Converts input date string to date using an optional input date format. Refer to Java's SimpleDateFormat for available formats. If the input date format is omitted, default format is yyyy-[M]M-[d]d. Accepted formats are :[ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ]

Examples:
1. toDate('2012-8-18') -> toDate('2012-08-18')
2. toDate('12/18/2012', 'MM/dd/yyyy') -> toDate('2012-12-18')

98449-image.png

95169-image.png

98525-adf-string2date.gif

For more details, refer Derived column transformation in mapping data flow.

Hope this helps. Do let us know if you any further queries.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.


image.png (125.9 KiB)
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @KimAveryadmin-9674,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hello @KimAveryadmin-9674,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Take care & stay safe!


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

0 Votes 0 ·

Not sure where I should write the comment. I'll attempt the derived comment again. Also, when I write toDate($$,'yyyy-MM-dd') in the expression I get an error that refers to the $$ and toDate does not accept 'any'. I don't have the exact error message currently but will update you. I can only start with toDate(toString($$,'yyyy/MM/dd'), 'yyyy-MM-dd') 99555-derivedcolumn-1.png


0 Votes 0 ·
derivedcolumn-1.png (38.9 KiB)

Attached is the error I get: toDate does not accept a parameter of type any 99878-derivedcolumn-2.png


0 Votes 0 ·
derivedcolumn-2.png (108.6 KiB)

Hello @KimAveryadmin-9674,

As explained on the above answer, toDate() function will accept date string and format string. So we should make sure to pass string values only.

The below expression will work in your case because here we are converting value to string first and then passing in to toDate() function.

 toDate(toString($$,'yyyy/MM/dd'),'yyyy-MM-dd')

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

0 Votes 0 ·
Show more comments