Compare Excel to Access Table

[Igawa29]

I had this idea but I am not sure how to go about implimenting this process. I have a combo box on my form that when loaded houses my table names (From Access). I want to be able to open an Excel file and compare it with this table, then spit out the differences in another Excel file. I am just not sure how to go about bringing in the Excel file to compare.

 

[SkipVought]

HI,

I can only offer a perspective that I can relate to, as I'm not a .net guy.

I've used ActiveX Data Objects to query an Access db and an Excel workbook. Each has its own connection string and SQL syntax. For instance an Excel sheet/table is denoted
[tt]
[YourSheetName$]
[/tt]
So you have two data sources and you might be able to join them in some SQL statement.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Igawa29]

Thanks Skip I will try that and find syntax around that. If this were SAS it would be easy to reference everything, however being in the .net environment its all about the connection strings.

 

[Andrzejek]

Your combo box on the form has table names from Access data base.
How are the 'table names' stored in Excel? All in Column A on Sheet1?
Or each table is in its own worksheet? I.e. each worksheet is named as a table in Access?
Or maybe be all of it is done totally different way...?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Igawa29]

Hey Andy,
Basically what I want to do is once a table is selected on the combo box it will select the table in Access then when the Excel file is opened it will know what table to compare to.

 

[Andrzejek]

Your 'explanation' raises even more questions (at least in my simple mind...)

"it will select the table in Access "
What does that mean? What is 'it'? The table's name is already selected in your combo box in your VB.NET application.

"then when the Excel file is opened it will know what table to compare to. "
So each Access' table have a separate Excel file to be compared to?

And, "to be compared " - a compare what to what?

If I am completely missing your point (which I am) and messing your thread, please let me know. I'll step aside and let somebody else (who can understand your issue) help you.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Igawa29]

Sorry Andy I might not be explaining it correctly either.

Here is what I am planning:

1) User selects an already established table (Microsoft Access) from a combobox

2) Then clicks a Compare button which brings up a file open browser

3) User selects their Excel template with the data

4) The system will look at the Microsoft Access table data vs. The Excel Uploaded Data

I have started a little on the code being able to let the user upload their Excel file:

Dim FileOpenBrowse As New OpenFileDialog
Dim dsDataPull As DataSet
Dim strSQL As String
Dim dcCompare As New Data_Connection_Final

If Not iscomboboxvalid() Then
Exit Sub
End If


FileOpenBrowse.Filter = "Excel|*.xls; *.xlsx"
FileOpenBrowse.Title = "Select Excel Template (Download from Main Screen)"

If FileOpenBrowse.ShowDialog = System.Windows.Forms.DialogResult.OK Then
Try
'Pulling the Access Database based on the combo box table
strSQL = "Select * from " + Me.TableDrop_CB.Text
dsDataPull = dcCompare.GetDataSet(strSQL)
Catch ex As Exception

End Try

End If

 

[SkipVought]

User selects their Excel template with the data"

So there's only one workbook in play?

So how is the data structured in the workbook?

Are there tables on various worksheets?

Are you comparing table to table?

You are very vague.

What happens in vagueness stays in vagueness!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JRB-Bldr]

OK, I am most certainly no expert with VB.net, but what VB.net development I have done over the last 2 years in VS2008 & VS2010 might offer some suggestions for you.

First, get the appropriate connection string information you need from:

https://www.connectionstrings.com/

Obviously that info is 'genericized' so it will need to be customized to your own requirements.
Note - you can test the connection string via a UDL file

(

https://www.google.com/#q=connection+string+test+udl

)​

Then you can find how to use the Connection string in your code.

(

https://www.google.com/#q=vb.net+use+odbc+connection

)​

Next, within Visual Studio I'd layout the user forms/pages (the ...aspx files) - setting the needed objects to runat="server"
Then in the form's ...aspx.vb file I'd develop the code to do what was needed.

In the Sub Page_Load() method I'd have a If Not (Page.IsPostBack) Then....Endif section I'd have code to populate the mentioned Combobox with the Access data table filenames.
This can be done by 'hard-code' or by having the system 'read' the associated directory and populate the Combobox with what it finds.

Now the user can make the needed Access filename selection.
Since you don't want the server to have to work each time the Combobox selection changes (such as scrolling through the values), you might want the next action to occur on the click of some button.

When that button is clicked, the Sub button_Click() would be accessed which would acquire (via and ODBC Connection) the requested data and display it into a DataGrid on the form/page.

You don't say how the User selects their Excel template.
* Do they have this in another Combobox?
* Or is it presented to the user in some other manner?

Regardless, you would do something similar to the above to acquire the Excel data.

You also do not go into detail on The system will look at the Microsoft Access table data vs. The Excel Uploaded Data
* Is the 'system' somehow doing some 'comparison' of the data - row-by-row, field-by-field?
* Or is the user comparing the data?
* Or what?

And then, under what circumstances, will it write out the data to another Excel file?

Obviously there are a LOT of things that need to occur.
I'd start with just setting up the user form/page and getting the most simple things done (maybe read the Access file and display the data).

After that was working, I'd go on to the next part of the problem - going piece-by-piece until all the parts were complete.

Keep in mind - the value of what I offer is proportional to what you had to pay for it.
Good Luck,
JRB-Bldr

 

[Andrzejek]

1) User selects an already established table (Microsoft Access) from a combobox
So you may have in your combo box:
tblCustomers
tblOrders
tlbCountries
tblManagers
Let’s say user selects tblCountries

2) Then clicks a Compare button which brings up a file open browser
OK
3) User selects their Excel template with the data
Let’s say user selects a file named Countries.xlsx that has only one worksheet
4) The system will look at the Microsoft Access table data vs. The Excel Uploaded Data
So that steps assumes that if the tblCountries have 5 fields, worksheet will have 5 columns corresponding to the fields in the table tblCountries.
And you want to know if the data in the Countries.xlsx file matches the data in tblCountries table.

Just trying to understand your requirements.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Igawa29]

Thanks Skip, defiantly don't want to be vague:

There is only one workbook in play and one worksheet.
I am having the user download the Excel template by selecting all the header rows in their selected Access Table. (This is done on another form)
Then the user will enter new data into their Excel file, and submit
Then the system should look at the table they selected and compare with the Excel template they submitted.
Once VB does the SQl for the comparison a new Excel sheet will be generated and exported with the differences between the Excel File and the Access Database
So the headers on the Excel sheet will match the headers on the Access Database Table
Thus when compared it will be a 1 to 1 compare.


Thanks too jrbbldr I am currently looking at the links that you provided. I am just trying to understand the concept of loading the Excel File into a temp table so i can compare against my permanent table.

Yep Andy that is pretty much it. I have a good idea on how to export my data results after the comparison. But like I said I am not sure how to load that Excel into a temp table.

 

[jebenson]

If you're using Excel 2007 or later, use this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path\and\filename.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Note that HDR=YES means that the first row contains column names.

In your program add this at the top:

Imports System.Data.OleDb

To actually open the Excel file in a DataTable:

Dim conn As OleDbConnection

Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<path\and\filename.xlsx>;Extended Properties="""Excel 12.0 Xml;HDR=YES""";"

'Note the extra quotation marks in the Extended Properties section of the connection string. This is done to embed quotation marks around that portion of the connection string. You may have to play around a bit to get the quotes right.

Dim dt As DataTable

dt = New DataTable

Dim SQLStr As String

SQLStr = "Select * from [WorksheetName$]" 'Note: the brackets ([]) and dollar sign ($) are required

Dim da As OleDbDataAdapter

da = New OleDbDataAdapter(SqlStr, conn)

da.Fill(dt)


All this should give you a DataTable (dt) with the Excel worksheet data.

Note also that it is important that the first row of the Excel worksheet contain only column names.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!