Results 1 to 5 of 5
  1. #1
    gbmarlysis is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    48

    Adding large amount of Excel data into Access

    Hi,

    I have 250 separate worksheets with a lot of data to put into Access. Problem is the data is 120,000 rows in each worksheet and a lot of duplicate date eg


    DATE, NAME, TIME,are some of the column headings and there are multiple rows with same DATE or NAME. That is just how I received the data. I would like to transfer all records into Access as quickly and efficiently as possible.

    Regards

    Luke

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you mean 250 WORKBOOKS in a folder then heres some code that scans all files in the folder and imports them to the table

    Code:
    sub LoadAllXLFiles()
      ScanAllFilesInDir "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\Patient 47"
    end sub
    
    '----------------
    Public Sub ScanAllFilesInDir(ByVal pvDir)
    '----------------
    Dim vFil, vTargT
    Dim i As Integer
    Dim fso
    Dim oFolder, oFile
    Dim  vTbl
    
    On Error GoTo errImp
    If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
    
    
    sTbl = "xlFile"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(pvDir)
    
    
    For Each oFile In oFolder.Files
        vFil = pvDir & oFile.Name
        If InStr(vFil, ".xls") > 0 Then      'ONLY DO XL FILES
               DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, vTbl, vFil ,True
        End If
    Next
    
    
    Set fso = Nothing
    Set oFile = Nothing
    Set oFolder = Nothing
    MsgBox "Done"
    Exit Sub
    
    
    errImp:
    MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
    Exit Sub
    Resume
    End Sub

  3. #3
    gbmarlysis is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    48
    Hi Ranman,

    Can you lead me to where I would put that in. What do I do about all the duplicate data? Do I set up a table with same headings as Excel Column headings and then go to Macro in Access.

    Regards

    Luke

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    sorry, this is a lot for a novice.....

    if you want NO duplicate , the you need to KEY the field you dont want duplicated.

    you can import 1 excel file to a table, delete the data, then key the field.
    now you have all the fields and names and are ready.

    to use the code:
    click ctl-G (this will put you in VBE mode)
    click INSERT
    module
    paste ALL the code here
    save as module1

    now build a form, put a button on it,
    in the button click event , put LoadAllXLFiles

    OR
    instead of the form method, you can put it in a macro IF...
    You change
    sub LoadAllXLFiles()
    to a function
    FUNCTION LoadAllXLFiles()

    then you can just run a macro
    create a new macro,
    in the action put RUNCODE
    put in LoadAllXLFiles()
    save the macro.

    running the macro will import all files. Be sure to set YOUR table name where the code says
    transferspreadsheet VTBL

  5. #5
    gbmarlysis is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    48
    Hi,

    Would you set it up right the first time. create separate tables so no data is duplicated. How would that code work if I wanted the Excel data split into separate tables. As a novice the only way I can think of is create a macro in Excel where I delete any duplicated data then run code from access to import. The Excel worksheets contain a lot of data and I want to enter in 200 worksheets but I will have to add new worksheets regularly. I would like the data set out right in Access so when it grows it runs efficiently and I can run reports and queries on that data. If I can get a bit of a detailed response that would be great as a lot of aspects of Access you take for granted as it is common knowledge but I have only just begun.

    Regards
    Luke

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding another date code amount
    By Thompyt in forum Programming
    Replies: 6
    Last Post: 05-22-2015, 03:06 PM
  2. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  3. Need help exporting large Access file into Excel
    By phidelt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 06:14 PM
  4. Replies: 6
    Last Post: 12-03-2012, 08:08 AM
  5. Excel file that I export from Access is extremely large
    By Ronald Mcdonald in forum Access
    Replies: 1
    Last Post: 05-25-2012, 03:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums