Populating MS Access through Excel using VB2008

L

lccengr

Member
Joined
Mar 27, 2009
Messages
15
Programming Experience
Beginner
Hello all,

I am a newbie in .net world and i am trying to populate MS Access through excel using Visual Basic.

I have created the code to take the input of excel but i don't have any idea how to go ahead. Supposing my excel has only 1 sheet with huge amount of data, and i want to upgrade my Access database everyday using excel sheet then how to do it?

Here is what i have done so far:

'opening Excel Sheet to import
Dim xlopenfile As OpenFileDialog = New OpenFileDialog()
Dim xlpath As String
xlopenfile.Title = "Select Excel Sheet"
xlopenfile.Filter = "Excel 2000 to 2003 (*.xls)|*.xls| Excel 2007 (*.xlsx)|*xlsx"
xlopenfile.InitialDirectory = "C:\"
xlopenfile.ShowDialog()
xlpath = xlopenfile.FileName()
If xlpath <> "" Then
'database connection with the path specified above
Dim xlsconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xlpath; Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
'Fetching table of Excel in to dataadapter
Dim xlda As New OleDbDataAdapter("select * from [sheet1$]", xlsconn)
End If


I know i have to write more statements in If statement, the next step is to create the access database here and then to populate the excel sheet in access table, anyone can give me an example here? it will be very helpful if you mark the comments and describe the code a bit, understanding something is far more important then to just copy paste it. Please also tell me how to get the UPDATING BAR during the access update process !!

Thanks
 
Sort by date Sort by votes
You have the begining idea, use the dataadapter to fill a dataset/datatable. Format and validate all the data in your table before transfering this data.

For the transfer I would create an Stored Procedure in your database for inserting records for your table then in the coding create a sub to activate this stored procedure and set your datatable columns as the datasource for each of the stored procedure parameters.

A progress bar could be used if/while looping through your table records and formatting the data but isnt going to provide feedback during that actual insert
 
Upvote 0 Downvote
Tom said:
You have the begining idea, use the dataadapter to fill a dataset/datatable. Format and validate all the data in your table before transfering this data.

For the transfer I would create an Stored Procedure in your database for inserting records for your table then in the coding create a sub to activate this stored procedure and set your datatable columns as the datasource for each of the stored procedure parameters.

A progress bar could be used if/while looping through your table records and formatting the data but isnt going to provide feedback during that actual insert
Click to expand...

Thanks tom for the quick reply, i would appreciate if you just add some few lines and just put some comments, it is really helpful as i m also understanding the code. Handling database with Visual Basic .net is really gr8, i may proceed to insert few things like charts etc in my applications but before that i have to complete the database part. thanks once again
 
Upvote 0 Downvote
The database record inserts is really the easy part and yes I'll give full examples ;) The prep for the import is the grunt work and its important to understand all thats being done with it. (file(s) reading, data validation, formatting values, any additional calculations or info that needs to be added, checking for dupicates if needed, splitting info into multiple tables if needed etc)

I have a few questions about your import such as the file sizes (aproximate max record counts)? Will the data need to be stored in multiple tables or a single table? For the import do you want to use transactions and any record fails import reject the whole import or just skip that record? Changing your db a Sql Server Express a possibility? What is your understanding skill level working with datasets?
 
Upvote 0 Downvote
Dear bruv,

Max records can be in almost 65000 rows (but it is variable, i have told u that i need to update my database regularly, almost daily). i don't mind changing the database to SQL SERVER 2005. I just want it to be quicker, way too quick than MS Access. The worst part is that i don't have any knowledge of SQL Server 2005 but obviously it will be helpful again if u tell me how to begin with !!

I have worked on Access 2007 but you can say it is not an expert level. I have written some queries in Access but now i want to try the front hand language (VB). I want to run the database on behind my application, all the queries should be initiated from Visual Basic.

My excel sheet will contain only one sheet, but in future i may also plan to add multiple sheets !

The nature of my application is to view the statistics from my database. Now the records could be as long as of 3-4 months and it could be in large number. I want to search an item in it and then have the graphical statistic in my application. Obviously i want to select the date range in it. This is basically what i am trying to do, there are further features which i may introduce in it but again the first part is to connect my application to a database and updating it on regular basis.
 
Upvote 0 Downvote
Seems pretty database intensive specially with daily imports of that size. I would definitely recommend bumping up to a better database such as Sql Server. I'm getting ready to leave here for the lacrosse game tonight but will be available all day tomorrow to help and start sending you some samples.

Question of curiousity is this import going to be a new feature or something you are already importing? Just wondering if it is, how your doing the import and how long its takes to complete?
 
Upvote 0 Downvote
Sure, i would love to go to SQL server 2005. infact i have started downloading it 2 :)

Actually i am a Telecom RF Optimization Engineer and i get the statistics of 1000s of sites daily. Obviously the behaviour of the sites changes everyday that is why i have to monitor my sites on daily basis. Every cell of a single site has the unique name to it, for example KHD1021, KHD1022, KHD1033 etc ... I have to monitor their cells in order to have the best performance of that site. In short, you can say that my table will have a unique name with CELLID.

For that purpose i need to make use of the graph, all my statistical data will be stored in a database. What i want to do is to enter the CELLID, for example KHD1021 and select the date range, which will show me the statistic of that site in a Line Graph. I have the MS Chart feature enabled in VB 2008 already but before making the use of it, the connection of database is necessary.

Your help in this regard is highly appreciated tom. I have used Visual Basic 6 before and few days ago i have started working with VB 2008. I have found VB 2008 much impressive, please give me some examples with comments to get start with, thank you tom !
 
Upvote 0 Downvote
A typed dataset will automatically perform data validation but to import directly to a typed dataset is problematic because it will reject the whole read operation if any of the values are invalid.

I usually start by loading the file(s) records into an untyped dataset. Then loop thru each record (progress bar can be displayed here) validate & format each of the values and added the valid record to my typed dataset which matches my database tables constraints.

Ok here is a few subs to get started. The below is an example of reading data from an Excel file and placing the results into a datatable.

VB.NET: 
    [COLOR="Blue"]Private Sub [/COLOR]btnImport_Click([COLOR="blue"]ByVal [/COLOR]sender [COLOR="blue"]As [/COLOR]System.Object, [COLOR="blue"]ByVal [/COLOR]e [COLOR="blue"]As [/COLOR]System.EventArgs) [COLOR="blue"]Handles [/COLOR]btnImport.Click

        [COLOR="blue"]Dim [/COLOR]dtExcel [COLOR="blue"]As [/COLOR]DataTable = [COLOR="blue"]Nothing[/COLOR]

        [COLOR="blue"]If [/COLOR]dlgOpen.ShowDialog <> DialogResult.OK [COLOR="blue"]Then Return[/COLOR]
        dtExcel = ImportExcelToDataTable(ExcelConnectionString(dlgOpen.FileName))
        dgv1.DataSource = dtExcel

 [COLOR="blue"]   End Sub[/COLOR]

----------------------------------------------------------------------

    [COLOR="blue"]Public Function[/COLOR] ExcelConnectionString([COLOR="blue"]ByVal [/COLOR]strFile [COLOR="blue"]As [/COLOR]String, [COLOR="blue"]Optional ByVal [/COLOR]blnHeader [COLOR="blue"]As Boolean = True, Optional ByVal[/COLOR] blnDataAsText [COLOR="blue"]As Boolean = True[/COLOR]) As String

        [COLOR="blue"]Dim [/COLOR]bldrStr [COLOR="blue"]As New[/COLOR] StringBuilder

        [COLOR="blue"]With [/COLOR]bldrStr
            .Append("Provider=Microsoft.Jet.OLEDB.4.0;")
            .Append("Data Source=""")
            .Append(strFile)
            .Append(""";")
            .Append("Extended Properties=""Excel 8.0;")

            [COLOR="blue"]If [/COLOR]blnHeader <> [COLOR="blue"]True Then [/COLOR].Append("HDR=NO;")
            [COLOR="blue"]If [/COLOR]blnDataAsText = [COLOR="blue"]True Then [/COLOR].Append("IMEX = 1;")
            .Append("""")
        [COLOR="blue"]End With [/COLOR][COLOR="seagreen"]'bldrStr[/COLOR]

        [COLOR="blue"]Return [/COLOR]bldrStr.ToString

[COLOR="blue"]    End Function[/COLOR]

----------------------------------------------------------------------

    [COLOR="blue"]Public Function[/COLOR] ImportExcelToDataTable([COLOR="blue"]ByVal[/COLOR] strXlsConnection [COLOR="blue"]As [/COLOR]String, [COLOR="blue"]Optional ByVal [/COLOR]strWorkSheet [COLOR="blue"]As [/COLOR]String = "Sheet1") As DataTable

        [COLOR="blue"]Dim [/COLOR]dtXls [COLOR="blue"]As N[/COLOR]ew DataTable

        [COLOR="blue"]Using [/COLOR]con [COLOR="blue"]As New [/COLOR]OleDbConnection(strXlsConnection)
            [COLOR="blue"]Dim [/COLOR]daXls [COLOR="blue"]As [/COLOR]New OleDbDataAdapter("Select * From [" & strWorkSheet & "$]", con)
            daXls.Fill(dtXls)
        [COLOR="blue"]End Using [/COLOR][COLOR="SeaGreen"]'con[/COLOR]

        [COLOR="blue"]Return [/COLOR]dtXls

[COLOR="blue"]    End Function[/COLOR]
 
Upvote 0 Downvote
In order to validate & format the data I will need to get details about your file & data first. So to skip past this part for the time being I'll explain the actual import of the records to the database after the validation has taken place.

I mentioned earlier using stored procedures for the import but since you are now using Sql Server instead we can take advantage of the sqlBulkCopy method which is even faster for record insertion.

The example below also includes the use of transactions so there is not any partial imports if any of the records fail to be transferred (all or nothing).

VB.NET: 
        [COLOR="Blue"]Dim [/COLOR]conBatch [COLOR="blue"]As New [/COLOR]SqlConnection
        [COLOR="blue"]Dim [/COLOR]trans [COLOR="blue"]As [/COLOR]SqlTransaction = Nothing

        [COLOR="blue"]Try[/COLOR]
            [COLOR="SeaGreen"]'Open db connection and assign transaction[/COLOR]
            conBatch.ConnectionString = strYourConnectionStringHere
            conBatch.Open()
            trans = conBatch.BeginTransaction

            [COLOR="seagreen"]'Bulk Copy records to Database[/COLOR]
            [COLOR="blue"]Using [/COLOR]bcpPck [COLOR="blue"]As New [/COLOR]SqlBulkCopy(conBatch, SqlBulkCopyOptions.Default, trans)
                bcpPck.BatchSize = 0 
                bcpPck.DestinationTableName = "tblNameHere"
                bcpPck.WriteToServer(dsXml.Tables("tblNameHere"))
            [COLOR="blue"]End Using[/COLOR]

            trans.Commit()
        [COLOR="blue"]Catch [/COLOR]ex [COLOR="blue"]As [/COLOR]Exception
            trans.Rollback()
            Messagebox.Show(ex.Message)
            Return
        [COLOR="blue"]Finally[/COLOR]
            trans.Dispose()
            conBatch.Close()
            conBatch.Dispose()
        [COLOR="blue"]End Try[/COLOR]
 
Upvote 0 Downvote
Thanks bruv, little comments would be helpful, i am seeing 2 functions in it that are populating it, do you recommend using nested for loop?

I'll go through with this technique and let you know, i still dont have any experience of SQL Server 2005. But thank you, i really appreciate it !


Tom said:
In order to validate & format the data I will need to get details about your file & data first. So to skip past this part for the time being I'll explain the actual import of the records to the database after the validation has taken place.

I mentioned earlier using stored procedures for the import but since you are now using Sql Server instead we can take advantage of the sqlBulkCopy method which is even faster for record insertion.

The example below also includes the use of transactions so there is not any partial imports if any of the records fail to be transferred (all or nothing).

VB.NET: 
        [COLOR="Blue"]Dim [/COLOR]conBatch [COLOR="blue"]As New [/COLOR]SqlConnection
        [COLOR="blue"]Dim [/COLOR]trans [COLOR="blue"]As [/COLOR]SqlTransaction = Nothing

        [COLOR="blue"]Try[/COLOR]
            [COLOR="SeaGreen"]'Open db connection and assign transaction[/COLOR]
            conBatch.ConnectionString = strYourConnectionStringHere
            conBatch.Open()
            trans = conBatch.BeginTransaction

            [COLOR="seagreen"]'Bulk Copy records to Database[/COLOR]
            [COLOR="blue"]Using [/COLOR]bcpPck [COLOR="blue"]As New [/COLOR]SqlBulkCopy(conBatch, SqlBulkCopyOptions.Default, trans)
                bcpPck.BatchSize = 0 
                bcpPck.DestinationTableName = "tblNameHere"
                bcpPck.WriteToServer(dsXml.Tables("tblNameHere"))
            [COLOR="blue"]End Using[/COLOR]

            trans.Commit()
        [COLOR="blue"]Catch [/COLOR]ex [COLOR="blue"]As [/COLOR]Exception
            trans.Rollback()
            Messagebox.Show(ex.Message)
            Return
        [COLOR="blue"]Finally[/COLOR]
            trans.Dispose()
            conBatch.Close()
            conBatch.Dispose()
        [COLOR="blue"]End Try[/COLOR]
Click to expand...
 
Upvote 0 Downvote
I'm not sure what you mean by 2 functions but I certainly can add more comments and answer any questions you have. Also if you need any help setting up Sql Server just ask, I think its easier to work with then access though.

VB.NET: 
        [COLOR="Blue"]Dim [/COLOR]conBatch [COLOR="blue"]As New [/COLOR]SqlConnection
        [COLOR="blue"]Dim [/COLOR]trans [COLOR="blue"]As [/COLOR]SqlTransaction = Nothing

        [COLOR="blue"]Try[/COLOR]
            [COLOR="SeaGreen"]' Assigns your connection string and opens connection to the database[/COLOR]
            conBatch.ConnectionString = strYourConnectionStringHere
            conBatch.Open()

           [COLOR="SeaGreen"] '  Adds a transaction to the database connection object
[/COLOR]
            trans = conBatch.BeginTransaction

[COLOR="seagreen"]
            ' A dataadapter passes the information back 
            ' and forth to the database from your dataset/datatable.
            ' The line below creates a new data adapter
            ' telling it which db connection to use, and that 
            ' we dont want any partial import if any record
            ' fails so we use this tranasaction.[/COLOR]
                                       
            [COLOR="blue"]Using [/COLOR]bcpPck [COLOR="blue"]As New [/COLOR]SqlBulkCopy(conBatch, SqlBulkCopyOptions.Default, trans)

                [COLOR="seagreen"]'Batch size tells the data adapter how many records 
                'to send to the server with each call
                'Setting it to zero tells it to use the max available[/COLOR]
                bcpPck.BatchSize = 0 

                [COLOR="seagreen"]'This line tells it which database table we want to write too[/COLOR]
                bcpPck.DestinationTableName = "tblNameHere"
 
                [COLOR="seagreen"]'Here we pass our source records to be written to the database[/COLOR]
                bcpPck.WriteToServer(dsXml.Tables("tblNameHere"))
            [COLOR="blue"]End Using[/COLOR]

            [COLOR="seagreen"]'Save import results as long as there were no errors at all[/COLOR]
            trans.Commit()
        [COLOR="blue"]Catch [/COLOR]ex [COLOR="blue"]As [/COLOR]Exception
            [COLOR="seagreen"]'If there was an error inserting any record in the file, 
            'dont add any records at all to the database[/COLOR]
            trans.Rollback()
            Messagebox.Show(ex.Message)
            Return
        [COLOR="blue"]Finally[/COLOR]
            trans.Dispose()
            conBatch.Close()
            conBatch.Dispose()
        [COLOR="blue"]End Try[/COLOR]
 
Upvote 0 Downvote
I mean to say that i have taken the input from excel in a different way and you have used functions in it. I know how to use functions but more comments from you would be helpful. Also please tell me why you haven't prefered my way of taking input from excel?

I would appreciate if you also give me a brief setup guide for SQL Server 2005. I have downloaded the enterprise addition for it, if there is any document then kindy share it, thanks Tom
 
Upvote 0 Downvote
lccengr said:
I mean to say that i have taken the input from excel in a different way and you have used functions in it. I know how to use functions but more comments from you would be helpful. Also please tell me why you haven't prefered my way of taking input from excel?

I would appreciate if you also give me a brief setup guide for SQL Server 2005. I have downloaded the enterprise addition for it, if there is any document then kindy share it, thanks Tom
Click to expand...

Oh ok, I figured you were talkin about the latter code and went back and added more comments. Breaking it up into subs/functions is more for re-usability. I place these subs & functions into a module file within a project and whenever I have to input data from any Excel file or worksheet all I have to do is place a single calling line in my form to fill any datatable such as

dtExcel = ImportExcelToDataTable(ExcelConnectionString(dlgOpen.FileName))

No need to rewrite the whole block of code such as you had.

With Sql Server do you need help setting up the database itself or installing the software?
 
Upvote 0 Downvote
Tom said:
Oh ok, I figured you were talkin about the latter code and went back and added more comments. Breaking it up into subs/functions is more for re-usability. I place these subs & functions into a module file within a project and whenever I have to input data from any Excel file or worksheet all I have to do is place a single calling line in my form to fill any datatable such as

dtExcel = ImportExcelToDataTable(ExcelConnectionString(dlgOpen.FileName))

No need to rewrite the whole block of code such as you had.

With Sql Server do you need help setting up the database itself or installing the software?
Click to expand...

Oh dat is ok. I need help setting up the database and installing as well. One thing i wonder is that most of the people who works in an OFFICE environment usually have Office 2003/2007 (ACCESS DATABASE). I don't know if my application will be able to run on those standalone machines where SQL Server 2005 is not installed. I guess the answer is "NO". Please comment because many people here doesn't have the SQL Server 2005 and there is no dedicated machine at which people can login and make it as a centralized database.

In that case, what do you recommend tom? I know SQL Server 2005 is a better choice but can everyone easily use it without installing the complete enterprise suite ?
 
Upvote 0 Downvote
Tom said:
A typed dataset will automatically perform data validation but to import directly to a typed dataset is problematic because it will reject the whole read operation if any of the values are invalid.

I usually start by loading the file(s) records into an untyped dataset. Then loop thru each record (progress bar can be displayed here) validate & format each of the values and added the valid record to my typed dataset which matches my database tables constraints.

Ok here is a few subs to get started. The below is an example of reading data from an Excel file and placing the results into a datatable.

VB.NET: 
    [COLOR="Blue"]Private Sub [/COLOR]btnImport_Click([COLOR="blue"]ByVal [/COLOR]sender [COLOR="blue"]As [/COLOR]System.Object, [COLOR="blue"]ByVal [/COLOR]e [COLOR="blue"]As [/COLOR]System.EventArgs) [COLOR="blue"]Handles [/COLOR]btnImport.Click

        [COLOR="blue"]Dim [/COLOR]dtExcel [COLOR="blue"]As [/COLOR]DataTable = [COLOR="blue"]Nothing[/COLOR]

        [COLOR="blue"]If [/COLOR]dlgOpen.ShowDialog <> DialogResult.OK [COLOR="blue"]Then Return[/COLOR]
        dtExcel = ImportExcelToDataTable(ExcelConnectionString(dlgOpen.FileName))
        dgv1.DataSource = dtExcel

 [COLOR="blue"]   End Sub[/COLOR]

----------------------------------------------------------------------

    [COLOR="blue"]Public Function[/COLOR] ExcelConnectionString([COLOR="blue"]ByVal [/COLOR]strFile [COLOR="blue"]As [/COLOR]String, [COLOR="blue"]Optional ByVal [/COLOR]blnHeader [COLOR="blue"]As Boolean = True, Optional ByVal[/COLOR] blnDataAsText [COLOR="blue"]As Boolean = True[/COLOR]) As String

        [COLOR="blue"]Dim [/COLOR]bldrStr [COLOR="blue"]As New[/COLOR] StringBuilder

        [COLOR="blue"]With [/COLOR]bldrStr
            .Append("Provider=Microsoft.Jet.OLEDB.4.0;")
            .Append("Data Source=""")
            .Append(strFile)
            .Append(""";")
            .Append("Extended Properties=""Excel 8.0;")

            [COLOR="blue"]If [/COLOR]blnHeader <> [COLOR="blue"]True Then [/COLOR].Append("HDR=NO;")
            [COLOR="blue"]If [/COLOR]blnDataAsText = [COLOR="blue"]True Then [/COLOR].Append("IMEX = 1;")
            .Append("""")
        [COLOR="blue"]End With [/COLOR][COLOR="seagreen"]'bldrStr[/COLOR]

        [COLOR="blue"]Return [/COLOR]bldrStr.ToString

[COLOR="blue"]    End Function[/COLOR]

----------------------------------------------------------------------

    [COLOR="blue"]Public Function[/COLOR] ImportExcelToDataTable([COLOR="blue"]ByVal[/COLOR] strXlsConnection [COLOR="blue"]As [/COLOR]String, [COLOR="blue"]Optional ByVal [/COLOR]strWorkSheet [COLOR="blue"]As [/COLOR]String = "Sheet1") As DataTable

        [COLOR="blue"]Dim [/COLOR]dtXls [COLOR="blue"]As N[/COLOR]ew DataTable

        [COLOR="blue"]Using [/COLOR]con [COLOR="blue"]As New [/COLOR]OleDbConnection(strXlsConnection)
            [COLOR="blue"]Dim [/COLOR]daXls [COLOR="blue"]As [/COLOR]New OleDbDataAdapter("Select * From [" & strWorkSheet & "$]", con)
            daXls.Fill(dtXls)
        [COLOR="blue"]End Using [/COLOR][COLOR="SeaGreen"]'con[/COLOR]

        [COLOR="blue"]Return [/COLOR]dtXls

[COLOR="blue"]    End Function[/COLOR]
Click to expand...
Dear Tom,

When i try to write this code, i get the following error

dlgOpen is not declared
dgv1 is not declared
StringBuilder is not declared

I see that you haven't declared them as a variable and just used it in a code. Can you please clear it?
 
Last edited:
Upvote 0 Downvote

Similar threads

B
  • Question
Question add rows to excel
Replies
6
Views
2K
Rythorian
Rythorian
H
  • Question
Fill cells with color
Replies
2
Views
905
jdelano
J
J
  • Question
I can see the Excel Workbook, but the WorkSheet methods etc don't work :(
Replies
4
Views
3K
johnhoens
J
T
  • Question
Resolved how i check if excel sheet is close to attach the file to Gmail
Replies
0
Views
1K
Tamiryosef
T
J
  • Solved
Can't find where my mistake is
Replies
11
Views
841
jdelano
J

Share this page

Latest posts

Back
Top