Import and Append Daily Excel Spreadsheets

[JayRab]

I am looking into another project to help combine data for easy analysis and use with my other access databases.

Currently there are two files saved a day in a specific folder named : 2014-01-05 Night Shift.xlsx ; 2014-01-05 Day Shift.xlsx. They always have this format and just change the date and between DAY & NIGHT. The spreadsheet itself is always in same format just updated values. I have these for everyday of the week.

What I would like to do is have these brought into an access database and appended to an existing table. I figure I can use DoCmd.TransferSpreadsheet with my range selected and use a temp table then do append query (but would rather not, hoping there is another way).

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Daily Shift Reports", "X:\Gordonsville\Mine ops\Daily Forman Paper Work\CUMB Leadmen Paperwork\Daily Shift Reports 2013\2014-01-05 Night Shift.xlsx", False, "B6:O30"

Also I would like to date stamp these with the date and the shift ( Day & Night). So if it comes from the 2014-01-05 Night shift. There are two additional columns added to table with Date and shift.

I have found some info to pull in all data from a specific folder. How do I go about stepping through the days that are in the future without getting duplicate data.

So tomorrow when I run this command I dont want to run it for every file in the folder just the one dated 2014-01-09 Night(& DAY) shift.xlsx.

Any help would be appreciated.

[June7]

The file path\name can be constructed with concatenation. Automatically determining the date part could be tricky. The code to append records to primary table can populate the additional fields. Something like:

For i = 1 to 2

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Daily Shift Reports", "X:\Gordonsville\Mine ops\Daily Forman Paper Work\CUMB Leadmen Paper work\Daily Shift Reports 2013\" & Format(Date()-1, "YYYY-MM-DD") & IIf(i=1, "Day", "Night") & " Shift.xlsx", False, "B6:030"

CurrentDb.Execute "INSERT INTO tablename SELECT *, Date(), '" & IIf(i=1, "Day", "Night") & "' FROM " & Format(Date()-1, "YYYY-MM-DD") & IIf(i=1, "Day", "Night") & " Shift"
Next

Should weekends be excluded?

Note: correct spelling Foreman - is your folder actually spelled with Forman?

[JayRab]

yeah I was thinking that was the route to take on the looping. DoCmd.TransferSpreadsheet doesnt APPEND does it? So I have to loop Table name -"Temp & IIf(i-1, "Day", "Night")"as well as file path\name?
I too do not know how I want to make sure I am only getting the specific data I want. The excel spreadsheets are saved everyday 365.

Myself or others will probably only click the Update buttons on weekdays...no holidays. . The date()-1 would work if it was done ONLY ONCE daily. Maybe the possibly of built in background timer? With a check box table to see if it has been updated today or not? To keep from duplicate data being imported?

Spelling is off on EVERYTHING!!! has been a nightmare for some of my other projects. I learn to just open file and copy paste paths.

[June7]

I edited my previous post probably while you added your latest so you might look at again..

The Date()-1 will work if there is a file everyday and the update is run everyday, including weekends and holidays. If those must the excluded, the code gets more complicated.

The alternative is to have user select date in a textbox.

More code to validate the date input and make sure it is not already in the table would be necessary.

[JayRab]

The text box/drop box option is another good one. Can just create a Input Daily Foreman paperwork form and go from there.

Once I get this done for 1 site. I have 7-20 other sites to do this so trying to make it as automated as possible. I would like to get them to use just Access and not even make the excel spreadsheets...but that is not an easy task. I had to break teeth to just get them to use access for the maintenance one.

I appreciate the insert code too. This is a great start, Ill get to testing and see how it works.

Again I really apprecaite all the help.

Lastly: Is there a way to Append with the TransferSpreadsheet. or will I have to stick with the Temp Tables append them to main table and delete?

[June7]

It's my understanding that should be able to import directly to existing table. http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I don't have much need for importing data and can't remember what I've encountered before.

If can go directly to existing table, then updating for the additional fields would be:

CurrentDb.Execute "UPDATE tablename SET Datefield=Date(), Shift='" & IIf(i=1, "Day", "Night") & "' WHERE Datefield Is Null"

[JayRab]

Getting an error with the insert section.
CurrentDb.Execute "INSERT INTO tablename SELECT *, Date(), '" & IIf(i=1, "Day", "Night") & "' FROM " & Format(Date()-1, "YYYY-MM-DD") & IIf(i=1, "Day", "Night") & " Shift"
Next

No destination field name in INSERT INTO statement (Date()).

Do I need to just expand the "*" part?

[JayRab]

Figured out most of this...just having issues out of the Insert command. The Insert Into works with

strSQL = "INSERT INTO [2014 Cumberland Daily Shift Reports]( F1, F16 ) VALUES('1/9/2014', 'Day')"

but I would like to add it to everyline in F2 that is Null

Syntax help my where statement has a mistake

strSQL = "INSERT INTO [2014 Cumberland Daily Shift Reports]( F1, F16 ) VALUES('1/9/2014', 'Day') WHERE F2 IsNull"

Thanks

[June7]

Space between Is and Null: Is Null

http://allenbrowne.com/casu-12.html

[JayRab]

Figured out I wanted a UPDATE in there to do what I wanted. Now working on getting it to step through and update.

[June7]

Sorry, should have noticed was an INSERT sql. A WHERE clause is meaningless in an INSERT sql.

Use an UPDATE sql to populate fields of existing records. Example in post 6. I just noticed my typo missing space and fixed it.

F1 is a text or date/time data type?

[JayRab]

Option Compare Database
Function ImpoThisYears_allExcel()
'Imports all of this years data to date. From excel it also inserts date and shift stamps for each excel file for tracking deletes any empty rows.
'Set Variables
Dim ShiftStrg As String
Dim MyFolderPath As String
Dim MyPath As String
Dim MyTableName As String
Dim DateStrg As String
Dim strSQL As String
Dim strDeleteSQL As String
Dim MyFileName As String
Dim DateStrgFormatted As String
Dim ThisYear As Date
Dim TodaysDt As Date
Dim EndJ As String
Dim J As String
J = 0
ThisYear = "01/01/2014"
TodaysDt = Date
'Debug.Print (ThisYear)
'Debug.Print (TodaysDt)
EndJ = TodaysDt - ThisYear
'Debug.Print (EndJ)
'Step Trough all date so far this year
Do While J < EndJ
For k = 1 To EndJ
J = k
    'Debug.Print (k)
    'Debug.Print (J)
        DateStrg = Date - k
    DateStrgFormatted = Format(DateStrg, "YYYY-MM-DD ")
   ' Debug.Print (DateStrgFormatted)
   'Below steps to change from day shift to night shift on each DAY change.
        For i = 1 To 2
            'Debug.Print (i)
        If i = 1 Then
            ShiftStrg = "Day"
        Else
            ShiftStrg = "Night"
        End If
            MyFileName = DateStrgFormatted & ShiftStrg & " Shift.xlsx"
            MyFolderPath = "X:\Gordonsville\Mine ops\Daily Forman Paper Work\CUMB Leadmen Paperwork\Daily Shift Reports 2013\"
            MyPath = MyFolderPath & MyFileName
            'Debug.Print (MyPath)
                MyTableName = "2014 Cumberland Daily Shift Reports"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, MyTableName, MyPath, False, "A6:Q30"
                'Insert Date( F1) and Shift (F16) for each sheet
                strSQL = "Update [2014 Cumberland Daily Shift Reports] SET F1='" & DateStrg & "',F16='" & ShiftStrg & "' WHERE F2 is not null AND F1 is null"
                'Debug.Print (strSQL)
                CurrentDb.Execute strSQL
        Next
Next
Loop
strDeleteSQL = "DELETE [2014 Cumberland Daily Shift Reports].F2, [2014 Cumberland Daily Shift Reports].F3, [2014 Cumberland Daily Shift Reports].F5 FROM [2014 Cumberland Daily Shift Reports] WHERE [2014 Cumberland Daily Shift Reports].F2 Is Null AND [2014 Cumberland Daily Shift Reports].F3 Is Null AND [2014 Cumberland Daily Shift Reports].F5 Is Null"
CurrentDb.Execute strDeleteSQL
End Function

got it to do everything I wanted. Probably went about it the long way, just did it the way I would a matlab program lol. I have several locations that with data that is saved this way. So now all I need to do is change a few things to get it to work.

Thanks for help guys.

[June7]

A DELETE sql does not need to enumerate fields because the entire record is deleted.

DELETE FROM tablename WHERE criteria;

[JayRab]

Thanks I just create a delete query that worked and and copied the SQL from that lol.