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
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") & "'"
... and I've imported namespaces to try to resolve this ...
<%@ Import Namespace="system.Data" %>
<%@ Import Namespace="System.Data.Ole
<%@ Import Namespace="system.Data.Sql
and I searched for 'OleDbTableAdapter' and it seems that it is not a real object, so I am wondering if what is meant is 'OleDbData
Thank you for your time.