locked
Importing date column to sql server from excel RRS feed

  • Question

  • Hi
    I have excel sheet which has dates from 1800-2000 in date column. When i am importing to SQL server , it is working fine till 1899 year. From 1900 year it is displaying as NULL. Even i check with the data viewer, it is showing  as null from excel source. if i open excel sheet  it is showing all years properly. Please let me know hot to get all the data from excel.
    Thank you
    Tuesday, January 26, 2010 7:56 PM

Answers

  • You need to modify how you're treating the Excel provider.  Read through these articles (from the bottom up): Douglas Laudenschlager's posts on Excel.  Likely, you need to use the IMEX=1 setting - but you may find an easier answer in there.
    Todd McDermid's Blog
    Tuesday, January 26, 2010 9:12 PM
  • Here is the string

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;"
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    • Marked as answer by pradeepz_reddy Tuesday, January 26, 2010 10:52 PM
    Tuesday, January 26, 2010 9:30 PM

All replies

  • You need to modify how you're treating the Excel provider.  Read through these articles (from the bottom up): Douglas Laudenschlager's posts on Excel.  Likely, you need to use the IMEX=1 setting - but you may find an easier answer in there.
    Todd McDermid's Blog
    Tuesday, January 26, 2010 9:12 PM
  • Here is the string

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;"
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    • Marked as answer by pradeepz_reddy Tuesday, January 26, 2010 10:52 PM
    Tuesday, January 26, 2010 9:30 PM