Link to home
Start Free TrialLog in
Avatar of Freerider
FreeriderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

URGENT - Import Excel data into .mdb with vb.net

Hi all,

I am using Visual Studio 2005 (VB) and have an application which uses MS Access database to hold customer details. I would like to add a function to my app which imports data from Excel spreadsheet. The dataset already contains data, so the excel data should be appended.

How can I fill my dataset with the data from the spreadsheet? I assume this is the best way to get the data into the .mdb

Thanks.

Avatar of Sancler
Sancler

Try this

    Private Sub ExcelToAccess(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String)

        'Sub transfers all records from .xls sourcefile.sourcesheet ...
        '... to .mdb targetfile.targettable
        'It is assumed that the .mdb targettable definition already ...
        '... exists, with the same number and types of fields, ...
        '... in the same order, as the .xls worksheet columns.
        'It does not depend on the .mdb field names being the same ...
        '... as the .xls column headings: although it does assume ...
        '... that the .xls columns are named.

        If Not sourceSheet.EndsWith("$") Then
            sourceSheet &= "$"
        End If

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim sourceSQL As String = "SELECT * FROM [" & sourceSheet & "]"
        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT * FROM " & targetTable

        'use dataadapter for target and command builder to ...
        '... create insert command, including parameter collection
        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

        'now do the work
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'for each row from source
            For i As Integer = 0 To sourceReader.FieldCount - 1
                'load values into parameters
                targetCommand.Parameters(i).Value = sourceReader(i)
                'if any of the source data needs to be modified ...
                '... for the target, it could be done here ...
            Next
            '... or here
            'then write to target
            targetCommand.ExecuteNonQuery()
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()

    End Sub

I've just in fact pulled it out for another question

https://www.experts-exchange.com/questions/21905295/I-know-why-my-data-will-not-merge-but-what-other-approach-can-I-take.html

and you might like to check out my comments in that.  You might not need to alter quite so much as I described there, but unless the Excel and Access fields are completely compatible, some fiddling might be necessary.

Roger
Avatar of Freerider

ASKER

Thanks Roger,

I have added the code above, but get the error:
"Invalid index 14 for this OleDbParameterCollection with Count=14."
at the line:
targetCommand.Parameters(i).Value = sourceReader(i)

I have included the correct parameters for ExcelToAccess("<path>\Customers.xls", "Sheet1", "<path>\Customers.mdb", "Customers")

Any ideas what could be causing this?
The parameters collection is indexed starting from 0, so the highest valid index for a count of 14 is 13.  On the assumptions stated in the comments

>>
        'It is assumed that the .mdb targettable definition already ...
        '... exists, with the same number and types of fields, ...
        '... in the same order, as the .xls worksheet columns.
<<

that shouldn't be a problem, because the number of parameters in the target command (built from the .mdb target table structure) should be identical to the number of values in each source row, and the iteration is

            For i As Integer = 0 To sourceReader.FieldCount - 1

that is, the - 1 at the end specifically takes account of the point above.  So what it looks like here is that your excel input has more columns than the .mdb target.  The target has 14 columns and the input has (at least) 15.

The explanation may well be a Primary Key.  Does your .mdb table have a Primary Key and, if so, is it Autonumber?  In that case the Insert Statement that the Command Builder makes will not include it and nor will the Parameter collection.  To overcome that you will need to do a bit of "fiddling" of the sort I mentioned.  The best bet is probably for you to stick in some (temporary) debugging code to see what's not matching up.  To find out what the automatically built Insert statement looks like, put this

        Debug.WriteLine(targetCommand.CommandText)

anywhere after

        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

and, if you want to see the Parameters, use something like this

       For Each param In targetCommand.Parameters
              Debug.WriteLine(param.SourceColumn)
       Next

Or you could use any other property instead of, or besides, SourceColumn.

To see what is coming in, you could use code like

            For i As Integer = 0 To sourceReader.FieldCount - 1
                Debug.WriteLine(sourceReader.GetName(i))
            Next

Or again you could use some other method instead of, or besides, GetName

With that information you should be able to identify any mismatch and adjust the swapover so it is not just

            For i As Integer = 0 To sourceReader.FieldCount - 1

If you have problems doing that, copy and paste the code from the debug output into another post here and I'll have a look at it.

Roger
I haven't put in the debug code yet as the .mdb table DOES have a Primary Key and, it IS Autonumber.

The fields do match up cos I exported the .xls file from Access and deleted all but one row. I tried typing in a value for the first field (Customer_ID) (Primary Key) but that didn't help cos its Autonumber.

I'm not sure of the knid of "fiddling" involved to insert the Primary Key.
When you exported the file from Access to Excel it had (on the evidence so far) 15 columns.  They included the value in the Access Primary Key column.  When you try to put it back with the code provided it is going to be inserted.  That means that .NET and the Jet engine are going to say that the incoming record is going to have to provide 14 columns.  The other one - the Primary Key - is going to be generated autmatically by the .mdb table itself.  It names those 14 columns that are going to provided in the Insert statement and provides 14 parameters for them.  It ignores the Primary Key in the .mdb table.  The code orignally provided then tries to put all the values from the 15 columns in the Excel table into the 14 parameters in the Insert command.  Not surprisingly, they won't go.  So what we need to do is exclude the column in Excel that originally came from the Access Primary Key.  If (as is quite possibly the case given a common structure for Access tables) it is the first column in the Excel table the "fiddling" that is required is as simple as

            For i As Integer = 1 To sourceReader.FieldCount
                'load values into parameters
                targetCommand.Parameters(i - 1).Value = sourceReader(i)
                'if any of the source data needs to be modified ...
                '... for the target, it could be done here ...
            Next

That is, we just put every value from excel in a parameter numbered one lower.  We ignore the first, Primary Key, column in Excel just like we know the Insert statement is doing in Access.  What was column 1 in Excel will go in parameter number 0 for Access.  It will still go into column 1 (rather than column 0) in Access because (a) column 0 in Access is not affected by the Insert statement - it does not appear in the Insert statement and there is no parameter for it - (b) Access will be filling column 0 itself with an autogenerated Primary Key value (c) the value in parameter 0 is going to go in the first field/column named in the Insert statement which will be column 1 in the Access table.

But it won't always be as simple as that.  If the Primary Key value that we have to ignore is in column 6 then we need to transfer on the basis of the original code for the columns before that but on the alternative basis for the columns after that.

I suggested the debug.code because that should, if I have diagnosed the situation correctly, have illustrated the general points above with field names that you recognised.  If the general explanation above is confusing, that might still be a good idea.

Another test you might do is, in the Excel table, having imported it from Access but before you try to transfer it back, delete the column that represents the Access Primary Key.  If that is the problem then the original code should work as is after such a deletion.

Does that give you sufficient to overcome the problem?  If not, come back.

Roger
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Roger!

I will need to read over your explanation a few times to really understand whats happening.

I removed the first column of the spreadsheet (Primary Key) and it works perfectly using the original code.

This is a very important part of my app - I really appreciate the detailed explanation.

Thanks again,
Andrew.
OOPS. I accepted the wrong comment.