Importing Excel File into SQL the dates are coming up NULL

Transact-SQL
1

I have an excel file I need to import into SQL. Looks like there are a few records that are in General format 7/10/2015 and the others are in date format, which still look like 7/10/2016. However, the ones that are in date format when imported into SQL they come up null.

How can this be fixed? I am not sure what to do.

2

Try to change the format of date in excel and then import

3

Hmmm ... not sure that I have any good ideas. Looks like some of the values/cells in Excel are in TEXT and others are in DATE data type.

My thought would be to select the column, change the Format to a different date style - e.g. dd-Mmm-yyyy or Mmm-dd,yyyy and then see which rows/cells do NOT change to that format. Those are the ones that are in TEXT rather than DATE data type. I would then rekey / convert them ... but if there are lots of columns with this problem, and/or lots of rows/cells, then that may not be viable.

Perhaps you could insert a new column with a formula to CONVERT the existing column's values to DATE and then yet-another-column into which you copy from the new calculated column and PASTE SPECIAL using VALUES. That should get you a column of TRUE date datatypes. Then you could delete the original column, and the calculated column.

An alternative would be to import into a temporary table with all the columns set to be something like VARCHAR(8000), and then "convert" from that into your actual target table. When we do this we add a column to the TEMP table called ErrorMsg and do things like:

UPDATE MyTempTable
SET ErrorMsg = NULLIF(ErrorMsg+'. ', '') + 'Illegal date in Col1'
WHERE IsDate(Col1) = 0 AND Col1 IS NOT NULL

and then we print all the rows with any ErrorMsg and get the user to fix the original data, and re-export. Or we import just the "clean" rows. Or we fix the goofy data in the Temp Table and run the test/import again

4

What are you using to do the import? Will it take the IMEX=1 directive? If so, use it and bring everything in as text and let the target table do the conversion for you.

5

Thanks everyone for your help, I just manipulated the data in Excel =TEXT(A2,"mm/dd/yyyy"), Then I imported the data.