SQL Server Performance

Strange behaviour when trying to import .xls

Discussion in 'SQL Server 2005 Integration Services' started by MunterFace, Jul 10, 2008.

  1. MunterFace New Member

    Hi
    I have a problem when importing a .xls containing date fields into a table using SSIS through development studio( tasks >import data etc). My target database schema is all varchar 255 simply to get the table into staging prior to manipulation. The trouble I am experiencing is that although all my source fields are valid dates in the format dd/mm/yyyy in the .xls when I try and insert them as varchar and look at the results some fields are formated differently and some records are null where they should be populated.
    Any advice would be appreciated.
  2. madhuottapalam New Member

    check what is this statement returns, whether the same problem exists or notSELECT *
    FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=c:yourexcelfilename.xls', 'SELECT * FROM [Sheet1$]') AS XLMadhu
    SQL Server Blog
    SQL Server 2008 Blog
  3. MunterFace New Member

    Same problem occurs when using the query provided.
  4. satya Moderator

    When the source data is constant such as date format why you are putting them in VARCHAR, better to use proper data type rather than dribbling around them.
  5. MunterFace New Member

    [quote user="satya"]
    When the source data is constant such as date format why you are putting them in VARCHAR, better to use proper data type rather than dribbling around them.
    [/quote]
    Although the data I am currently testing is consistent there is no garauntee this will always be the case as the source data will be collated manually hence the reason why setting the load to import into varchar.
    The problem can be fixed by changing the .xls to a .csv so it would appear that the problem is occuring between the two microsoft products exchanging meta data or the way that excel stores data.
  6. moh_hassan20 New Member

    Is the format of cells in excell is date format or text ?
    What is the format of the date in the server? it may be mm/dd/yyyy .check it.
    As no problem in csv format, why not to save excel sheet as csv format , and import it using ssis.

Share This Page