Automate Daily Import

[tmyers]

I know of the tie in to outlook to run as saved import, but I am looking for something a bit more autonomous. Is there a way to have Access at say, 6:00PM run an import query every day to load in the newest file output by my companies report writer? The table structure is static, I just need the current days data to be appended to the table.

 

[theDBguy]

Hi. If it was me, I might approach it from the other end - make Outlook "export" the data to Access.

Just a thought...

 

[tmyers]

I did not know Outlook had that function.
Currently my report writer exports its file every day at 6PM and that in turn feeds a Power BI report. The files have become much too unwieldy to keep as Excel files, which is why I am trying to just stuff it all in Access (Just last month the Excel file had 450k rows of data 15 columns wide to deal with).

Would you happen to have any reading material on that function for Outlook? I didnt see any after a quick Google search.

 

[theDBguy]

I did not know Outlook had that function.
Currently my report writer exports its file every day at 6PM and that in turn feeds a Power BI report. The files have become much too unwieldy to keep as Excel files, which is why I am trying to just stuff it all in Access (Just last month the Excel file had 450k rows of data 15 columns wide to deal with).

Would you happen to have any reading material on that function for Outlook? I didnt see any after a quick Google search.

Since I don't have one handy, I had to use Google as well. Take a look at this one.

Developer | TechRepublic

www.techrepublic.com

 

[pbaldy]

I import data from several different sources via email. I'm sure theDBguy is correct about doing it from Outlook, but being more comfortable with Access I did it there. The site I originally got code from is down, but example 12 here shows how to loop the inbox:

Automate Microsoft Outlook from Excel, using VBA

Automate Microsoft Outlook from Excel, using VBA

www.excelanytime.com

I typically compare the subject and sender to identify the appropriate emails (not everything in the inbox gets imported). You can save the attachment if that's where the data is and import from there, or open the body of the email and parse data out of it. I then move the email into a subfolder so I don't import it again. The Access file is fired off from Scheduled Tasks.

 

[tmyers]

That is essentially what I am after pbaldy. I can setup our report writer to email the file rather than export it to a designated spot, so all I would need is to set rules for my email to send that email to a specific folder for import then I can either setup a timed rule to move it out/delete it or do it manually.

I am reading that link now.

 

[pbaldy]

If you can have the file exported to a designated spot, why not just grab it from there and import it?

 

[tmyers]

I can do that as well as long as not have a static name isn't a problem. The file gets exported with the queries name and long format time stamp all in the name (such as ReportWriter_Query1_020722180000) so the file name will always be different.

 

[pbaldy]

If it's predictable you can use:

strFileName = "ReportWriter_Query1_" & Format(Date(), "mmddyy") & "180000.xyz"

If the seconds may or may not be in there, you could compare the leftmost x characters.

 

[tmyers]

Would this be able to work via a timer or some other event? I have everything happen around 6PM after everyone has left (including me) and want all my data to update for my reports.

Edit:
This whole process would be so much simpler if work would just let me attach Power BI directly to our database lol.

 

[bastanu]

You might want to have a look at my free utilities for some examples of Outlook automation; you can create a small Access app that would read a certain Outlook folder and process each message in that folder (then move it to a "Processed" subfolder) and run that app at certain times\intervals using the built-in Windows scheduler.

FBA Outlook email parser and importer - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

FBA Outlook Companion - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,

 

[pbaldy]

Would this be able to work via a timer or some other event? I have everything happen around 6PM after everyone has left (including me) and want all my data to update for my reports.

Sure, just create an Access app that grabs and processes the file and fire it off at 6:15 (or whenever you're sure the import will be complete). You can have it email you with success or failure.

 

[Gasman]

I can do that as well as long as not have a static name isn't a problem. The file gets exported with the queries name and long format time stamp all in the name (such as ReportWriter_Query1_020722180000) so the file name will always be different.

So export it, so that is the ONLY file in that folder?, delete after a successful import?

 

[pbaldy]

Oh, and you'd fire it off with Scheduled Tasks. Just leave your computer logged in but locked.

 

[pbaldy]

So export it, so that is the ONLY file in that folder?, delete after a successful import?

That would certainly be easier, you could also move it instead of deleting, if they need to be kept.

 

[tmyers]

Once the file is imported there would be no reason keeping it, so I could automate the deletion of it as well once the process finishes.
I just finished up importing the last two years of data into Access and the table is now up to 1.9 million records

 

[Gasman]

Once the file is imported there would be no reason keeping it, so I could automate the deletion of it as well once the process finishes.
I just finished up importing the last two years of data into Access and the table is now up to 1.9 million records

I'd be for moving it now, not deleting it as @pbaldy has mentioned. Then if anything goes untoward or is discovered later you have the file to work with. If you can regenerate?, then no need, I would have thought.

 

[tmyers]

I think I have a pretty good idea on what to do now but have one more question for you pbaldy. After looking over previous file names, the seconds do change as I have some files that are 180001. How would I adjust for that based on what you said higher up?

 

[pbaldy]

If it will be the only file, you can just grab it:

strFileName = Dir("PathToFileLocation")

If there will be multiple, you can loop with the Dir() function and test the left x characters of each name against the part you're sure of.

 

[Pat Hartman]

As long as there will be only one file in the input folder or all files in the folder need to be imported, then you don't care what the file name is. You can just use FSO (File System Object) to browse the directory and import each .xls or .csv or whatever file it finds. To clean up, you can either rename them to Imported-oldname or copy them to an archive folder and then delete them from the input folder. If you want code, I'll give you what I use and you can modify it. I like FSO rather than the internal Access methods but to use FSO, you need to set a reference to the Microsoft Scripting Runtime library.

To schedule the task, create a database that links to the appropriate tables and has an autorun macro that runs the code to do the processing. Use the windows task scheduler to run it. I have found that I have better luck if I create a batch file to run the access app/macro and then schedule the batch file. The last thing the macro does is logs the import when it is complete. You can create a way to check on this so you get some warning that the file did not get imported. Sometimes the PC reboots or the network connection is lost or the file is not there when the job runs. Whatever, you need a head-up either that it didn't run or that it did, whichever makes more sense to you.

Or, you can keep log tables (I tend to do this anyway because it makes backing out easier) and instead of relying on a scheduling job, the first person who opens the app each day automatically kicks off the import. This is probably easier if the file is not large and the import takes only a few seconds. If it takes longer, then the scheduled task is probably better.