How Best to Structure Imports and Queries (1 Viewer)

robsmith

New member
Local time
Today, 13:47
Joined
Feb 17, 2020
Messages
26
Hi,

I have a database of 5 tables that I have imported from 5 csv files. I have then run different queries to produce new tables of combined data, converted date formats and added a calculated field to one table.

I have then linked the new tables to Excel for analysis.

The csv files are updated periodically with new data being added (but the field headings remain the same).

This is where I am getting stuck...

When the csv files are updated what's the best way to import into Access then run the queries? Is it possible to do it automatically in some way or do I have to manually import each file then manually run each query?

If anyone could point me in the right direction I'd appreciate it.

Thanks
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:47
Joined
Aug 11, 2003
Messages
11,696
docmd.transfertext is your friend both for import and export.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 28, 2001
Messages
26,999
Depends on what you are doing, but if you want to preserve what you had imported already, the best method is possibly to always import to a temporary table. Namliam's suggestiong of DoCmd.TransferText is probably as good as any other method for that step. Run your validation and conditioning queries on the temp. Then run a filtration query against the main table so that you can see what (if anything) changed for those records appearing in both the main table and the temp table. Once you get that handled, import the rest by running an INSERT INTO...SELECT FROM type of query.
 

bastanu

AWF VIP
Local time
Today, 06:47
Joined
Apr 13, 2010
Messages
1,401
If the data editing happens only in the original csv files (or whatever app produces them) you could try to link them instead of importing. Then try to do your data manipulations in queries instead of other Access tables and use the queries in Excel for analysis. This way you would have a "live' system that gets updated soonest the source csv's do.

Cheers,
Vlad
 

Cronk

Registered User.
Local time
Tomorrow, 00:47
Joined
Jul 4, 2013
Messages
2,770
Robsmith asked in #1
Is it possible to do it automatically in some way

A series of queries and other things could all be run from with a procedure which could be tied to a button click. Or, the procedure could be run whenever the database was opened. Or, the procedure could be run at a particular time if a schedule was set in the database (this requires the database to be open). Or, a windows schedule could be set to open the database and run the procedure (this requires the PC to be running).
 

ebs17

Well-known member
Local time
Today, 14:47
Joined
Feb 7, 2020
Messages
1,880
What happens to the data in access apart from the indicated processing?

With an analysis only in Excel, the data from the CSV could be loaded and processed directly using the Power Query.
Power Query has powerful database skills that you can develop step by step under the Excel interface.

Eberhard
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:47
Joined
Feb 19, 2013
Messages
16,553
When the csv files are updated what's the best way to import into Access
you can use a query rather than copying the data to a temporary table using transfertext. Something like this for example, adds names in the text file which are not in the destination table.

Code:
INSERT INTO tstNames ( FirstName, LastName, County)
SELECT txt.FirstName, txt.LastName, txt.County
FROM (SELECT * FROM [TEXT;DATABASE=C:\PathToFile;HDR=Yes].FileName.csv) txt LEFT JOIN tstNames ON txt.FirstName = tstNames.FirstName and txt.LastName = tstNames.LastName and txt.County = tstNames.County
WHERE tstNames.County Is Null
 

robsmith

New member
Local time
Today, 13:47
Joined
Feb 17, 2020
Messages
26
Thanks for all the suggestions guys. I really appreciate it
 

Users who are viewing this thread

Top Bottom