• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

How to Import Excel 2007 To SQL Server 2000

Guest
Jul 26, 2010 Jul 26, 2010

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques

Views

3.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 26, 2010 Jul 26, 2010

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 27, 2010 Jul 27, 2010

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jul 27, 2010 Jul 27, 2010

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation