Import CSV then delete/remove file

[andrewhoddie]

Hi

I have an access database that I would like to update automatically but I am having issues, the debugger comes back to the table line as the issue, does anyone know why it is failing?

Also is there a way I can then delete the imported csv file or move it into a sub folder e.g. imported?

I would then like to run the macro automatically using task scheduler which I should be able to do.

thanks in advance
Andrew

Option Compare Database

Option Explicit

Public Function Import_data_files()

Dim report_path As String, file_name As String

report_path = "\\mycomputer\CVS Files\"

file_name = Dir(report_path & "*.csv", vbDirectory)

Do While file_name <> vbNullString

DoCmd.TransferText acImportDelim, , "dbo_COProperties", , True
file_name = Dir
Loop


End Function

 

[Joe4]

You seem to be missing the file name argument from this line:

DoCmd.TransferText acImportDelim, , "dbo_COProperties", , True

See: DoCmd.TransferText method (Access)

 

[andrewhoddie]

Hi @Joe4

thanks very much for your response, I have changed it to

DoCmd.TransferText acImportDelim, , "dbo_COProperties", "*.csv", True

and also tried

DoCmd.TransferText acImportDelim, , "dbo_COProperties", "\\my computer\CVS Files\*.csv", True

This is still erroring at the same point. We will be getting lots of files with different names so I cant specify a file name

thanks
Andrew

 

[Joe4]

You cannot use wildcards in there!
You are actually looping through the file names, so that file name variable is what you want to reference, but you also need to include the path, i.e.

DoCmd.TransferText acImportDelim, , "dbo_COProperties", report_path & file_name, True

See here for more details: How to Import All Files within a Folder to Your Access Table

 

[andrewhoddie]

Hi @Joe4

You cannot use wildcards in there!
You are actually looping through the file names, so that file name variable is what you want to reference, but you also need to include the path, i.e.

DoCmd.TransferText acImportDelim, , "dbo_COProperties", report_path & file_name, True

See here for more details: How to Import All Files within a Folder to Your Access Table

thank you very much I have now got it working, I have also had to add in an Import specification as my table names to not exactly match some of the headings in the CSV and i have also added in a way to move the file. I have copied the code below in case anyone else comes across this

thanks
Andrew

Public Function Import_data_files()

Dim report_path As String, file_name As String
Dim FSO As Object
Dim FolderNameMove As String
Dim FileList As String

report_path = "\\mycomputer\CVS Files\"
FolderNameMove = "\\mycomputer\CVS Files\old\"

file_name = Dir(report_path & "*.csv", vbDirectory)

Do While file_name <> vbNullString

DoCmd.TransferText acImportDelim, "Import CO CSV Specification", "dbo_COProperties", report_path & file_name, True
file_name = Dir
Loop

'Move files after import
FileList = Dir(report_path & "*.csv")
While (Len(Trim$(FileList)) > 0)
If (Len(Trim$(FileList))) > 0 Then
Set FSO = CreateObject("scripting.filesystemobject")
FSO.MoveFile Source:=report_path & FileList, Destination:=FolderNameMove
End If
FileList = Dir
Wend
End Function