Import automation and copy to a temp table

[Alonza Malcom]

Good Morning!

I am attempting to automate the import of a .csv file from a given directory and copy and append it to another table in a different layout. Below is my code. The error that i'm getting is it can't fine the copyobject in the database...any ideas on how th do this?

Option Compare Database
Option Explicit
Function DoImportandAppend()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in CSV worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Users\u8201332\Desktop\New folder with the new path to the folder that
' contains the CSV files
strPath = "C:\Users\u8201332\Desktop\HRC folder\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strFile = Dir(strPath & "*.csv")

Do While Len(strFile) > 0
strTable = Left(strFile, Len(strFile) - 4)
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
DoCmd.SetWarnings False
DoCmd.CopyObject "", "tblhrc", acTable, "strpath & strFile"
DoCmd.OpenQuery "qryHRCflatfile", acViewNormal, acAdd
DoCmd.OpenQuery "qryappHRCdata", acViewNormal, acAdd

' Kill strPathFile
strFile = Dir()
Loop

End Function


Thanks!

[pbaldy]

What are you trying to accomplish with that line? I haven't used that command, but the variables couldn't be in quotes:

DoCmd.CopyObject "", "tblhrc", acTable, strpath & strFile

[Alonza Malcom]

Thank you Paul!

This is the code that imports a series of .csv files automatically...works great!

Option Compare Database
Option Explicit
Function DoImport()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in CSV worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Users\u8201332\Desktop\New folder with the new path to the folder that
' contains the CSV files
strPath = "C:\Users\u8201332\Desktop\HRC folder (partial)\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strFile = Dir(strPath & "*.csv")

Do While Len(strFile) > 0
strTable = Left(strFile, Len(strFile) - 4)
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop

End Function


After that, i'm running a macro that copies thei imported file (one by one) to a temp file and runs two action queries that re-formats the .csv file and appends it to another table:
Option Compare Database
'------------------------------------------------------------
' Macro2
'
'------------------------------------------------------------
Function Macro2()
On Error GoTo Macro2_Err

DoCmd.SetWarnings False
DoCmd.CopyObject "", "tblhrc", acTable, "Imported table"
DoCmd.OpenQuery "qryHRCflatfile", acViewNormal, acAdd
DoCmd.OpenQuery "qryappHRCdata", acViewNormal, acAdd

Macro2_Exit:
Exit Function
Macro2_Err:
MsgBox Error$
Resume Macro2_Exit
End Function


Works great too. Is there a way to combine the two in one Function? Access is having a hard time finding "Imported table" if I try to combine the code. I'm thinking that i'm not declaring "Imported table" as a variable properly, but that might not be it...any help would be appreciated!

Alonza Malcom

[pbaldy]

Well, you don't use a variable in the second function. If one of them was created in the first, you may be able to add DoEvents between the 2 processes and have them within the same function.

[Alonza Malcom]

Hi Paul!

I took out the quotes from the "Imported file" variable in the code...works great! Thank you!!!


Option Compare Database
Option Explicit
Function DoImportandAppend()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in CSV worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Users\u8201332\Desktop\New folder with the new path to the folder that
' contains the CSV files
strPath = "C:\Users\u8201332\Desktop\HRC folder\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strFile = Dir(strPath & "*.csv")

Do While Len(strFile) > 0
strTable = Left(strFile, Len(strFile) - 4)
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
'Takes the imported file, copies it to a temp table, reformats and appends the record to another master table

DoCmd.SetWarnings False
DoCmd.CopyObject "", "tblhrc", acTable, strTable
DoCmd.OpenQuery "qryHRCflatfile", acViewNormal, acAdd
DoCmd.OpenQuery "qryappHRCdata", acViewNormal, acAdd



' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported


' Kill strPathFile
strFile = Dir()
Loop

End Function

[pbaldy]

Happy to help!

[Alonza Malcom]

Hi Paul

Me again! Could you advise me of the additional code in the below/above that would add a column and insert the filename for every line?

Thank you!

[pbaldy]

At which point? I don't think you can add it to the TransferText, but you could pick up the variable and include the value in one of your queries. It couldn't be used directly, but could be placed in a form control or grabbed with a public function.

[Alonza Malcom]

OK...how could I pick it up and add it to this (make table) query?

DoCmd.OpenQuery "qryHRCflatfile", acViewNormal, acAdd

[Alonza Malcom]

Also...I would like to delete the .csv files after the append query runs to save space in the database...thanks!

DoCmd.OpenQuery "qryappHRCdata", acViewNormal, acAdd

[pbaldy]

Is that in a standard module? Presuming so, or either way, move this line to the top above the function (or to a standard module):

Dim strFile As String

and rename to

Public strFile As String

Then also in a standard module create this function:

Public Function GetFileName() As String

GetFileName = strFile
End Function

Then add a field to your make table query. In design view:

SourceFile: GetFileName()

it may not want the parentheses.

[pbaldy]

Oh, you can use Kill to delete a file.

[Alonza Malcom]

Thank you! I'll try this and let you know how I made out...

[pbaldy]

No problem. Not sure why it's broken my little function into 2 code windows, but it won't let me fix it. Those 3 lines should be together.

[Alonza Malcom]

Together in another standard module or in this one?