Solved

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

Posted on 2006-06-30
8
490 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now