07-28-04, 22:48 #1Registered User
- Join Date
- Jul 2004
Unanswered: Help, is this possible? Processing on identically formatted external files easily
Hi, I am a casual Access user who has been forced into spending much more time working with data than I currently have the time to do. I am looking to find out how I can process the data I receive faster so I can get back to doing the other parts of my job.
I receive 4 40M, 130,000 record .csv files every week that I have to process individually, but the same way. All 4 of these files are in the exact same format, and I currently import them into Access, run several queries, generate tables, and have to export two files to .dbf format based on this information for my boss.
I am able to do all of this manually, but I am trying to figure out how to automate this. I have an idea what I want, and am hoping that someone can guide me in the right direction.
What I want is the ability to create an Access database that acts as a mega-template to do all of the processes against any of the .csv files I get. Is there some way to build a database with all of the queries and tables finished, and be able to change and refresh the data "on the fly" from a different .csv files. Ideally, there would be a menu driven way to open a dialog box and point to the new file, then refresh so all of the tables and queries would be populated with the new data. I would also like to find a way to generate the two .dbf files at a touch of a button.
I thought the key to this would be linking files, but for some reason, these files import into Access without trouble, but seem to be too large to link.
The very best thing would be to build an interface were a novice user could press a few buttons and get the results they need. If that were to happen, I could get this completely off my desk.
What directions do I need to be looking at to start in building something like this? Am I correct that I can do all of the in Access? If I can figure this out, it will take hours off of my week. Plus the fact that I doing the exact same reports, on basically the same information, four times, every week is driving me nuts.
Thank in advance
07-28-04, 23:01 #2Registered User
Originally Posted by Patton
- Join Date
- Jun 2004
TransferDatabase Method, will show you how to export to dBASE.
All that you are looking for can be done with Access if you take the time to learn how.
Basically, you are going to need to create; table, query, form. Then code the onclick events to any buttons you want on your form, and/or any additional coding.
07-29-04, 04:10 #3Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
i get a daily .CSV that access churns into several dozen graphical .SNP reports and e-mails them to the concerned parties. it's a single-button-click jobbie and has been running for six years. i don't run it daily or i would have automated it completely... report frequency is a loose function of where we are in the fiscal quarter, so i still run it manually to suit my mood.
i do not use transferXxxxxx.
i link to the .csv using an import spec that treats everything as text (my .CSV file names are always the same - so linking is a one-time setup process).
the button-click zaps through the linked .CSV record by record, reformatting and shaping the data and then saving to an access table (my .CSV is an incremental report "this month so far" so first all this-month records are deleted from the table and then the new month-so-far records are appended). this "accumulating" table has relationships with other lookup tables so that queries can provide appropriate grouping etc.
izycurrently using SS 2008R2
07-29-04, 11:02 #4Registered User
- Join Date
- Feb 2004
Why not use the Dir() command to check if the date has change since you last did the update procedure to launch the updates. Also, why not use a weekly scheduled task prior to your boss getting to work (say 4AM) to do all the dirty work while you sleep or drive into work. Just make sure to create a good log file that tells you what processed automatically, and trap errors to the log also.