Solved

Uploading csv file to Access using VBA

Posted on 2010-08-30
8
695 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 500 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now