Post reply

Strange result from DTS

  • 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.

  • 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

  • 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

  • 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

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply