I have a question about writing VBA code in Access to handle uploading .csv files to a database. I have put together some of the code but I still need to fill in some areas. Please bear with my attempts to construct this program so far.
I would like to read the filename of file(s) in a folder (there should only be one as after a file is uploaded it will be deleted - but there may be more) and parsing the file name(s) to determine the date(s) included in the filename. Since I don't know on which days the data will be uploaded to the folder , I am thinking of checking everyday to see if a file exists in the folder and if it does processing it, otherwise, aborting the procedure.
The file (which would be uploaded to the folder approximately weekly) will be named in the following format:
So the last 8 digits will be the date_name. Once the date_name has been parsed I want to then convert it to a date format which would be calculated as follows:
'Modify date_name from 20100825 format to 08/25/2010 format
Date = Mid([date_name], 5, 2) & "/" & Right([date_name], 2) & "/" & Left([date_name], 4)
Then I want to compare the date to an Access file on Fiscal Periods (probably use a SQL query which I haven't constructed yet ) which is in the following form:
Month Fiscal_Period Fiscal_Year End_date
July 3 2011 07/28/10
August 4 2011 08/25/10
Sept 5 2011 09/22/10
Since, in this case, the date is between 07/28/10 and 08/25/10 the file would be for Fiscal Period 4 and the End_date for the Fiscal Period would be 08/25/2010. These numbers then would be compared to a file (called CUrrent_FP) with the date_name, date and Fiscal Period of the last uploaded file which would have the following format:
Date_Name Date Fiscal_Period
20100818 08/18/2010 4
If the date is prior or equal to the date in Current_FP then delete the file. If it is greater than the date in Current_FP but has the same Fiscal_Period then append the file to the main datafile (DataDtl_Main) in the database with the following code:
Private Sub TableData_Import()
Dim SourceFile As String
Dim ThsDay As String
On Error GoTo error_handler:
' Code to enable selection of current data file only
'input current file Date_number
CurFile = Date_Name ' (of file to be appended) .....not sure how to do this....
SourceFile = "\filefolder\DataDtl" & CurFile & ".csv"
' Import method using Import File Specification to ensure correct data types for included fields
DoCmd.TransferText acImportDelim, "MainDtlImport Specification", "DataDtlMain", SourceFile, True
' remove sourcefile once it is uploaded to database
On Error Resume Next
If the number for the fiscal period is greater than the existing entry in Current_FP (then the new file is for the next fiscal period) then I want to run a make table query to aggregate the data in DataMainDtl from weekly data to fiscal period prior to the import procedure.
If the file currently exists, I want to delete it first and then create the table again with the new file.
Dim fiscper as Fiscal_Period ' from new data file
Dim Src as String
Src = "CREATE TABLE DataDtl_" & fiscper & " AS "
Src = Src & "SELECT DataDtlMain.[Restaurant Number] AS Restaurant_Number, DataDtlMain.[Fiscal Year] AS Fiscal_Year,
DataDtlMain.[Fiscal Period] AS Fiscal_Period, DataDtlMain.[Item Number] AS Item_Number, DataDtlMain.[Item Description] AS
Item_Description, Sum([DataDtlMain]![Qty Sold]) AS Qty_Sold, CCur(Sum([DataDtlMain]![Gross Sales])) AS Gross_Sales "
Src = Src & "FROM DataDtlMain LEFT JOIN Restaurant_AllData ON DataDtlMain.[Restaurant Number] =
Src = Src & "GROUP BY DataDtlMain.[Restaurant Number], DataDtlMain.[Fiscal Year], DataDtlMain.[Fiscal Period],
DataDtlMain.[Item Number], DataDtlMain.[Item Description] "
Src = Src & ORDER BY DataDtlMain.[Item Number] "
Once the make table query is run then I will clear the contents of the main data file, DataDtlMain, and append the current data to the main data file using the aforementioned code.
CurrentDb.Execute "delete" * from DataDtlMain
and then run TableDataImport (the import code above)
I then want to clear the data in Current_FP (from the last uploaded file) and then save the Fiscal Period number, the date and the date_name from the new imported file (in this case 4, 08/25/2010, 20100825) to the file called Current_FP so that it can be used to evaluate the next uploaded file and the process repeats itself.
I want to automate this procedure using the Windows scheduler which I imagine will require a vbscript. However, I will post another question on this as I don't want to make this request to burdensome.
I hope that I have been clear on my intentions -- although I am open to other techniques to accomplish the same process if there is a more efficient way.
Thanks for your help in advance.