Results 1 to 3 of 3
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Suggestions for best way to import in data


    Hi all,

    I am working on designing an import process for our DB and I wanted to get your suggestions on the best way to design it. Basically, I need to import in complaints to the DB via excel. The department that sends me the complaints sends them via CSV excel file and its literally every historical complaint, so its not just only the new ones.

    I was thinking that I would set up a VBA import code that imports the whole file into a temp table, then append the temp table into the normal table based on the complaint ID number (a unqieu number to each complaint, thereby excluding the complaints already in the normal table), and then lastly a delete query wipes out the temp table.

    Thoughts? Suggestions on different ways to do it?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe could just set a link to the Excel/CSV instead of using temp table. If sheet structure always same and file always saved with same name, link would persist. Either use the linked 'table' same as a local table (except cannot edit data) or then INSERT SELECT records to local table.

    It is possible to connect to Excel/CSV and import directly, example for Excel:
    Code:
    Sub getExcelData()
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    
    'set connection to worksheet and open recordset
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\June\Condos.xlsx" _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    rs.Open "SELECT * FROM [Sheet1$A1:C10]", cn, adOpenDynamic, adLockOptimistic
    Debug.Print rs!Unit
    
    
    'use DAO SQL to import to table
    CurrentDb.Execute "INSERT INTO [tablename] SELECT * FROM [Excel 12.0 Xml;HDR = Yes;Database=C:\Users\June\Condos.xlsx].[Sheet1$]"
    
    
    End Sub
    "Best" method can be influenced by size of Excel/CSV file. Since you indicate the data is cumulative, it could get quite unwieldy over time.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you are sent a csv file, import as csv, excel just likes to 'take over'.

    However I would link to the file rather than importing and use that to append your data. Or just use a sql query. The basic select query (equivalent to a linked table) would be something like



    SELECT *
    FROM (SELECT * FROM [TEXT;DATABASE=C:\somefolder;HDR=Yes].csvfilename.csv) AS txt


    this can then be modified in the query builder view to left join to your destination table, using a null criteria to identify new records

    INSERT INTO desttable ( fldname1, fldname2, etc)
    SELECT fldname1, fldname2, etc
    FROM (SELECT * FROM [TEXT;DATABASE=C:\somefolder;HDR=Yes].csvfilename.csv) AS txt LEFT JOIN desttable ON txt.uniqueID = desttable.uniqeID
    WHERE destable.uniqueID Is Null

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

Similar Threads

  1. Import Linked Outlook Tasks Folder Doesn't Import All Data
    By Bkper087 in forum Import/Export Data
    Replies: 0
    Last Post: 11-19-2019, 12:06 AM
  2. Address formats in data bases? Suggestions ?
    By d9pierce1 in forum Forms
    Replies: 4
    Last Post: 10-31-2019, 03:09 PM
  3. Suggestions on Mass Import of Data
    By TerraEarth in forum Import/Export Data
    Replies: 11
    Last Post: 05-02-2018, 02:33 PM
  4. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  5. Replies: 2
    Last Post: 02-27-2013, 10:40 AM

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