Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Uploading csv file to Access using VBA

Posted on 2010-08-30
8
Medium Priority
?
744 Views
Last Modified: 2013-11-27
Hi,

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:

\filefolder\DataDtl20100825.csv

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)

Open in new window


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"

DoCmd.SetWarnings False

' 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
Kill SourceFile

DoCmd.SetWarnings True
Exit Sub

error_handler:

On Error Resume Next
DoCmd.SetWarnings True

End Sub

Open in new window


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] = 

Restaurant_AllData.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] "

DoCmd.RunSQL Src

Open in new window


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

Open in new window


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.

Regards,

scurvylion
0
Comment
Question by:scurvylion
  • 3
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33569619
FWIW,

Your questions seem more like a "Project" than one direct question requiring a single straightforward answer.

Perhaps you can break this up into several smaller questions spanning multiple questions?

Please click the "Request Attention" link for help with this.

;-)

JeffCoachman
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 total points
ID: 33573781
Here is a custom import spec I helped build for someone. This is some good example code for some of what you are doing.

http://www.experts-exchange.com/Q_26163641.html#a32666928
0
 

Author Comment

by:scurvylion
ID: 33588801
Hi guys,

I apologize for getting a bit carried away on this question. I want to thank all of you for yor assistance so far.

I have gone ahead and written all of the code for this questions and it works perfectly; however, I want to award the points here to Jimpen since you have attempted to provide me with some of the answers to my questions.

Here is the related code that I put together:

Thanks again for your help.
Option Compare Database
Option Explicit

Public Sub ImportFileMain()
Dim DtlDataInputFile As String, strSQL As String, DtlDataInputDataSource As String
Dim date_name As String, fiscper As Long, fiscyear As Long, lstimportfiscyear As Long, lstimportfiscper As Long
Dim Filedate As Date, lstimportdate As Date
Dim dbsDtlReport As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo leave:

    DtlDataInputDataSource = "D:\AAA Ltd\Projects\Company\VBA Tool Project\Dtl Data\"
    DtlDataInputFile = Dir("D:\AAA Ltd\Projects\Company\VBA Tool Project\Dtl Data\*.csv")
    
    Do While DtlDataInputFile <> ""
    
        'determine date_name from import file name
        date_name = Left(Right([DtlDataInputFile], 12), 8)
        
        'Modify date_name from 20100825 format to 08/25/2010 format - also determine fiscal year
        Filedate = CDate(Mid([date_name], 5, 2) & "/" & Right([date_name], 2) & "/" & Left([date_name], 4))
        
        'determine fiscal year and fiscal period by joining with Fiscal_Periods table
        fiscper = FiscalPeriod(Filedate)
        fiscyear = FiscalYear(Filedate)
        
        'Retreive the file date of the last import data file
        lstimportdate = LastImportDate()
            
        'Retreive the name of the respective Fiscal Period for the last import data file
        lstimportfiscper = LastImportFiscPeriod()
            
        'Retreive the name of the respective Fiscal Year for the last import data file
        lstimportfiscyear = LastImportFiscYear()
            
          
        ' if date of import file is earlier than last imported file then delete
        If Filedate <= lstimportdate Then
            Kill (DtlDataInputDataSource & DtlDataInputFile)
            
        ' if date of import file is the same or later than last import file and fiscal period is the same then append data to main data table
        ElseIf Filedate > lstimportdate And fiscper = lstimportfiscper And fiscyear = lstimportfiscyear Then
            Call TableData_Import(date_name, DtlDataInputDataSource, DtlDataInputFile)
            
            ' replace last import file specifications
            CurrentDb.Execute "delete * from Current_FiscalPeriod_FiscalYear"
            Call CurFiscPeriodFiscYear_Update(date_name, Filedate, fiscper, fiscyear)
            
        ' if data of import file is the same of later than last import file and fiscal period is greater then run make tabel query
        ' and then delete main data table then append new file
        ElseIf Filedate > lstimportdate And fiscper > lstimportfiscper And fiscyear = lstimportfiscyear Then
        
            ' replace Bar_AllData table with current Bar information
            Call BarAllDataMakeTableQuery
            
            ' aggregate weekly data for fiscal period and create new table for current fiscal period and fiscal year
            Call FiscalPeriodMakeTableQuery(fiscper, fiscyear)
            
            ' empty main data table
            MainDataTableEmpty
            
            'append new fiscal period data to main data table
            Call TableData_Import(date_name, DtlDataInputDataSource, DtlDataInputFile)
            
            ' replace last import file specifications
            CurrentDb.Execute "delete * from Current_FiscalPeriod_FiscalYear"
            Call CurFiscPeriodFiscYear_Update(date_name, Filedate, fiscper, fiscyear)

        End If
        ' next file
        DtlDataInputFile = Dir
        
    Loop
    
    Close
Exit Sub

leave:
    
End Sub

Public Function FiscalPeriod(Filedate As Date) As Long
Dim dbsDtlReport As DAO.Database
Dim rst As DAO.Recordset
Dim Src As String

' On Error GoTo leave:

Set dbsDtlReport = CurrentDb

'Select statement to determine which fiscal period contains the filedate parameter

Src = "SELECT Fiscal_Periods.Fiscal_Period, Fiscal_Periods.End_Date FROM Fiscal_Periods WHERE Fiscal_Periods.End_Date >= #" & Filedate & "# "

Set rst = dbsDtlReport.OpenRecordset(Src)

'Begin row processing - do not process if end-of-file reached
Do While Not rst.EOF

'Retreive the name of the respective Fiscal Period for the current import data file

FiscalPeriod = rst!Fiscal_Period

' exit loop once correct Fiscal Period is determined
Exit Do

Loop

If rst.EOF Then
MsgBox "Fiscal_Periods table is out-of-date. Please update Fiscal_Periods table in Access database in order to calculate parameters correctly", vbCritical, 

"Data Warning!"
End If

Exit Function

leave:

End Function
Public Function FiscalYear(Filedate As Date) As Long
Dim dbsDtlReport As DAO.Database
Dim rst As DAO.Recordset
Dim ddate As Date
Dim Src As String

 On Error GoTo leave:

Set dbsDtlReport = CurrentDb

'Select statement to determine which fiscal period contains the filedate parameter
Src = "SELECT Fiscal_Periods.Fiscal_Year, Fiscal_Periods.End_Date FROM Fiscal_Periods WHERE Fiscal_Periods.End_Date >= #" & Filedate & "# "

Set rst = dbsDtlReport.OpenRecordset(Src)

'Begin row processing - do not process if end-of-file reached
Do While Not rst.EOF

'Retreive the name of the respective Fiscal Period for the current import data file
FiscalYear = rst!Fiscal_Year

' exit loop once correct Fiscal Period is determined
Exit Do

Loop

If rst.EOF Then
MsgBox "Fiscal_Periods table is out-of-date. Please update Fiscal_Periods table in Access database in order to calculate parameters correctly", vbCritical, 

"Data Warning!"
End If

Exit Function

leave:

End Function

Public Function LastImportDate() As Date
Dim dbsDtlReport As DAO.Database
Dim rst As DAO.Recordset
Dim currentdate As Date
Dim strSQL As String

' On Error GoTo leave:

Set dbsDtlReport = CurrentDb

        ' get descriptive information from last imported file
        strSQL = "SELECT Current_FiscalPeriod_FiscalYear.DDate, Current_FiscalPeriod_FiscalYear.Fiscal_Period, Current_FiscalPeriod_FiscalYear.Fiscal_Year 

FROM Current_FiscalPeriod_FiscalYear;"
        
        Set rst = dbsDtlReport.OpenRecordset(strSQL, dbOpenSnapshot)
        
            'Begin row processing - do not process if end-of-file reached
            Do While Not rst.EOF
            
            'Retreive the date for the last import data file
            LastImportDate = rst!ddate
            
            ' exit loop once last import file date is determined
            Exit Do
            
            Loop
            
            If rst.EOF Then
            MsgBox "Current_FiscalPeriod_FiscalYear table is out-of-date. Please update Current_FiscalPeriod_FiscalYear table in Access database in order to 

calculate parameters correctly", vbCritical, "Data Warning!"
            Exit Function
            End If

Exit Function

leave:

End Function
Public Function LastImportFiscPeriod() As Date
Dim dbsDtlReport As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String, currentfiscper As Long
' On Error GoTo leave:

Set dbsDtlReport = CurrentDb

        ' get descriptive information from last imported file
        strSQL = "SELECT Current_FiscalPeriod_FiscalYear.DDate, Current_FiscalPeriod_FiscalYear.Fiscal_Period, Current_FiscalPeriod_FiscalYear.Fiscal_Year 

FROM Current_FiscalPeriod_FiscalYear;"
        
        Set rst = dbsDtlReport.OpenRecordset(strSQL, dbOpenSnapshot)
        
            'Begin row processing - do not process if end-of-file reached
            Do While Not rst.EOF
            
            'Retreive the name of the respective Fiscal Period for the last import data file
            LastImportFiscPeriod = rst!Fiscal_Period
            
            ' exit loop once correct Fiscal Period is determined
            Exit Do
            
            Loop
            
            If rst.EOF Then
            MsgBox "Current_FiscalPeriod_FiscalYear table is out-of-date. Please update Current_FiscalPeriod_FiscalYear table in Access database in order to 

calculate parameters correctly", vbCritical, "Data Warning!"
            Exit Function
            End If

Exit Function

leave:

End Function
Public Function LastImportFiscYear() As Date
Dim dbsDtlReport As DAO.Database
Dim rst As DAO.Recordset
Dim currentdate As Date
Dim strSQL As String, currentfiscper As Long, currentfiscyear As Long

' On Error GoTo leave:

Set dbsDtlReport = CurrentDb

        ' get descriptive information from last imported file
        strSQL = "SELECT Current_FiscalPeriod_FiscalYear.DDate, Current_FiscalPeriod_FiscalYear.Fiscal_Period, Current_FiscalPeriod_FiscalYear.Fiscal_Year 

FROM Current_FiscalPeriod_FiscalYear;"
        
        Set rst = dbsDtlReport.OpenRecordset(strSQL, dbOpenSnapshot)
        
            'Begin row processing - do not process if end-of-file reached
            Do While Not rst.EOF
            
            'Retreive the name of the respective Fiscal Period for the current import data file
            LastImportFiscYear = rst!Fiscal_Year
            
            ' exit loop once correct Fiscal Period is determined
            Exit Do
            
            Loop
            
            If rst.EOF Then
            MsgBox "Current_FiscalPeriod_FiscalYear table is out-of-date. Please update Current_FiscalPeriod_FiscalYear table in Access database in order to 

calculate parameters correctly", vbCritical, "Data Warning!"
            Exit Function
            End If

Exit Function

leave:

End Function
Private Sub TableData_Import(date_name As String, DtlDataInputDataSource As String, DtlDataInputFile As String)

Dim SourceFile As String
Dim dbsDtlReport As Database

Set dbsDtlReport = CurrentDb

On Error GoTo error_handler:

' Code to enable selection of current data file only
 SourceFile = DtlDataInputDataSource & DtlDataInputFile

DoCmd.SetWarnings False

' Import method using Import File Specification to ensure correct data types for included fields
DoCmd.TransferText acImportDelim, "DtlDtlImport Specification", "DtlDtlMain", SourceFile, True
 Kill SourceFile

DoCmd.SetWarnings True
Exit Sub

error_handler:

On Error Resume Next
DoCmd.SetWarnings True

End Sub

Public Sub CurFiscPeriodFiscYear_Update(date_name As String, Filedate As Date, fiscper As Long, fiscyear As Long)
Dim dbsDtlReport As DAO.Database
Dim rs As DAO.Recordset
Dim Src As String

On Error GoTo leave:

Set dbsDtlReport = CurrentDb

'Add latest file information from last imported data file
Set rs = CurrentDb.OpenRecordset("Current_FiscalPeriod_FiscalYear", dbOpenTable)

    rs.AddNew
    rs!Date_Number = date_name
    rs!ddate = Filedate
    rs!Fiscal_Period = fiscper
    rs!Fiscal_Year = fiscyear
    rs.Update

Exit Sub

leave:

End Sub

Public Sub FiscalPeriodMakeTableQuery(fiscper As Long, fiscyear As Long)
Dim lastfiscyear As Long, lastfiscper As Long
Dim Src As String, LastYearDataTable As String


Dim dbs As DAO.Database

On Error Resume Next
lastfiscper = fiscper - 1
lastfiscyear = fiscyear - 1

'Delete DtlFP_FY Table from previous 13 fiscal periods ago
LastYearDataTable = "DtlDtl_FP" & lastfiscper & "_" & lastfiscyear & " "

If IsObject(dbs.TableDefs(LastYearDataTable)) Then
DoCmd.DeleteObject acTable, LastYearDataTable
End If

On Error GoTo leave:
DoCmd.SetWarnings False

' run maketable query to generate new dataset for current fiscal period and fiscal year

Src = "SELECT DtlDtlMain.[Bar Number] AS Bar_Number, DtlDtlMain.[Market Number] AS Market_Number, DtlDtlMain.[Region Number] 

AS Region_Number, " _
        & "Bar_AllData.TIER AS Tier, DtlDtlMain.[Fiscal Year] AS Fiscal_Year, DtlDtlMain.[Fiscal Period] AS Fiscal_Period, 

DtlDtlMain.[Meal Period] AS Meal_Period, " _
        & "DtlDtlMain.[Item Number] AS Item_Number, DtlDtlMain.[Item Description] AS Item_Description, DtlDtlMain.[Item Category] AS 

Item_Category, DtlDtlMain.[Item Category Description] " _
        & "AS Item_Category_Description, DtlDtlMain.[Item Group] AS Item_Group, DtlDtlMain.[Item Group Description] AS Item_Group_Description, 

Sum([DtlDtlMain]![Qty Sold]) AS Qty_Sold, " _
        & "CCur(Sum([DtlDtlMain]![Gross Sales])) AS Gross_Sales, CCur(Sum([DtlDtlMain]![Net Sales])) AS Net_Sales, " _
        & "CCur(Sum([DtlDtlMain]![Item Cost]*[DtlDtlMain]![Qty Sold])) AS Cost " _
        & "INTO DtlDtl_FP" & fiscper & "_" & fiscyear & " " _
        & "FROM DtlDtlMain LEFT JOIN Bar_AllData ON DtlDtlMain.[Bar Number] = Bar_AllData.Bar_Number " _
        & "GROUP BY DtlDtlMain.[Bar Number], DtlDtlMain.[Market Number], DtlDtlMain.[Region Number], Bar_AllData.TIER, 

DtlDtlMain.[Fiscal Year], DtlDtlMain.[Fiscal Period], " _
        & "DtlDtlMain.[Meal Period], DtlDtlMain.[Item Number], DtlDtlMain.[Item Description], DtlDtlMain.[Item Category], 

DtlDtlMain.[Item Category Description], DtlDtlMain.[Item Group], DtlDtlMain.[Item Group Description]" _
        & "ORDER BY DtlDtlMain.[Item Number];"

DoCmd.RunSQL Src

DoCmd.SetWarnings True

Exit Sub

leave:
DoCmd.SetWarnings True


End Sub

Public Sub MainDataTableEmpty()

CurrentDb.Execute "delete * from DtlDtlMain"

End Sub

Public Sub BarAllDataMakeTableQuery()
Dim Src As String
Dim dbs As DAO.Database
Dim RestData As String

On Error Resume Next

RestData = "Bar_AllData"

'Delete old BarAllData table if it exists
If IsObject(dbs.TableDefs(RestData)) Then
DoCmd.DeleteObject acTable, RestData
End If

On Error GoTo leave:

DoCmd.SetWarnings False

' run maketable query to generate new dataset for current Baralldata table
Src = "SELECT DISTINCT Bar_Data.Bar_Number, DtlDtlMain.[Bar Name], DtlDtlMain.[Market Number], DtlDtlMain.[Market 

Name], DtlDtlMain.[Region Number], " _
        & "DtlDtlMain.[Region Name], Bar_Data.TIER, Bar_Data.Location, Bar_Data.State " _
        & "INTO Bar_AllData " _
        & "FROM Bar_Data LEFT JOIN DtlDtlMain ON Bar_Data.Bar_Number=DtlDtlMain.[Bar Number];"

DoCmd.RunSQL Src

DoCmd.SetWarnings True

Exit Sub

leave:

DoCmd.SetWarnings True

End Sub

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:scurvylion
ID: 33588866
Hey - it wouldn'tlet me award the points to Jimpen.

What's up with that??

Anyway, thanks again guys.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33589579
<Hey - it wouldn'tlet me award the points to Jimpen.

What's up with that??>

Then click the "Request Attention" link again, ...and explain your intentions...
0
 

Author Comment

by:scurvylion
ID: 33589645
I submitted a request for Jimpen to be awarded the 500 points.
Thanks
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question