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
LVL 1
dslocumAsked:
Who is Participating?
 
xSinbadConnect With a Mentor Commented:
Failing this here is some code to get you started;

Firstly to read from the excel sheet you will need some OLE, are the sheets in the same format each time,  if not you may need to do some serious thinking.

Read Excel Sheet:-
Dim xl As New Excel.Application
   Dim xlw As Excel.Workbook

   ' Open the excel file.
   ' replace the "c:\myDir\book1.xls" below with your excel file name.
   Set xlw = xl.Workbooks.Open("c:\myDir\book1.xls")

   ' replace "Sheet1" with the sheet that you want to get the data from it.
   xlw.Sheets("Sheet1").Select

   ' Get the value from cell(2,3) of the sheet.
   MsgBox xlw.Application.Cells(2, 3).Value

   ' Close worksheet without save changes.
   ' If you want to save changes, replace the False below with True
   xlw.Close False

   ' free memory
   Set xlw = Nothing
   Set xl = Nothing

'Code from Ark Q.20191560.

Then to put the data into a SQL server database, here is some code I have used successfully:-


Public Function AddData(ExcelData As String, ServerGroup As String, DatabaseName, login, Password)
Dim dbsData As Database, strConnect As String
Dim strData As String

strConnect = "ODBC;Driver=SQL Server;Server=" & ServerGroup & ";DATABASE=" & DatabaseName & ";UID=" & login & ";PWD=" & Password
                 
Set dbsData = OpenDatabase("", False, False, strConnect)
    strData = "insert into TableName (FieldNames) VALUES (" & Chr(39) & ExcelData & Chr(39) & ")"
    With dbsData
           .Execute strData, dbSQLPassThrough
           
    End With
    dbsData.Close
    Set dbsData = Nothing
If Errors.Count > 0 Then
   Dim loError As Error

   For Each loError In Errors
       MsgBox "Error: " & loError.Number & vbCrLf & "Description: " & loError.Description

   Next
End If

End Function
0
 
xSinbadCommented:
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?

0
 
dslocumAuthor Commented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dslocumAuthor Commented:
BTW, Excel is installed on each client machine.
0
 
chops123Commented:
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
0
 
dslocumAuthor Commented:
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
0
 
chops123Commented:
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



0
 
dslocumAuthor Commented:
xSinbad / chops123,

Any further input on my previous question would be appreciated.

Doug
0
 
xSinbadCommented:
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.
0
 
chops123Commented:
Have you tried the DTS option as I mentioned in my previous comment ?

If not,then pls. try it out.

Rgds
KC
0
 
DanRollinsCommented:
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
0
 
dslocumAuthor Commented:
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
0
 
DanRollinsCommented:
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
0
 
xSinbadCommented:
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?
0
 
dslocumAuthor Commented:
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
0
 
dslocumAuthor Commented:
Thanks!
Doug
0
All Courses

From novice to tech pro — start learning today.