insert imported filename into table

[petho]

I am using the following VBA to automatically import csv files into an access table:


Function ImportCSV()

Dim InputDir, ImportFile As String, tblName As String, FinalName As String, specName As String
Dim InputMsg As String
InputDir = "c:\testFiles\"
ImportFile = Dir(InputDir & "\*.csv")
Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'Use this to import each file into separate tables.
tblName = "tbl_Validation" 'I use this to import all my files into one table.
specName = "INTMR_Sites"
DoCmd.TransferText acImportDelim, specName, tblName, InputDir & ImportFile, True
ImportFile = Dir
Loop
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
'If fs.FileExists("c:\YOUR DIR WHERE THE FILES LAND\*.csv") Then
fs.MoveFile "c:\testFiles\*.csv", "c:\testHistory\"

End Function

An example filename of my csv files is:
SAGAS_INTMR_ENVSA_REMCO_20050715102115.CSV

What I now need to do is add a column within my table that can store the "date" which is found within the file name (first 8 numerals). Ie, in the above example, 15/07/2005 needs to be stored in a date field.

Is there any way I can do this using VBA?

Cheers.

 

[namliam]

TheDate = substr(ImportFile, 24,14) ' may have gotten this wrong

Currentdb.execute "UPDATE " & tblName & _
" SET [Fieldname] = '" & TheDate & "'" & _
" WHERE [FieldName] Is Null;

Add that to your code just after the transfertext....

Edit: Spoted that you want an actual date...
then use some dateformating to convert it using functions like Datevalue and Timevalue to store into TheDate

Then use below query:
Currentdb.execute "UPDATE " & tblName & _
" SET [Fieldname] = #" & Format(TheDate,"MM/DD/YYYY HH:MM:SS") & "#" & _
" WHERE [FieldName] Is Null;

P.S. You forgot to Dim inputDir propers (missing 'as string')

P.P.S.
InputDir = "c:\testFiles\"
ImportFile = Dir(InputDir & "\*.csv")
=> Dir("c:\testFiles\\*.csv") ?? Should not be what you want?

P.P.P.S. fs.MoveFile "c:\testFiles\*.csv", "c:\testHistory\"
You should really move each file after importing. This way you know which files have been imported and which not. Should your importing ever be cancelled or fail.....

 

[petho]

Excuss my lack of VBA knowledge, but when I run the script, an error appears as "Compile Error: Sub or function not defined" which is referring to the substr

Do I need to define both TheDate as well as substr and if so, what to?

Cheers.

 

[petho]

FYI - this is my current code:

Function ImportCSV()

Dim InputDir As String, ImportFile As String, tblName As String, FinalName As String, specName As String
Dim InputMsg As String
Dim TheDate As Date
Dim fs

Set fs = CreateObject("Scripting.FileSystemObject")

'set the location of the directory and files'
InputDir = "G:\EAM\FRCT\ACK_OK_Files\Files\"
ImportFile = Dir(InputDir & "*.csv")

'whilst at least 1 csv file exists in the directory'
Do While Len(ImportFile) > 0

'set table and spec names'
tblName = "tbl_Validation"
specName = "INTMR_Sites"

'import data within files to the allocated table'
DoCmd.TransferText acImportDelim, specName, tblName, InputDir & ImportFile, True

TheDate = substr(ImportFile, 24, 14)

CurrentDb.Execute "UPDATE " & tblName & _
" SET [Fieldname] = '" & TheDate & "'" & _
" WHERE [FieldName] Is Null;"

ImportFile = Dir

fs.MoveFile "G:\EAM\FRCT\ACK_OK_Files\Files\*.csv", "G:\EAM\FRCT\ACK_OK_Files\History_Files\"

Loop

End Function

 

[RuralGuy]

Moving over from VB? I'm rusty at VB. How about using Instr() or Left()?

 

[petho]

Can you please clarify exactly how you mean? I'm very new to VBA...
Ta.

 

[RuralGuy]

TheDate = substr(ImportFile, 24, 14)

The above statement is not valid VBA. I can't remember what substr() does but if you are trying copy to the variable TheDate 14 characters from a string called ImportFile starting at character 24 then I am not sure there is a direct replacement. I'll do some research and post back. I would think there is something other than writing your own function (which would be easy by the way).

 

[RuralGuy]

Aha! TheDate = Mid(ImportFile, 24, 14)

 

[namliam]

Aha! TheDate = Mid(ImportFile, 24, 14)

Substr is Oracle SQL... Sorry, yes... Mid is the correct....

TheDate should be defined (dim) either as a double or a date depending on if you want an actual date or a number....