Daily File Form

[DAyotte]

I'm looking to create a form with a series of macros to simplify the task of importing a daily file, and narrowing down the 90k records to the 500 that we are required to look at. The query part, I can do... but it's creating the macros to run the importing on a consistent basis that has me baffled. It's not the same file that is getting replaced on a daily basis, but a new file that is getting imported into a specific folder daily. The name changes with the date. Is this even possible?

I came across a thread that looked to be similar, but it didn't appear to be exactly what I was looking for:
http://www.mrexcel.com/forum/showthread.php?t=522468&highlight=import+daily+file

Thanks in advance.

 

[xenou]

It's possible ...

If you need to run a series of queries on the data you can probably just script it out with

DoCmd.OpenQuery("Query1")
DoCmd.OpenQuery("Query2")
DoCmd.OpenQuery("Query3")
... etc. etc. etc.

This can also be done with Access Macros - doesn't even require VBA.

Importing a text file can also be scripted but I guess I'd want to know:
1) what is the naming convention
2) is the file the only file in the folder
3) what happens to it after you're done (is it deleted?, remains in the file?)
4) what is the current process for dealing with this (you can probably just build on that ... imitation in code of what you are doing manually at the present time).

Also, my habit is to build it all into a form so that a "user" can just open the form and click a button - a form is also a good place where the user can select options, choose dates, and so on... That said, I also by habit try to write my forms so I can (if I want to) run the same form code "automatically" ... in case some day I might want to schedule the task in the middle of the night.

 

[SydneyGeek]

You can code for a file picker so the user selects the file to be processed. The FileDialog object is usually what I use. Something like...

Dim strFile as String
Dim varSelection
Dim fdFile As FileDialog
 
    'Create a file picker to allow user to select the daily file.
    Set fdFile = Application.FileDialog(msoFileDialogFilePicker)
    With fdFile
        .AllowMultiSelect = False
        If .Show Then
            For Each varSelection In .SelectedItems
                Workbooks.Open varSelection
                strFile = CStr(varSelection)
            Next varSelection
        Else
            MsgBox "You must select a file to process1", vbCritical
            Exit Sub
        End If
    End With
    Set fdFile = Nothing
 
   '... continue processing here

Denis

 

[DAyotte]

Hrm, Denis - the file picker doesn't seem like a bad way to go. I do want to try to make the task as simple as possible for the end user so I would like to see where else I can go with this, but that is a good 'plan B'.

Xenou - this is what I got for you:

1 - as far as the naming convention, each file is saved under the following format: "CXS196AYYYYMMDD.txt". of course the yyyymmdd is the date of the file. Also, another point that may be imperative is that it's the previous day's file that comes in every day. Like today's file was CXS196A20110802.txt

2 - All files are being saved in the same folder on the company's shared drive.

3 - After the department imports the information for their current use, they leave it in the original file for back up.

4 - Currently, the department is importing the file into a separate database that was created some time ago. There are a series of queries that run here to narrow down the large amount of records to ones that would be most appropriate for us to review. This whole project came up because there were certain records that were being over looked as a part of this series of queries that the team felt were pertinent for review. They wanted a way of bringing up records that fell under a certain category into it's own table, so that these may also be reviewed. I decided just to start from scratch, and work on a way of pulling the file over to a separate database so that I'm not disturbing their daily workflow.

I have very little knowledge of the scripting aspect of this, which is why I'm here. I'm just good at making it visually appealing and simplifying the workflow for the end user.

 

[xenou]

This worries me:

s far as the naming convention, each file is saved under the following format: "CXS196AYYYYMMDD.txt". of course the yyyymmdd is the date of the file. Also, another point that may be imperative is that it's the previous day's file that comes in every day. Like today's file was CXS196A20110802.txt

What about Monday? Is the file present from Sunday or is the most recent file really from Friday? Or what about the day after a holiday?

Things like this means we can't *just* look for yesterday's file. Perhaps we could look for the most recent file. A file picker is the most foolproof (as long as you don't need this to run at night automatically).

What do you think?

(Note that just scripting an import isn't hard but making sure you're importing the right file is the main thing).

 

[DAyotte]

Ah, that's a good point. The files are automatically put into the folder everyday, albeit on a Monday or after a holiday, we have to play catch up and upload 2 or more files.

 

[xenou]

Okay.
Well, is it only scripting file imports you are looking for help with? It sort of depends on what you want to do with the file ... but typically you can import a file to any table by appending the records. I would normally append them to a staging table and deal with the new records from there.

Note: I would in any event start with a file picker (a la SydneyGeek's post), and you can tweak/jazz/enhance from there ...

ξ

 

[DAyotte]

Sorry about the late response, but basically... I would like to start with the scripting for the file import, yes, but this may lead to other questions in the future development of it.

 

[xenou]

Generally for a file import I use either text files (csv or tab delimited) -- xml is sometimes an option. And Excel files are of course common sources of data. So if it's a text file, you want to *first* do an import manually. This lets you set things like the column data types, date formats, and so on. When you do this, save it - this is called an import specification. Now, given the file path (which is what the file picker is for, as in SydneyGeek's post), you can simply use DoCmd.TransferText - the TransferText method allows you to specify an import specification by name, which you have just saved, and the rest is easy.

Excel imports are similar but no file import specs are used - so you have less control of the process.

My general practice is:
1) truncate the staging table (delete all records in it)
2) import the data to the staging table
3) Run any validations I need to do
4) Append to the final destination table

In this way, you have a lot of flexibility to do whatever you need to do before you get to the last step to make sure everything is right.

 

[SydneyGeek]

xenou's approach is very similar to mine.
The main difference is that I don't always clear out the staging table. Particularly where a history may be required, I append to the staging table and use an Unmatched query to determine which records to push to the main table.

To get the chronology it's very useful to have a timestamp field in the staging table, with a default value of =Now()

Denis