CODEWITHSUNDEEP
sql-serverexcelt-sqlimport
Pat
Pat

Reputation: 310

Date converting to number on import from Excel to SQL Server

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?

Upvotes: 2

Views: 5112

Answers (1)

Jerry
Jerry

Reputation: 71538

You could try this:

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

    enter image description here

  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):

    enter image description here

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

enter image description here

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

Upvotes: 3

Related Questions