Skip to main content
A
Anonymous
July 26, 2010
Question

How to Import Excel 2007 To SQL Server 2000

  • July 26, 2010
  • 1 reply
  • 3755 views

Need to set up a daily DTS program to import Excel 2007 files (.XLSX) to SQL Server 2000.  Only Excel options go to version 2002.  After reading blogs, I found an update my Microsoft, called:  AccessDatabaseEngine.exe, that adds an Excel 2007 option to your DTS options.  However, when you try to run it, it's looking for .DSN files, not .XLSX files.

I tried a few things, and found that a .DSN file was created, a small text file referencing the Excel file, including full path. But when trying to complete the DTS import, there was nothing to import.

For our needs, saving Excel file as a .CSV/.TXT is not possible.   So back to Excel, does anyone know how to get this to work?  Is the Access DB Engine the correct approach?    Since we ultimately need an automated daily production process, whatever the solution is, has to run automatically, and not require manual intervention each morning, before the DTS job is scheduled to run against the Excel file (FTP'd each morning to the SQL Server).

Thanks for any help/advice.

Gary

This topic has been closed for replies.

1 reply

I
insuractive
Inspiring
July 26, 2010

Hi Gary,

I'd be curious to see if you've come up with a solution yet, since it is an ongoing frustration for most CF/SQL Server set ups that DTS doesn't support the newer 2007 excel XLSX Zip format.  I'm not sure how much help you are going to find on this particular forum, though, since it is a related topic, though not strictly coldfusion-related.  I'm sure you've already checked on the popular SQL Server forums as well, but if not I would recommend sweeping those as well.

Is there any way you can generate your source files as the older Excel .xls format?  That should fix your problem in terms of being able to import directly without any work-arounds.

A
Anonymous
July 27, 2010

No solution yet.  Importing the file, with manual intervention, is easy.  You can save manually save the file (SAVE AS) a lower version, and it imports fine.  Or convert to a .CSV file.  But we need the process to run "in production", 7 days/week, 365, around 4AM each morning, with no manual intervention.  I'm sure a program can be written (JS, VB, etc.) to do the job, but my skills are not in that area.  I'm a SQL, CF guy.

I appreciate the interest and advice.  Much appreciated.  I'll keep experimenting with this .DSN thing. I really don't understand why it even creates a .DSN file, which references the .XLSX file.  We will be upgrading our SQL Server to the latest version later this year.  I've got the only server in the company that hasn't been upgraded yet.  It's also one of the busiest, with 2M lines of CF code, 19 major reports, and 20,000 hits/day by 5,000 employees.

That's probably why you don't see too many posts about this issue.  Most people probably have the latest versions of SQL Server and Office already.  Thanks again,

Gary

-
-__cfSearching__-
Inspiring
July 27, 2010

I cannot say I know much about it. But out of curiousity, are you able to read files if you set up a direct connection manually like mentioned here:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices