Import Excel to SQLServer - last posted in 2006

[oldnewbee]

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.

 

[cfSearching]

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/

 

[oldnewbee]

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.

 

[cfSearching]

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(v=SQL.90).aspx

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/

 

[cfSearching]

So you want to investigate other options

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

----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

So you want to investigate other options

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

----------------------------------

http://cfsearching.blogspot.com/

 

[FALCONSEYE]

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.