Solved

Import Excel file to SQL table

Posted on 2002-05-26
16
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert VB6 MSXML2.ServerXMLHTTP process to C# 2 80
Child Form in front 4 65
which modules are active in VB6 project? 6 56
SQL Server 2012 to SQL Server 2016 24 54
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

732 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