Solved

Import Excel file to SQL table

Posted on 2002-05-26
16
304 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:dslocum
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 6

Expert Comment

by:xSinbad
ID: 7035893
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
 
LVL 1

Author Comment

by:dslocum
ID: 7035912
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
 
LVL 1

Author Comment

by:dslocum
ID: 7035913
BTW, Excel is installed on each client machine.
0
 
LVL 6

Accepted Solution

by:
xSinbad earned 100 total points
ID: 7035937
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
 
LVL 2

Expert Comment

by:chops123
ID: 7037603
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
 
LVL 1

Author Comment

by:dslocum
ID: 7044337
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
 
LVL 2

Expert Comment

by:chops123
ID: 7044383
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
 
LVL 1

Author Comment

by:dslocum
ID: 7053665
xSinbad / chops123,

Any further input on my previous question would be appreciated.

Doug
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 6

Expert Comment

by:xSinbad
ID: 7054961
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
 
LVL 2

Expert Comment

by:chops123
ID: 7055544
Have you tried the DTS option as I mentioned in my previous comment ?

If not,then pls. try it out.

Rgds
KC
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7818583
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
 
LVL 1

Author Comment

by:dslocum
ID: 7824776
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7825219
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
 
LVL 6

Expert Comment

by:xSinbad
ID: 7825821
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
 
LVL 1

Author Comment

by:dslocum
ID: 7826041
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
 
LVL 1

Author Comment

by:dslocum
ID: 7826044
Thanks!
Doug
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now