Search directory for single file and add file to zip

Question

I'm wondering if this is possible and the best way to accomplish it if it is.

Scenario: We have multiple sites that create a "dated subdirectory" each day at a certain time. The dated subs contain information for that day of business.

I need to pull a single DBF file out of the dated sub each day and either export the data to an ever-expanding Excel file, that contains information from the single DBF file from EACH day so it looks like:

Day 1's information . . . Day 2's information . . . Day 3's information

OR

Add a copy of the DBF file from each dated sub to a ZIP file that is done daily.

The name of the DBF file never changes, and can't be deleted.

I'm thinking it could be done with a forfiles command, but am curious if it could be done more efficiently. The file that searches, pulls, and zips would be run as a task nightly.

As an add-on, could it be pushed to a Google Drive for safe storage?

Sorry if this is rambling. This is something I'd love to try to do, but not sure where to start exactly.

-Dated sub created nightly, single file from that directory needs to be pulled or read and transferred either to an Excel file or copied to a ZIP with a way to seperate each file, maybe a directory with date as name?

Also, if able, it needs to start with a particular date, like 6/1/2014 but no further back.

Thanks in advance for any help.

Answer 1

Can you merge data from a DBF file to an Excel file? Not really with pure batch, but you can use JScript or VB Script. You'll need the MS ACE OLEDB 12.0 driver. Then you can use a connection string for DBF and another for XLSX. (If you're using XLS or CSV, you could get by with the MS Jet driver, running the WOW64 version of cscript.) Once connected, just use SQL queries. SELECT * FROM dbffile, and as you're looping through the recordset, INSERT INTO xlsxfile.

Can you append a DBF file to a zip file? Probably. I'm guessing 7za.exe a will append to the archive if the archive already exists. Try it and see. Or were you wanting to script the zip functionality without 3rd party software?

Can it be copied to a Google Drive? Well, yeah, the Google Drive software watches and mirrors a folder on your hard drive. So chances are, copying the file to %userprofile%\Google Drive\ will do what you want without any conscious effort.

Try posting another question. But rather than rambling again, find one specific problem where you're getting stuck, and explain what you've tried without success.