locked
Importing data from Excel 2007 to SQL Server 2005. RRS feed

  • Question

  •  

    Hi All,

     

    Till now I am using Excel 2003 as an excel source to import my data to SQL Server 2005. And the Application is doing fine enough.

    Now I have been asked to import data using Excel 2007. And as per my knowledge SSIS do not support Excel 2007.

    If anybody knows the solution of my problem please reply as soon as possible.

     

    Thanks and Regards

    RG

     

    Tuesday, July 22, 2008 1:14 PM

Answers

  • (deleted some words here...)

    SQL Server 2008 (will released sonly) including its CTPs support Excel 2007.

    Tuesday, July 22, 2008 1:36 PM
  • You can actually import Excel and Access 2007 data using the Microsoft Access 12.0 Access Database Engine OLE DB Provider.  Set up an ADO.NET connection manager, select this provider, supply the path and name of your Excel 2007 file, and then on the All tab, enter Excel 12.0;hdr=yes, or no, in the Extended Properties.  Test your connection, and you should be good to go.  You'll probably want to add a Data Conversion Transformation after your ADO.NET source to cast your data to the appropriate type and length.

     

    And as far as I know, this will work for either SQL 2005 or 2008, as long as you have the most up to date OLEDB drivers.

    Tuesday, July 22, 2008 3:12 PM

All replies

  • (deleted some words here...)

    SQL Server 2008 (will released sonly) including its CTPs support Excel 2007.

    Tuesday, July 22, 2008 1:36 PM
  • You can actually import Excel and Access 2007 data using the Microsoft Access 12.0 Access Database Engine OLE DB Provider.  Set up an ADO.NET connection manager, select this provider, supply the path and name of your Excel 2007 file, and then on the All tab, enter Excel 12.0;hdr=yes, or no, in the Extended Properties.  Test your connection, and you should be good to go.  You'll probably want to add a Data Conversion Transformation after your ADO.NET source to cast your data to the appropriate type and length.

     

    And as far as I know, this will work for either SQL 2005 or 2008, as long as you have the most up to date OLEDB drivers.

    Tuesday, July 22, 2008 3:12 PM
  •  

    Hi Chris Fo 

     

    Thanks for your valuable response.

    But when I do the changes as suggested I get the connection failure error....

    it would be more helpful for me if you could alaborate the solution Or can give the steps.

     

    Thanks

     

    Friday, July 25, 2008 7:46 AM
  •  Quest_RG wrote:

     

    Hi Chris Fo 

     

    Thanks for your valuable response.

    But when I do the changes as suggested I get the connection failure error....

    it would be more helpful for me if you could alaborate the solution Or can give the steps.

     

    Thanks

     

     

    make sure that sql server 2005 service pack 2 (or later) is installed.

     

    hth

    Friday, July 25, 2008 8:18 AM
  • Hi

     

    saw your post........... I am using Excel 2003 only, but my SSIS is not working properly. getting following errors from excel connection manager:

    Error 3 Validation error. Data Flow Task: SQL Server Destination [13055]: The column "Review Date" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported. d_ccrs_obligor_stg_ld.dtsx 0 0 

     

    In excel date is in format :mm/dd/yyyy hh:mmTongue Tieds am/pm.

     

    Please help...........

     

    thanks in advance

    Saturday, September 20, 2008 12:45 PM
  •  shweta_forum wrote:

    Hi

     

    saw your post........... I am using Excel 2003 only, but my SSIS is not working properly. getting following errors from excel connection manager:

    Error 3 Validation error. Data Flow Task: SQL Server Destination [13055]: The column "Review Date" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported. d_ccrs_obligor_stg_ld.dtsx 0 0 

     

    In excel date is in format :mm/dd/yyyy hh:mms am/pm.

     

    Please help...........

     

    thanks in advance

     

    if i'm not mistaken, this error means that ssis cannot perform the implicit conversion. therefore, you must perform an explicit conversion.

     

    try using the data conversion or the derived column transformation and the following ssis expression:

     

    Code Snippet

    (DT_DBTIMESTAMP)[Review Date]

     

     

    hth

    Monday, September 22, 2008 3:20 AM
  • thanks for reply............. actually I am looking for the date format, which sql server 2005 directly picks from excel. no conversion is reqd.

     

    Like I tried entring data in format: yyyy-mm-dd hh:mmTongue Tieds

    mm/dd/yyyy hh:mmTongue Tieds AM/PM or mm/dd/yyyy hh:mmTongue Tieds

    but nothing worked

     

     

    Monday, September 22, 2008 6:40 AM
  •  shweta_forum wrote:

    thanks for reply............. actually I am looking for the date format, which sql server 2005 directly picks from excel. no conversion is reqd.

     

    Like I tried entring data in format: yyyy-mm-dd hh:mms

    mm/dd/yyyy hh:mms AM/PM or mm/dd/yyyy hh:mms

    but nothing worked

     

     

     

    previously, you wrote the following:

     

     shweta_forum wrote:

    Error 3 Validation error. Data Flow Task: SQL Server Destination [13055]: The column "Review Date" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported. d_ccrs_obligor_stg_ld.dtsx 0 0 

     

    this means that the sql server destination can't insert the DT_DATE data type coming from excel.  sql server is expecting the DT_DBTIMESTAMP data type, not the DT_DATE data type.  however, the DT_DATE data type can be casted to the DT_DBTIMESTAMP data type, which sql server understands and can insert.

     

    hth

    Monday, September 22, 2008 6:54 AM
  • Okay............... but instead of putting any code, can i directly put date as expected data type. If yes, what should be that date.... I had tried almost all combinations. nothing worked Sad

    Monday, September 22, 2008 8:50 AM
  •  shweta_forum wrote:

    Okay............... but instead of putting any code, can i directly put date as expected data type. If yes, what should be that date

     

    i don't know.  why not just let ssis do the conversion for you?  is that not working?

     

     

    Wednesday, September 24, 2008 4:51 AM