Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-06-30
8
Medium Priority
?
500 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
[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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Technology Partners: 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!

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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