Solved

Updating a SQL table from an excel file - vb

Posted on 2010-08-26
8
494 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

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.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 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