Solved

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

Posted on 2006-06-30
8
494 Views
Last Modified: 2008-02-16
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.

0
Comment
Question by:Freerider
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17021399
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

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21905295.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
0
 

Author Comment

by:Freerider
ID: 17023627
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?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17023712
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Freerider
ID: 17023811
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.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17023887
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
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17023902
Oops

            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

in that last post should read

            For i As Integer = 1 To sourceReader.FieldCount - 1
                '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

Roger
0
 

Author Comment

by:Freerider
ID: 17024039
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.
0
 

Author Comment

by:Freerider
ID: 17024040
OOPS. I accepted the wrong comment.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 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