dslocum
asked on
Import Excel file to SQL table
I receive an Excel file from a client on a daily basis that must have all of its records, in some (but not all) columns imported / appended into my SQL server, via ODBC. I need to do this via DAO, as ADO (ect) is not available right now (if that's important). I need to do this within an existing DAO based application.
I have -VERY- limited knowledge of OLE, etc. I'm pretty good at VB, in general, but there seems to be precious little information on how to do what I'm asking for. I know this should be simple, but for the life of me, I JUST DON'T GET IT!
Am I even asking the right questions or presenting the problem correctly??? I've tried MSKB and can't seem to come up with the right combination of answers.
HELP, please! Source is the best way for me to understand. If you respond, expect that I'll ask LOTS of questions!!!
Regards,
Doug
I have -VERY- limited knowledge of OLE, etc. I'm pretty good at VB, in general, but there seems to be precious little information on how to do what I'm asking for. I know this should be simple, but for the life of me, I JUST DON'T GET IT!
Am I even asking the right questions or presenting the problem correctly??? I've tried MSKB and can't seem to come up with the right combination of answers.
HELP, please! Source is the best way for me to understand. If you respond, expect that I'll ask LOTS of questions!!!
Regards,
Doug
SQL server comes with an application called the DTS wizard (Data Transformation Services) it is used to do exactly what you are asking, importing data into the SQL DB. Could you get access to this?
ASKER
I probably should have mentioned that this is a Client/Server application, and that this function must be done on client machines. Is there still a hope???
Doug
Doug
ASKER
BTW, Excel is installed on each client machine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could use DTS for the same.It could be possible for you to avoid the VB code as well.
If the structure of the file and content do not change,you can create/schedule and save a DTS job to run at specified intervals.
A minor advantage to this is that entire processes are handled by SQL server itself.
DTS can be found under the Import/Export options in SQL Server
Rgds
KC
If the structure of the file and content do not change,you can create/schedule and save a DTS job to run at specified intervals.
A minor advantage to this is that entire processes are handled by SQL server itself.
DTS can be found under the Import/Export options in SQL Server
Rgds
KC
ASKER
xSinbad,
Thanks for your helpful code. It basically works fine, but does this mean that I must loop thru each Excel row -in code- to do inserts, or is there another / better way?
chops123,
I'd love to save work, and make this more efficient. Could you describe the process a little more, please? Remember that this import process will be happening on a -client- machine, not the server, so I'm wondering if this is really an option.
Regards,
Doug
Thanks for your helpful code. It basically works fine, but does this mean that I must loop thru each Excel row -in code- to do inserts, or is there another / better way?
chops123,
I'd love to save work, and make this more efficient. Could you describe the process a little more, please? Remember that this import process will be happening on a -client- machine, not the server, so I'm wondering if this is really an option.
Regards,
Doug
I dont think it matters where you are scheduling your task.
In fact having this done on the SQL server itself makes it more meaningful since the process runs in the SQL Server memory space.
Open up enterprise manager.Click on databases.Select your table,right click-- All tasks--import data.
Just follow the procedure as displayed.
Select the from data source.It will be defaulted to SQL Server.Select "Text File" from the drop down.Select the CSV file you want to import.The Last window asks you to "Save the DTS Package" and "Schedule for Later execution".
Once done,you can have the same running at the intervals defined by you.
Rgds
KC
In fact having this done on the SQL server itself makes it more meaningful since the process runs in the SQL Server memory space.
Open up enterprise manager.Click on databases.Select your table,right click-- All tasks--import data.
Just follow the procedure as displayed.
Select the from data source.It will be defaulted to SQL Server.Select "Text File" from the drop down.Select the CSV file you want to import.The Last window asks you to "Save the DTS Package" and "Schedule for Later execution".
Once done,you can have the same running at the intervals defined by you.
Rgds
KC
ASKER
xSinbad / chops123,
Any further input on my previous question would be appreciated.
Doug
Any further input on my previous question would be appreciated.
Doug
Sorry for that dslocum I am currently on the road taveling around the country.
As to your question, yes you do need to loop through the cells to read them unless you connect to the sheet using some other method. But this should not be an issue unless the sheets are very large or the client machines are very slow.
As to your question, yes you do need to loop through the cells to read them unless you connect to the sheet using some other method. But this should not be an issue unless the sheets are very large or the client machines are very slow.
Have you tried the DTS option as I mentioned in my previous comment ?
If not,then pls. try it out.
Rgds
KC
If not,then pls. try it out.
Rgds
KC
Hi dslocum,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Accept chops123's comment(s) as an answer.
dslocum, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Accept chops123's comment(s) as an answer.
dslocum, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
ASKER
DanRollins,
I think I used to be able to cancel a "question" if I found an answer elsewhere, or simply wished to withdraw a question.
I certainly want to be a good citizen here, but it's not clear what to do in circumstances where an "asker" finds an answer outside this forum.
Could you offer insight into this please?
Doug
I think I used to be able to cancel a "question" if I found an answer elsewhere, or simply wished to withdraw a question.
I certainly want to be a good citizen here, but it's not clear what to do in circumstances where an "asker" finds an answer outside this forum.
Could you offer insight into this please?
Doug
hi dslocum,
Currently your choices are:
1) Accept an Expert's comment sa an Answer (please, don't give a bad grade in this case)
2) Post a 0-pt Question to the Community Support Topic Area and ask a Moderator to help you finalize this. There are options:
2A) Ask the Moderator to Split Points between Experts
2B) Ask a Moderator to DELETE the question (you get a refund and no Expert gets points)
2C) Ask a Moderator to save this in the PAQ and give you a refund (no Expert gets awarded points).
For 2C, it is important that you first post here, at least a summary of how you solved the problem. That way, if this question comes up in a search, there will be some value.
The Experts here do not get paid, nonetheless, several Experts took time out to examine your problem and try to help you work out a solution. It is customary to say "thanks" when people try to help you. So...
You can also do a sort of combination of #1 and #2x -- Post a summary of the solution you found, then thank one or more experts who helped you by accepting a comment as an Answer.
-- Dan
Currently your choices are:
1) Accept an Expert's comment sa an Answer (please, don't give a bad grade in this case)
2) Post a 0-pt Question to the Community Support Topic Area and ask a Moderator to help you finalize this. There are options:
2A) Ask the Moderator to Split Points between Experts
2B) Ask a Moderator to DELETE the question (you get a refund and no Expert gets points)
2C) Ask a Moderator to save this in the PAQ and give you a refund (no Expert gets awarded points).
For 2C, it is important that you first post here, at least a summary of how you solved the problem. That way, if this question comes up in a search, there will be some value.
The Experts here do not get paid, nonetheless, several Experts took time out to examine your problem and try to help you work out a solution. It is customary to say "thanks" when people try to help you. So...
You can also do a sort of combination of #1 and #2x -- Post a summary of the solution you found, then thank one or more experts who helped you by accepting a comment as an Answer.
-- Dan
dslocum you said about my comment" It basically works fine, but does this mean that I must loop thru each Excel row -in code- to do inserts, or is there another / better way?"
To which I proposed an answer and then recieved no feedback, so now you are saying you found the answer elsewhere eh?
To which I proposed an answer and then recieved no feedback, so now you are saying you found the answer elsewhere eh?
ASKER
Hi folks.
I don't want to piss off anyone. I want to do the right thing by all of you.
I was not able to figure out the DTC solution. No one suggested how to go about this, and I couldn't figure out how this should be done on client machines - not from the server. A couple of instructive links would have been helpful.
I did find an answer prior to xSinbad's reply but was unable to figure out how to prevent further replys or cancel my question.
I appologize to all for my lack of education about this forum and do indeed thank you.
xSinbad did provide the closest answer to my question, so I shall award him the points.
Regards,
Doug
I don't want to piss off anyone. I want to do the right thing by all of you.
I was not able to figure out the DTC solution. No one suggested how to go about this, and I couldn't figure out how this should be done on client machines - not from the server. A couple of instructive links would have been helpful.
I did find an answer prior to xSinbad's reply but was unable to figure out how to prevent further replys or cancel my question.
I appologize to all for my lack of education about this forum and do indeed thank you.
xSinbad did provide the closest answer to my question, so I shall award him the points.
Regards,
Doug
ASKER
Thanks!
Doug
Doug