×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Import Excel to SQLServer - last posted in 2006

Import Excel to SQLServer - last posted in 2006

Import Excel to SQLServer - last posted in 2006

(OP)
I have searched and found the last posting on tek-tips at http://www.tek-tips.com/viewthread.cfm?qid=1211227 for help with importing Excel data to SQLServer.  There was an offer to help write a DTS package.  I then followed these instructions; 1.  Click the database you want to use, right-click on TABLE and choose 'All Tasks' -> 'Import Data'
 but I do not see a value named "All Tasks" when I right click on a table in my SQLServer DB.  Is there anyone who can help me figure out 1)How to create a DTS package? 2)Basically I'm trying to import Excel to SQLServer and update tables monthly.  I do not want to overwrite the existing data (if I am ever successful in importing the Excel data in the first place), I want to update the table with new information provided in the newly generated monthly Excel spreadsheet.  So far I haven't figured out how to do that, but from my research it sounds as if a DTS package could be called from a script (which I did find and have saved with my DB info in it).  Bascially it says to create dts package object
pkg = createObject("COM","DTS.Package");
      // load package       
pkg.LoadfromSQLServer ...but that's not going to work until I actually have a DTS package.  Thanks in advance for help with creating a DTS package.

RE: Import Excel to SQLServer - last posted in 2006

Does the process have to use DTS? Because there may be other options. A few questions first:

1) What versions of: MS SQL, ColdFusion and Excel ?
2) How much data is involved?

----------------------------------
http://cfsearching.blogspot.com/

RE: Import Excel to SQLServer - last posted in 2006

(OP)
Thanks for your questions.  I am using MSSQL Server 2008 R2, ColdFusion on a BlueDragon platform and MS Excel 2010.  I 'believe' I have found the solution within MSSQL Server by right clicking on the database name within MS SQL Server Mgmt Express; the dropdown menu there contained an option named "Tasks" and under that menu there was an option "Import".  I was able to browse to my Excel spreadsheet and using a wizard was able to import all of my data from Excel to my SQLServer database where it created a table on the fly.  The downside of this is there didn't seem to be any opportunity to create multiple (relational) tables.  It's just one big table in SQLServer.  My Excel spreadsheet has 9980 rows, so it's fairly manageable.  However, what I really need is a way to do this automatically behind the scenes into multiple tables and dump this data into Tableau and ultimately present it on the web without routine human interference.  I'm still searching for tools that are avaiable for me to put all these pieces together for free or cheap (sorry very little funding available for software purchases). I'm still evaluating software and have reviewed Expressor (too complicated) but it will massage the data into multiple tables.  Just seems to have quite a steep learning curve and a high price ($2500/year - $7500/year for a subscription).  I'm looking at Tableau as it seems easier to use but not quite sure how to go about setting up multiple tables so that I can manage the resulting reports.  If anyone has done anything like this I'd love to hear more about it.  I think I'm looking for more than a DTS package at this point.

RE: Import Excel to SQLServer - last posted in 2006

Well a couple thoughts

1) The free versions of MS tools often lack some of the more advanced tools. So it is possible that you do not have DTS installed?
http://msdn.microsoft.com/en-us/library/bb500440.aspx

2) I am not extremely familiar with DTS, but I believe is deprecated anyway. So you want to investigate other options. For example, using OPENROWSET or OPENQUERY. **Note, I am not sure if you need different/additional drivers to read Excel 2010 files.

http://msdn.microsoft.com/en-us/library/ms190312.aspx
http://msdn.microsoft.com/en-US/library/ms188427%28v=SQL.90%29.aspx

Quote:

The downside of this is there didn't seem to be any opportunity to create multiple (relational) tables.

3) Personally I recommend processing in steps. Import the raw data into a staging table. Do your validation. Then insert the scrubbed data into your main tables as usual. Wrap the whole thing in a stored procedure and you can invoke it from CF easily.


 

----------------------------------
http://cfsearching.blogspot.com/

RE: Import Excel to SQLServer - last posted in 2006

Quote:

So you want to investigate other options

Sorry, that should have read "you may want to investigate other options".

----------------------------------
http://cfsearching.blogspot.com/

RE: Import Excel to SQLServer - last posted in 2006

Quote:

So you want to investigate other options

Sorry, that should have read "you may want to investigate other options".

----------------------------------
http://cfsearching.blogspot.com/

RE: Import Excel to SQLServer - last posted in 2006

By the way, SQL Server 2005 and on does not utilize DTS anymore. You will need to use SSIS. (SQL Server Integration Services) It has been quite sometime since I actually used this but they are some really cool stuff into this. You can even put vb code in there.

Since it's already in there, you don't have to purchase a 3. party application.

ColdFusion Ninja for hire.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close