Date converting to number on import from Excel to

Question

I am trying to import a table from Excel into SQL Server and there is one field that sometimes contains a date (mm/dd/yyyy) and sometimes contains a date and text (mm/dd/yyyy notes).

When I use the import wizard the rows that just have a date are converted to a five digit number (#####). The rows that have the date and text appear normally. There is another field that is only a date and that imports normally as datetime.

I also tried to write a import statement in excel like this:

="Import into MyTable values('"&A2&"','"&B2&"', [...] )"

But that also formatted the date as a five digit number.

Does anyone have a suggestion to import this field and preserve the date format? If that is not possible then can I convert from the five digit number back to a date?

Answer 1

You could try this:

1. First export the excel file as Text with Tab delimiter using Save As:

   

2. Then open up SQL Server and in the import wizard, pick Flat File (that is the name SQL uses for raw text files, CSVs, etc):

   

After that, just make sure that you have the right things set:

I highlighted the parts that are usually important during the import.