Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

read excel to a recordset using an array

Ultimately, I am trying to read from an excel workbook and write that data to an access db.  I am using the following for loop to read through excel and this works fine.  however, the excel book is huge and the code i am using now takes a long time since it reads one cell at a time and updates the rs each time.  how can i make this go faster (possibly by reading in an entire row of data to an array at one time)?

For ksheet = 1 To sheetNum
            worksheet = xlBook.Sheets(ksheet)
            lastcell = worksheet.Cells.SpecialCells(xlconstants.xlLastCell)
            row = lastcell.Row
            col = lastcell.Column
            For jcol = 2 To col
                    For irow = 2 To row
                   oRS.AddNew()

                    'write each field into the new record

                    oRS.Fields("Juris").Value = worksheet.Cells(irow, 1)
                    oRS.Fields("Month").Value = worksheet.Cells(1, jcol)
                    oRS.Fields("Type").Value = worksheet.Name
                    oRS.Fields("Count").Value = worksheet.Cells(irow, jcol)
                   
                    'commit changes to database
                    oRS.Update()
                Next
            Next
           
        Next
0
SysCapstone
Asked:
SysCapstone
  • 5
  • 4
  • 2
1 Solution
 
JRossi1Commented:
What version of .Net are you using?

0
 
JRossi1Commented:
it looks as if you are using ADO and NOT ADO.NET.  If that is the case then you can use the UpdateBatch method

oRS.ActiveConnection = cn
oRS.CursorType = adOpenStatic
oRS.LockType = adLockBatchOptimistic <----- All updates are made to the database after your loop is complete

Your loop:

    For ksheet = 1 To sheetNum
            worksheet = xlBook.Sheets(ksheet)
            lastcell = worksheet.Cells.SpecialCells(xlconstants.xlLastCell)
            row = lastcell.Row
            col = lastcell.Column
            For jcol = 2 To col
                    For irow = 2 To row
                   oRS.AddNew()

                    'write each field into the new record

                    oRS.Fields("Juris").Value = worksheet.Cells(irow, 1)
                    oRS.Fields("Month").Value = worksheet.Cells(1, jcol)
                    oRS.Fields("Type").Value = worksheet.Name
                    oRS.Fields("Count").Value = worksheet.Cells(irow, jcol)
                   
                    'commit changes to database  
                    oRS.Update()  <------ No longer updating after each iteration.  Remove this line.
                Next
            Next
        Next

oRS.UpdateBatch  <------ write all new records to the table in one shot

This will be alot faster.  
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.

 
SysCapstoneAuthor Commented:
the updatebatch makes it much quicker, but it is still taking about 10 minutes to do one sheet in my workbook, and there are about 50 sheets (each one has about 20,000 records to copy).  Is there anyway I can change the code that I have already or will i need to something more like what Sancler said...only i think it may be a problem since the records are not organized in the excel sheet as they will be in the access db.
0
 
SanclerCommented:
SysCapstone

Sorry ;-).  My posting was really just a knee-jerk reaction to reading "Ultimately, I am trying to read from an excel workbook and write that data to an access db" on the first line of your post.  Now that I have actually read your code, I don't think the approach that I pointed to would be appropriate.  Specifically, as you are not transferring specific row to specific row - but making up the access row from different rows in the excel sheet - the row-specific datareader approach on the excel side certainly wouldn't work.

It MIGHT be quicker using ADO.NET to read each complete excel sheet into a datatable and do the iterations in that rather than getting the data cell by cell, but I haven't tested.  One point I would make, though, is that it might be worth testing to see whether it's the reading or the writing that is taking the time.  I was surprised, when I was working on that other question, to find that most of the time was taken up with writing to the Access table rather than with reading from the excel sheet or juggling the data between input and output rows.  The write to access was speeded up by making sure that the Access database was "clean and tidy" - i.e. had recently been compacted - but, even in ideal conditions, that process took a (relatively large) irreducible minimum of processing time.

Roger
0
 
JRossi1Commented:
Perhaps a change in approach is in order.  Why not link the Excel Worksheets to the access database.  Then you can run append queries in Access to update the tables that you need, or, you can use DAO in an Access module to update the tables.  DAO is much faster than ADO when used in an Access application.
0
 
SysCapstoneAuthor Commented:
JRossi1, how would I make the switch to DAO?  I am relatively new with VB.net and am having difficulty piecing this together.  I have posted the entire function below, hope this helps.


Public Function WriteToAccess(ByVal sSourceData As String, ByRef oRS As ADODB.Recordset, ByVal sDBpath As String) As Boolean
        oRS = New ADODB.Recordset
        oRS.CursorType = CursorTypeEnum.adOpenStatic
        oRS.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
        'Open the ADO connection to the Excel workbook
        Dim oConn As New ADODB.Connection
        oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDBpath & ";" & "Persist Security Info=False"
        oConn.Open()
        oRS.Open("SELECT * FROM CrimeData WHERE 1 = 2", oConn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

        Dim xlBook As Excel.Workbook
        xlBook = GetObject(sSourceData)
        Dim worksheet As Excel.Worksheet

        Dim irow As Integer
        Dim jcol As Integer
        Dim ksheet As Integer
        Dim sheetNum As Integer
        sheetNum = xlBook.Sheets.Count
        Dim lastcell As Object
        Dim row As Integer
        Dim col As Integer
        Dim year As Integer

        If Not oRS.Supports(CursorOptionEnum.adAddNew) Then
            MsgBox("RecordSet does not support adding new records!")
        End If

        On Error Resume Next

        For ksheet = 1 To sheetNum
            worksheet = xlBook.Sheets(ksheet)
            lastcell = worksheet.Cells.SpecialCells(xlconstants.xlLastCell)
            row = lastcell.Row
            col = lastcell.Column
            For jcol = 2 To col
                    For irow = 2 To row
                   oRS.AddNew()

                    'write each field into the new record

                    oRS.Fields("Juris").Value = worksheet.Cells(irow, 1)
                    oRS.Fields("Month").Value = worksheet.Cells(1, jcol)
                    oRS.Fields("Type").Value = worksheet.Name
                    oRS.Fields("Count").Value = worksheet.Cells(irow, jcol)
                   
                    'commit changes to database  
                   
                Next
            Next
        Next
        'commit changes to database
        oRS.UpdateBatch()

        oRS.Close()
        oConn.Close()

        MessageBox.Show("records added!")
        ProgressBar1.Visible = False
    End Function
0
 
JRossi1Commented:
This approach does not utilize VB.NET.  What I am suggesting is this:
1) Link the Excel Spreadsheets to your access database, just like you would link to any other table.
2) Now you can do one of 2 things:
   a) Create append queries in access to copy the data from your linked tables to the access tables
   b) Write some vba code in access utilizing DAO.  The vba code would look something like this (you may have to tweek this in order to get it to work propertly:

Public Sub GetData(strExcelLinkedTableName as string)
    Dim DB as Database
    Dim strSQL as String

    Set DB = CurrentDB()

    strSQL = "INSERT INTO CrimeData SELECT * FROM " & strExcelLinkedTableName

    DB.Execute, dbFailOnError

    DB.close
    Set DB = Nothing

End Sub

That's it.  All you have to do is create a wrapper function to feed each of the linked table names to this procedure, error handling etc....  

No more loops. This will be real fast.




0
 
SysCapstoneAuthor Commented:
A user of this program will be selecting the Excel workbook to convert to access.  I then create a new access db then perform the WriteToAccess function.  Will this still work?
0
 
SysCapstoneAuthor Commented:
points increased
0
 
JRossi1Commented:
There are a few things to consider.  If the spreadsheets are centralized and the names won't change, then all you have to do is link them to your access db once and create a simple interface in access that the user can use to load the data.

If the spreadsheets are not centralized (they reside on each users local machine) and the names can be different, this creates complexity if you go the access route since the Excel spreadsheet would have to dynamically link to the access db. Probably not worth the trouble for what is essentially a performance issue.

Based on what I know I would take the following approach if possible:
1) Centralize the spreadsheets.
2) Link the spreadsheets to your Access db
3) Add the aforementioned procedure to a module.
4) Add a simple form to the access db that feeds each linked table name to the above procedure to load the data into the appropriate table.
5) Give the users access to the form so that they can load the data themselves

If this is not feasible, then your original approach, using excel to loan the data into access is the way to go (using UpdateBatch to improve performace).  

You are loading 1,000,000 records into an Access database.  This process is going to take a good amount of time no matter what you do.  If possible, you may want to consider using SQL Server instead of access.  You can use DTS to load the data from Excel into the DB, but that is a topic for another day.
0

Featured Post

Industry Leaders: 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!

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now