Maddening Problem Importing Dates From Excel

Question

Afternoon, all -

I am about to take a sledgehammer to my laptop. For a week, I've been trying to get SSIS to import dates from an Excel sheet. Why on earth MS doesn't provide some way to turn off how SSIS samples the data to select the type is beyond me. Geez, just let me assign the data type and then LEAVE IT ALONE!!

<pant pant>

Anyway, I have a datasheet with only about 5% of the rows in a date column populated. Those that are populated are formatted as Date, empty cells as General. The format of the dates is, "d/m/yyyy." The Day and Month fields are not padded with 0; the date is January 1, 2021, it is "1/1/2021."

Because the vast majority of cells in the column are empty, SSIS forces the data type to WSTR which then seems force SSIS to import the date CODE, not the date value, so I'm guessing that SSIS is receiving "44197" instead of "1/1/2021." At least, that's all I can figure is happening, because an SSIS expression attempting to transform the WSTR to DBDATE refuses to work, giving me a transformation error.

So, I converted the column of data in the Excel sheet to strings using TEXT() with a format of "mm/dd/yyyy." They convert fine and I format the entire column as Text. So, SSIS receives a text value for every cell in the column. I tried a DBDATE transform on the data and still an error.

I tried Copying the column and Pasting it as Values, then setting the entire column to Text, but still with unpadded Day and Month values. I then wrote the following formula in the SSIS Derived Column task;

ISNULL(Last_Spec_Visit_Dt) ? NULL(DT_DBDATE) : Last_Spec_Visit_Dt == "" ? NULL(DT_DBDATE) : (DT_DBDATE)(REVERSE(LEFT(REVERSE(Last_Spec_Visit_Dt),4)) + "-" + RIGHT("00" + LEFT(Last_Spec_Visit_Dt,FINDSTRING(Last_Spec_Visit_Dt,"/",1) - 1),2) + "-" + RIGHT("00" + SUBSTRING(Last_Spec_Visit_Dt,FINDSTRING(Last_Spec_Visit_Dt,"/",1) + 1,FINDSTRING(Last_Spec_Visit_Dt,"/",2) - FINDSTRING(Last_Spec_Visit_Dt,"/",1) - 1),2))

Still got the error.

I am at a loss and I'm sure I've missed some important detail here as my brain is quite frazzled trying to get this to work.

What is an easy way to import dates from Excel to SSIS, where, due to the lack of actual date values, SSIS samples and determines the type to be WSTR?

Can anyone help out on this?

Thanx in advance for any help.

Answer 1

Hi @Adam Quark ,

MS Excel is electronic paper which accepts any scribbles. It doesn't have a notion of a data type.

Please try to format Excel data in Excel as follows: yyyy-MM-dd, i.e. 2021-07-14.
It follows the ISO 8601 standard for dates: iso-8601-date-and-time-format

After that SSIS should be able to load such data.

Answer 2

Hi @Adam Quark ,

It seems that why it show "44197" is that the excel code for general like shown.

Like YitzhakKhabinsky-0887 said that format the data as Date and try again.

Below is my test, it works well in my side.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October