Data conversion from excel

• jbrannon

  SSChasing Mays

  Points: 657

  More actions

  May 11, 2009 at 10:37 am

  #137716

  I'm importing data from and excel file but 1 column isn't importing correctly. I have a territory column that looks like this -

  A01

  A03

  A27

  A39

  40

  75

  The connection to the file is setup and works fine - the data conversion is not working the way I think it should. The destination table for the column is a varchar(5). In my data conversion step I convert the territory to a string. When I look at the end result in the table the 2 records without an "A" show up as null instead of 40 and 75. Do I need to change datatypes? It seems like everything I have tried has not worked. Do I need to change the data type in the table?

  Thanks for any help.

• Mukti

  SSCrazy

  Points: 2826

  More actions

  May 11, 2009 at 12:03 pm

  #992367

  You need to change the connection properties of Excel connection to support mixed types.

  Modify the excel connection string and add IMEX=1 to the extended properties.

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