Excel to Parquet - Dates are null for few columns

Question

Excel to Parquet - Dates are null for few columns

Santhi Dhanuskodi 66

Hi,

I am getting null values for date columns when I am converting from excel to parquet.
My source excel contains proper dates. I dont see any empty or null values in date columns.

And surprisingly I see these null values only for fewer records.

For example, if i have 10 records with date values '2021-01-01'.... I get '2021-01-01' for 5 records.
And I get null values for another 5 records.

I am using toDate(Date, 'yyyy-mm-dd')... even then this problem occurs. Please help me on this

Santhi Dhanuskodi 66 Reputation points

2022-10-11T06:53:43.053+00:00

In Data preview of excel and Parquet, there is no null records seen.
Only during sink to db table, the data is stored as null.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2022-10-11T15:18:54.807+00:00

Hello @Santhi Dhanuskodi ,

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

The issue sounds strange as you mentioned For example, if i have 10 records with date values '2021-01-01'.... I get '2021-01-01' for 5 records. And I get null values for another 5 records. When your source data contains dates in the same format for all records, then either all should be mapped as date or null whereas in your case it seems half null and half actual values.

To better troubleshoot or understand the problem, could you please attach a sample file of your source ( Please remove any sensitive data in the sample file and rename extension of file from .xlsx to .txt so that you can attach here) And also please do share a screenshot of your source schema projection and inspect tabs to understand the datatypes of your source columns as well as please share schema/data types of your sink datastore as well .

We look forward to your response.

Thank you

2 answers

Your answer

Santhi Dhanuskodi 66 Reputation points

2022-10-11T06:53:43.053+00:00

In Data preview of excel and Parquet, there is no null records seen.
Only during sink to db table, the data is stored as null.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2022-10-11T15:18:54.807+00:00

Hello @Santhi Dhanuskodi ,

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

The issue sounds strange as you mentioned For example, if i have 10 records with date values '2021-01-01'.... I get '2021-01-01' for 5 records. And I get null values for another 5 records. When your source data contains dates in the same format for all records, then either all should be mapped as date or null whereas in your case it seems half null and half actual values.

To better troubleshoot or understand the problem, could you please attach a sample file of your source ( Please remove any sensitive data in the sample file and rename extension of file from .xlsx to .txt so that you can attach here) And also please do share a screenshot of your source schema projection and inspect tabs to understand the datatypes of your source columns as well as please share schema/data types of your sink datastore as well .

We look forward to your response.

Thank you

Answer 1

Santhi Dhanuskodi 66

We found out that some other columns which contain numbers, decimals were not in correct format.
We had to edit source excel files , those numeric columns formats. Then it worked out.

But it is surprising how Azure behaves on these kind of issues.
It is tiresome investigation to root cause such data format issues.

KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2022-10-19T06:20:06.233+00:00
Hello @Santhi Dhanuskodi ,

Glad to know that your issue has been resolved. If you have any specific feedback regarding the product improvements, we encourage you to please log them in Azure IDEAS forum here: https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c. All the feedback shared in this forum are actively monitored and reviewed by respective product owners.

Please do share the URL of the feedback once it is logged so that I shall up-vote it and share it with internal teams for further review.

Thank you

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Answer 2

The only situation which results in such scenario is when your source records had inconsistent date formats.

For example: If your source has inconsistent date format column (few records of format dd-MM-yyyy, MM-dd-yyyy, yyyy-MM-dd ), and if you use the toDate function as toDate(Date, 'yyyy-MM-dd'), then the function expects your source to pass the value of that column to be of format yyyy-MM-dd and if any of your source records contain a different format like dd-MM-yyyy or MM-dd-yyyy then those records will be treated as null.

I would suggest looking at the sink records that have null for date column and verify those records with your source to make sure that the date format is consistent as per the format defined in the toDate() function

Hope this info helps. Please do keep us posted on your findings and if this doesn't help then please share a sample file with some good and bad (null value) records.

KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2022-10-17T20:12:25.097+00:00

Hello @Santhi Dhanuskodi ,

Following up to see if the above information was helpful. If yes, then could you please confirm if you were able to figure out if the source date formats are consistent for all records or varies for some records?

Please let us know if you have further questions. Thank you

Share via

Excel to Parquet - Dates are null for few columns

2 answers

Your answer