Results 1 to 4 of 4
  1. #1
    WallyJ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    2

    Best Practices for Regular Ongoing CSV Import

    I export 3 CSVs from a data system and need to import them once a week into Access, replacing the existing data in all 3 tables each time I import. What is the best practice for how I should do this.

    A few notes:



    I do not keep the old data in the CSVs. When I export I replace the CSVs that have the correct names.

    I was thinking about linking to the CSVs and simply reopening the DB. There was a reason I converted the links to tables, but I can't remember. It had something to do with the queries I was building. But if it will work, I can go with links.

    I may build a query in the future that appends the old records to a different table for posterity, but that's another matter.

    Best workflow for this type of process? Thanks!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can just link to a csv file and if you then swap that file for another of the same name and structure your linked table will see the new file. My best guess as to the reason for importing the table is performance. A csv file does not have indexes whilst a table can. This may not matter for relatively few records but will become painful for larger volumes. I have seen situations where a query was taking 10 or 11 hours to run when referencing files linked to excel. Importing them and applying indexes reduced it to a few minutes. Indexes also significantly affect the performance of sorting, filtering and finding.

    Another reason may be because you wanted to be able to edit the data - not possible through linked tables for excel and .csv files.

    So, best workflow - all depends on what you have and what you are going to do with it. small data volumes you can use linked tables, larger volumes, import.

  3. #3
    WallyJ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    2
    Thanks Ajax, Is there any easy/simple way to replace my existing tables with CSVs in a way that I don't have to recreate the queries I have, or do I need to recreate them? Same field names, just CSVs instead of tables?

    Btw, I have a total of about 8,000 records across the 3 tables. Should I work on a query to delete the data and then append the new records, or do you think CSVs will not be too slow with that number of records? Thanks.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    providing you link to the csv's, you can change the name of the linked table to your existing table and the queries will still work (subject to any differences in datatypes).

    Can't answer the question about speed - yes, it will be slower because of no indexing, but I don't know if your tables were indexed in the first place. And also depends on the complexity of the queries. It may be that although slower, it is not significantly slower.

    Suggest you take a copy of your db and try it.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-08-2016, 12:28 PM
  2. Replies: 8
    Last Post: 10-13-2014, 12:19 PM
  3. Replies: 2
    Last Post: 07-07-2014, 03:01 PM
  4. Regular expression pattern
    By jacjacjac in forum Programming
    Replies: 1
    Last Post: 12-23-2012, 08:52 PM
  5. Using Regular Expression $ in Access Queries
    By rkdiekhoff in forum Queries
    Replies: 1
    Last Post: 01-20-2010, 12:50 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