Strange result from DTS

• Carl Farmer

  SSCrazy

  Points: 2197

  More actions

  June 30, 2004 at 4:35 am

  #86023

  I am trying to import some data from a .xls file straight into an empty table.

  However, I am getting some strange results. The first column of the .xls file is mapped straight to the first column of the table. The data in question looks like this:

  10321457

  12348666

  52124726

  33457887

  MT1225

  MT3364

  When the data is imported, records that start 'MT' are inserted as NULL. So I tried putting these at the top of the .xls file, this then inserted the records beginning MT and then inserted the rest as NULL. The colum in the .xls file is formatted as 'Text' and the table column data type is VARCHAR.

  Does anyone know why this happens?

  Thanks in advance.

  Carl.

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  June 30, 2004 at 5:33 am

  #512615

  This is a problem with importing data from Excel.  I have run into this in the past. 

  Our workaround was to place an apostrophe (single tick) before each numeric value in the Excel spreadsheet.  I just tried it with the sample information supplied and it worked.

  This works with or without a column header as well

  Good Luck,

  AJ

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  June 30, 2004 at 6:45 am

  #512645

  i've also found that importing to Access first and then to SQL improves the process.

  MVDBA

• KarstenEnH

  SSC-Addicted

  Points: 444

  More actions

  July 1, 2004 at 2:44 am

  #512820

  Hi Carl,

  another more simple workaround is to use the "text in columns" feature in the "data" menue in excel. Select the column(s) concerned, choose fixed size and two times click "next". Than you can set the column from standard to text, finish and save. Now DTS will load all entries without apostroph or an additional load to ACCESS.

  Note that the settings you chose are not shown when you start text in columns a second time. The columns seems to be set to standard always.

  I do hope this flawed situation in loading excel table by DTS will be handled by a service pack. Loading excel columns is a bit risky somehow because there are no warnings or errors when data is set to NULL. Does anyone know wether MS is aware of this problem already?

  Karsten