Importing CSV Data into existing Access database

[tina51581]

I'm not very experienced with Access, but a client is currently using it as a basic CRM system. They are pulling in some sales data from Quickbooks to create an entry for products sold (using the Serial number as the unique identifier). Then we'll be getting a ton more data from a form on their website to update that entry (customer name, address, etc). These fields exist in Access, but are empty until the form data comes in.

I want to be able to export a CSV of the data submitted to the website, then import it into Access using the Serial Number as the identifier and having all the fields in Access updated with the information that was loaded in the form (and is now in the CSV).

Guides?

 

[Cofad]

It seems like you'll have to import the web data to a new table and create a relationship between the two tables using the serial number. You can then process the data however you need to.

 

[SydneyGeek]

Import the data into a staging table. Once the data is there you can use queries to determine which IDs already exist in your main table (and run some updates against those records, or ignore them) and then append the records that don't yet have an ID.

To import a bunch of text (or csv) files, look at this tutorial... Importing Text Files Into Access -- DataWright Information Services
Note -- the import routine uses Application.FileSearch, which will not work in Access 2007 and higher. Substitute the import code with this, which uses the Dir function instead:

Function ImportCSVFiles()
    ''Create the archive folder BEFORE running this function, if you want to archive the files
    Dim FilesToProcess As Integer
    Dim i As Integer
    Dim bArchiveFiles As Boolean
    Dim sTargetFile As String
    Dim sFileName As String
    Dim sOutFile As String
    Const TOP_FOLDER = "C:\Users\Denis\Documents\Import Test" 'adjust folder name to suit
    Const ARCHIVE_FOLDER = "C:\Users\Denis\Documents\Import Test\Imported" 'adjust folder name to suit
    Const DEST_TABLE = "stgTimesheet" 'change to suit
    Const IMPORT_SPEC = "Timesheet_import" 'change to suit
    Const PATH_DELIM = "\"
    
    'set to False if you DON'T want to move imported files to new folder
    bArchiveFiles = True
    
    'use the Dir function to loop through files in the target directory
    sTargetFile = Dir(TOP_FOLDER & "\*.csv")
    Do While sTargetFile <> vbNullString
        If sTargetFile <> "." And sTargetFile <> ".." Then
            'import each file
            DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, _
                TOP_FOLDER & PATH_DELIM & sTargetFile, True
            'archive the imported files
            If bArchiveFiles Then
                'code for archiving imported files...
                sFileName = Left(sTargetFile, Len(sTargetFile) - 4)
                sOutFile = ARCHIVE_FOLDER & PATH_DELIM & sFileName & " " _
                  & Format(Date, "yyyymmdd") & ".csv"
                FileCopy TOP_FOLDER & PATH_DELIM & sTargetFile, sOutFile
                Kill TOP_FOLDER & PATH_DELIM & sTargetFile
            End If
        End If
        sTargetFile = Dir 'Loop through the next file that was found
    Loop
    
End Function

Denis