Solved

Updating a SQL table from an excel file - vb

Posted on 2010-08-26
8
490 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

831 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