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':
<%@ 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 'OleDbDataAdapter'
Thank you for your time.
Lynda
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<%@ 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 'OleDbDataAdapter'
Thank you for your time.
Lynda
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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).
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.
ASKER
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.
200 points of Cluskitt and 300 to CMorin ... based on number of responses. AND THANK YOU, CMorin for the code example.
ASKER
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!!!
Glad we could help :)