[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Updating a SQL table from an excel file - vb

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
LyndaPostal
Asked:
LyndaPostal
  • 3
  • 3
  • 2
2 Solutions
 
CMorinCommented:
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
 
CluskittCommented:
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
 
CMorinCommented:
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
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.

 
CluskittCommented:
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
 
CMorinCommented:
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
 
LyndaPostalAuthor Commented:
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
 
LyndaPostalAuthor Commented:
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
 
CluskittCommented:
Glad we could help :)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now