locked
importing data from excel to text file RRS feed

  • Question

  • Hi,

    i am importing data from excel to text file using ssis

    The problem i am facing is..one of the column of my excel file has both text as well as numeric data

    so if the column has numeric data in first 8 or in the begining rows then i am getting only numeric data in my text field and text data is displayed as blank.

    if in the beginning is text then numeric data is displayed as blank in my text file

     

    is there any way so that i can import the data without any blanks

    any suggestions on how to achieve this through my ssis package

    Friday, January 23, 2009 7:45 PM

Answers

  •  

    The Jet Excel Engine guesses the type of each column by sampling a certain number of rows. This number comes from a registry key TypeGuessRows. The default value is 8. Increase this number, so you will most likely get the data types right.

    Now if you check your excel Source - Advanced Properties - External Columns, you can see what Data Types Jet has guessed for each of your columns. If the problem column is not DT_NText, then please change the registry setting and retry

    • Marked as answer by Bob Bojanic Thursday, January 29, 2009 6:19 PM
    Saturday, January 24, 2009 1:57 AM
    Answerer

All replies

  •  

    The Jet Excel Engine guesses the type of each column by sampling a certain number of rows. This number comes from a registry key TypeGuessRows. The default value is 8. Increase this number, so you will most likely get the data types right.

    Now if you check your excel Source - Advanced Properties - External Columns, you can see what Data Types Jet has guessed for each of your columns. If the problem column is not DT_NText, then please change the registry setting and retry

    • Marked as answer by Bob Bojanic Thursday, January 29, 2009 6:19 PM
    Saturday, January 24, 2009 1:57 AM
    Answerer
  • Hi,

    As per this article http://support.microsoft.com/kb/194124/en-us, you need to append ;IMEX=1 to the end of the Excel Connection Managers Connection String.  Example:

    Before

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\whartonj\Documents\Book1.xls;Extended Properties="EXCEL 8.0;HDR=YES";

    After

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\whartonj\Documents\Book1.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

    Cheers
    Jeff


    SQL Server MVP / WARDY IT Solutions, Solutions Architect
    Saturday, January 24, 2009 1:59 AM