Solved

Updating a SQL table from an excel file - vb

Posted on 2010-08-26
8
488 Views
Last Modified: 2012-05-10
I'm not really sure this question belongs here, but I need to know if this is feasible.

I have a price / availability list in a SQL table. I have a parts distributor that updates their stock levels and sets an Excel (and a CSV) file up on their FTP server every night. We 'carry' a small percentage of those parts.

I would like to read in the downloaded file, and, using the SQL table as primary (or not, this is the advise I need) access the Excel file to update the 'In Stock' quantity field in my table. The Excel file is 2 meg and has many thousands of parts. My pricelist has only a few thousand. To loop through an in-memory table (considering its size) to check every Excel row against my SQL table seems to be a real waste of system resources.

So I think I should pull in the two fields from my SQL table (partNo and Quantity) and then match the part number to the column in the Excel file, grab the Quantity and update my SQL table.

comments? Advise? Please don't tell me I have to loop through every Excel row to 'find' each partNo. There has just got to be a better way. I just can't figure it out

I've pulled this code from the 'Web' and it SEEMS random-access to me, but Intellesense (VS 2010) is complaining bitterly about the '[OleDbTableAdapter':
        Dim dbadp As New OleDbTableAdapter("Select * From Sheet1$", "provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;")
        Dim dTable As New DataTable
        dbadp.Fill(dTable)
        dbadp.dispose()
        For i As Integer = 0 To dTable.Rows.Count - 1
            dbcmd.commandtext = "Update SQLTable Set OrderNo=" & dTable.Rows(i).Item("OrderNo") & " Where ItemNo=" & dTable.Rows(i).Item("ItemNo") & " AND Location='" & dTable.Rows(i).Item("Location") & "'"
            dbcmd.ExecuteNonQuery()
        Next

Open in new window

... and I've imported namespaces to try to resolve this ...

<%@ Import Namespace="system.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Import Namespace="system.Data.SqlClient" %>

and I searched for 'OleDbTableAdapter' and it seems that it is not a real object, so I am wondering if what is meant is 'OleDbDataAdapter'

Thank you for your time.

Lynda

0
Comment
Question by:LyndaPostal
  • 3
  • 3
  • 2
8 Comments
 
LVL 2

Accepted Solution

by:
CMorin earned 300 total points
ID: 33533744
You're correct this should be a OleDbDataAdapter, and then you need to add a couple of parts as in the attached code.
As to your loop if you want to update a SQL table you need a second connection, adapter and command for that, pointing at your SQL database.
To make your comparison you might want to read your SQL table in, and use a dataview filter for each SQL row against the Excel table to get the relevant values.
Dim xlConnectString As String = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;"

Dim xlConnection As New OleDb.OleDbConnection(xlConnectString)

Dim xladp As New OleDb.OleDbDataAdapter

Dim xlcmd As New OleDb.OleDbCommand("Select * From Sheet1$", xlConnection)

Dim xlTable As New DataTable



xladp.Fill(xlTable)

xladp.Dispose()

xlConnection.Close()

Open in new window

0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 200 total points
ID: 33533819
No need to do all that. You can simply send an update query to all lines, and add a where clause to match partno. If there is no partno in the DB, it will simply do nothing.
0
 
LVL 2

Expert Comment

by:CMorin
ID: 33533926
To clarify, by "all that" you mean the comparison, correct?  We still need to read in the Excel file and build the missing SQL connector.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33533985
Yes. I was talking just about the queries. I find that comparing to see if the value exists takes more effort than simply sending an update on all with a where clause (cause when you do that, you will already be comparing anyway).
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Expert Comment

by:CMorin
ID: 33534017
I guess the only value in comparing is if you don't have a unique reference.  For example if the same partno appears multiple times in Excel then SQL is only going to get the last set of values, and if SQL also has multiple instances of partno they will all be overwritten.  From the description its probably safe to assume that partno is unique though.
0
 

Author Comment

by:LyndaPostal
ID: 33534113
Wonderful!! I have the unique reference (partNo) that is common to the Excel file and the table I am updating ... and to simple issue an update query is an elegant solution. Thank you so much, both of you!! :)

200 points of Cluskitt and 300 to CMorin ... based on number of responses. AND THANK YOU, CMorin for the code example.
0
 

Author Closing Comment

by:LyndaPostal
ID: 33534651
I just can't tell you how much I appreciate the timely and complete solution to my problem. One of these days I will completely automate this (setting up an FTP client, and having the job run at midnight) but for now this routine is all I need/want to accomplish. GOOD JOB!!!
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33535289
Glad we could help :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

12 Experts available now in Live!

Get 1:1 Help Now