Results 1 to 9 of 9
  1. #1
    Grafixx01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    13

    Importing CSV, can it be done?

    So I'm just starting to get back into MS Access DB designs with my job. I've been tasked with creating a db from a CSV that is obtained monthly. This CSV never changes as far as format with the same column headers each time it is provided. The question I have is this, possibly two questions:

    1. Can you program Access to allow a user to import a CSV and just append to the current contents of the database?

    2. Can Access be programmed to append different tables from a single CSV?
    -- For instance, if the CSV has data like PII (name, address, phone, etc), business (name, address, phone, etc), pharmacy (name, address, etc), drug (name, generic name, strength, number count, etc)... All the data is in a SINGLE CSV but I would want it to append to the different, respective tables when it's imported.


    Needless to say, I work for a healthcare company.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    1. Sure, via various methods. Easiest may be TransferText. You can use FileDialog if necessary to let the user point to the desired file.

    2. Again, sure. You'd probably import the whole thing as is to a local table and then append out from there. Or you can probably link to the csv file instead of importing it and do the same thing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes to both questions.
    The best solution is to:
    1. LINK to the CSV file
    2. IMPORT the parts of the CSV data that you want to an intermediary table - sometimes called a buffer or staging table. This allows you to add a primary key field, modify datatypes if required and to discard any unwanted columns
    3. Now use a combination of append and/or update queries (or the equivalent SQL statements in VBA code) to add that data to one or more local Access tables.
    Once completed you can empty the intermediary table(s)

    Finally as you are going to do this repeatedly each month, it will cause a lot of database bloat.
    You may want to consider doing steps 1 & 2 in a separate 'side-end' database to overcome this issue

    For info, the above process is done with 30 CSVs every night in one of my apps for schools. The whole process is automated and performed in the middle of the night using a scheduled task
    Last edited by isladogs; 03-10-2021 at 01:45 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Grafixx01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    13
    Thanks, I clearly need to start doing Access from scratch as I've lost just about all knowledge not having to develop a DB since like 2008!

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Grafixx01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    13
    Quote Originally Posted by isladogs View Post
    Yes to both questions.
    The best solution is to:
    1. LINK to the CSV file
    2. IMPORT the parts of the CSV data that you want to an intermediary table - sometimes called a buffer or staging table. This allows you to add a primary key field, modify datatypes if required and to discard any unwanted columns
    3. Now use a combination of append and/or update queries (or the equivalent SQL statements in VBA code) to add that data to one or more local Access tables.
    Once completed you can empty the intermediary table(s)

    Finally as you are going to do this repeatedly each month, it will cause a lot of database bloat.
    You may want to consider doing steps 1 & 2 in a separate 'side-end' database to overcome this issue

    For info, the above process is done with 30 CSVs every night in one of my apps for schools. The whole process is automated and performed in the middle of the night using a scheduled task
    Thanks for the help. Is there any way that you may be able to provide an example, like you said you have a DB already that does it, so I can see what is going on? I'm trying to see if it is possible with the Excel CSV/Spreadsheet that I currently have and need to ingest then populate the individual tables while sill maintaining the integrity of the data.

    Also, not sure if it can be set up to 'not allow duplicates based on specific data such as "Firstname (same) + Lastname (same) + DoB (same/not same) = do not/ do add record"?

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi.
    The code is deeply embedded as part of a commercial app and it would take me several hours to separate out the relevant parts related to your question.
    However its really quite simple. For each CSV file
    1. Link the CSV file to your FE or better still to a separate 'side end' database
    2. Create a local table (ImportedCSV) with the fields you want to you from the CSV file and ignore any you don't need. This is your buffer table AKA staging table
    3. Create a query or code to import the required fields to the staging table.
    4. Now create another query or code to do any necessary processing of the data then run an append query to add the new records to the final table
    If the CSV file also contains existing records then you need to either modify the code to ignore existing records if these won't have changed or run an update query to grab the new values
    5. Finally empty the ImportedCSV table(s) using a delete query/queries.

    Now put each of those steps into a procedure so it can be run whenever required
    You can then repeat the process each time that new data needs to be imported
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Grafixx01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    13
    Cool. So I've gotten the 'staging table' in the db now. I do have a question, how or what would be the method to easily maintain integrity with the data? For instance, if the CSV has like 30 columns and those columns are spread out over numerous tables due to normalizing the data and DB, i don't understand / know how I can cycle through the CSV data and maintain that the appropriate data is still associated with the correct line in the CSV.

    Another side question is, if this DB is going to be used repeatedly to import CSVs on a monthly basis, some of the data may be the same (ie: patientFN, patientLN, DOB, etc), I'm guessing that I'll have to figure out how to make like a query go through as ask first if the person exists within the DB before adding them to the DB?

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. If your CSV table contains a field which will act as a primary key in one table e.g. PatientID then add that field as a foreign key (FK) in the other tables.
    You can also impose referential integrity between your various Access tables to ensure there are no orphan records - see Relationships & Referential Integrity

    2. Primary key fields will also prevent duplication of records. In other words the same PatientID cannot be imported twice.
    Or if you are using an autonumber primary key field, you would use an append unmatched query.
    To do so, use the wizard to create a select query of unmatched records then convert it to an append query

    If some of your existing records may have been updated in the CSV files, use update queries to get the latest data
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Importing
    By RustyRick in forum Import/Export Data
    Replies: 2
    Last Post: 04-04-2020, 03:21 PM
  2. Importing CSV
    By mike02 in forum Access
    Replies: 5
    Last Post: 08-01-2012, 05:21 PM
  3. Importing a PDF
    By JHighley in forum Access
    Replies: 6
    Last Post: 07-24-2012, 08:39 AM
  4. Need help importing to SQL
    By winterh in forum SQL Server
    Replies: 14
    Last Post: 03-28-2012, 12:36 PM
  5. Help with Importing
    By Souperbc in forum Programming
    Replies: 2
    Last Post: 04-13-2011, 12:45 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