SQL Server Performance

DTS Import Excel File

Discussion in 'SQL Server DTS-Related Questions' started by kannanonline, Oct 16, 2003.

  1. kannanonline New Member

    I have an ASP page which generates an excel ouput by specifying the following command.
    Response.ContentType = "application/vnd.ms-excel"
    The file is successfully created.When i try to Import this file using DTS Import Wizard, i get the following error "External Tables Not in Correct Format".Any help will be appreciated.
  2. satya Moderator

    Have you tried manually creating this excel file and then use DTS wizard.
    It refers some mismatch between excel file and sql tables.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. ykchakri New Member

    Also, make sure that you are choosing the right Excel version as your data source in DTS. I don't know about ASP, but the excel file generated using VFP, needs Microsoft Excel 5.0 as the data source. And I get the same error, if I use Excel 97-2000 as the data source.
  4. satya Moderator

    ... yeah make sure using correct data source.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. kannanonline New Member

    When i manually create an excel file,it works fine.Also i tried with all available Excel data source (5.0/8/0/97-2000 etc) but could not succeed though.
  6. satya Moderator

    Can you thru steps you're followed?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. kannanonline New Member

    I have an ASP page which outputs excel file (used response.contenttype).file is created perfectly.
    When i tried to import that file using DTS import/export wizard, i get the following error
    Externaal Tables not in expected format.Any help will be appreciated.
  8. satya Moderator

    Dont' know whether its related, what is the level of service pack on the SQL & OS?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. satya Moderator

    Instead of excel provider, try to import with TEXT file provider.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  10. Dave Wells New Member

    If the only purpose of the export is so that you can then import to SQL then you're much better off creating a simple csv or tab delimited file. Excel has got a handful of very annoying quirks when it comes to acting as a datasource which can cause you severe headaches. It's also a lot slower too.
  11. kannanonline New Member

    If i use text file provider,i'm able to import.I believe CSV also uses excel inturn for importing.Why is it not working when i use excel provider?Any suggestions?
  12. satya Moderator

    I think its because of the ASP file creation and excel driver referred thru DTS.
    Issues might be anticipated using Excel and paradox drivers with SQL DTS and also make a note of Dave Wells with regard to excel.

    I don't have any references or documents to prove the above, but seen troubles in past.



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page