DTS Import Excel File | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS Import Excel File

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.
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
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.
… yeah make sure using correct data source. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
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.
Can you thru steps you’re followed? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
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.
Dont’ know whether its related, what is the level of service pack on the SQL & OS? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Instead of excel provider, try to import with TEXT file provider. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
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.
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?
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
]]>